PostgreSQL Connection Issue on Apache/Passenger Server

🔧 Step-by-Step Guide: Fixing PostgreSQL Connection Issue on Apache/Passenger

✅ 1. Confirm PostgreSQL is Running

Run:

sudo systemctl status postgresql

If it’s not running, start it:

sudo systemctl start postgresql

✅ 2. Check Application Database Configuration

Verify your application’s DB settings:

Ruby on Rails: config/database.yml
Django: settings.py
Node.js: config.js or .env

Ensure:

host = localhost or correct IP
port = 5432 (default)
Correct username, password, and database name.

✅ 3. Test Direct Connection

From the terminal, test if the application user can connect:

psql -h localhost -U your_db_user -d your_db_name

If it fails:

Check the user/password
Ensure PostgreSQL is listening on the right host/IP

✅ 4. Check `pg_hba.conf`

Located at:

/etc/postgresql/<version>/main/pg_hba.conf   # Ubuntu/Debian  
/var/lib/pgsql/data/pg_hba.conf              # CentOS/RHEL

Ensure a line like this exists:

host    all             all             127.0.0.1/32            md5

Then reload:

sudo systemctl reload postgresql

✅ 5. Check `postgresql.conf` Listen Address

Located at:

/etc/postgresql/<version>/main/postgresql.conf

Ensure this line is set:

listen_addresses = 'localhost'  # or '*'

Restart PostgreSQL:

sudo systemctl restart postgresql

✅ 6. Check Firewall or SELinux

Ensure port 5432 is open.

Ubuntu/CentOS:

sudo ufw allow 5432/tcp          # Ubuntu
sudo firewall-cmd --add-port=5432/tcp --permanent && sudo firewall-cmd --reload  # CentOS

SELinux (if applicable):

sudo setsebool -P httpd_can_network_connect_db 1

✅ 7. Inspect Apache/Passenger Logs

Check error logs:

Apache:
/var/log/apache2/error.log or /var/log/httpd/error_log
Passenger:
passenger-status
or
/var/log/passenger.log

Look for:

Authentication errors
Timeout errors
Socket/host unreachable messages

✅ 8. Check Application Environment

Make sure your application is:

Using the correct RAILS_ENV, NODE_ENV, or DJANGO_SETTINGS_MODULE
Not defaulting to production/test configs that have wrong DB details

✅ 9. Restart Apache

Once you’ve applied changes:

sudo systemctl restart apache2       # Ubuntu/Debian
sudo systemctl restart httpd         # CentOS/RHEL

✅ 10. Enable Debug Logging (If Still Failing)

In PostgreSQL:

log_connections = on
log_disconnections = on
log_statement = 'all'

Then check:

sudo tail -f /var/log/postgresql/postgresql-*.log

💡 Common Errors and Fixes:

Error	Likely Cause	Fix
`FATAL: password authentication failed`	Wrong credentials	Fix `database.yml` or `.env`
`could not connect to server`	PostgreSQL not running or firewall	Start service / open port
`timeout expired`	Host unreachable	Check firewall, SELinux, IP config
`connection refused`	PostgreSQL not listening on given host/IP	Fix `listen_addresses` or pg_hba.conf