无法连接Ubuntu Server 16.04上的PostgreSQL主节点求助

Let's break down your problem step by step—your service showing active (exited) is a critical red flag that’s almost certainly driving your connection timeout issue. Here’s how to diagnose and fix this:

1. First, Resolve the "active (exited)" Service Status #

A status of active (exited) means PostgreSQL started up but immediately crashed or shut down. The fastest way to find out why is to check the PostgreSQL logs.

On Ubuntu 16.04, logs are typically stored at:

/var/log/postgresql/postgresql-<version>-main.log

Replace <version> with your PostgreSQL version (e.g., 9.5). Run this command to view the most recent log entries:

tail -n 50 /var/log/postgresql/postgresql-9.5-main.log

Look for error messages like:

• Invalid settings in postgresql.conf (e.g., incorrect wal_level for replication)
• Permission issues on the data directory
• Syntax errors in pg_hba.conf
• Missing replication-related configurations

Critical Replication Settings to Verify #

Since you’re using streaming replication, double-check these in postgresql.conf:

• wal_level = replica (required for replication; older versions may use hot_standby)
• max_wal_senders = 5 (needs to be at least the number of your slave nodes + 1)
• wal_keep_segments = 32 (or higher, to prevent slaves from falling behind)
• archive_mode = on (if using WAL archiving alongside streaming)

After updating any configs, restart the service properly:

sudo systemctl restart postgresql

2. Fix the Mismatched Listening Address #

Your netstat output shows PostgreSQL is only listening on 172.31.43.128:5432, but you set listen_addresses = '*' in postgresql.conf. This mismatch usually means one of three things:

• You edited the wrong postgresql.conf file (Ubuntu uses version-specific directories like /etc/postgresql/9.5/main/postgresql.conf)
• The service didn’t pick up your changes (you forgot to restart it)
• There’s a typo in the config line (e.g., extra spaces or misplaced quotes)

To confirm which config file PostgreSQL is using, run:

ps aux | grep postgres | grep -E "-D|config_file"

Edit the correct file, ensure listen_addresses = '*' is set, save, and restart the service.

3. Check for Firewall Blocking #

Ubuntu 16.04 uses ufw by default. Even if PostgreSQL is listening, the firewall might be blocking incoming connections on port 5432 from your client IP (172.31.33.203).

• View current firewall rules:

  sudo ufw status
  

• Allow connections from your client:

  sudo ufw allow from 172.31.33.203 to any port 5432
  

• Temporarily disable ufw to test if it’s the blocker:

  sudo ufw disable
  

If you can connect after disabling, re-enable ufw and keep the specific rule you added.

4. Kill Residual PostgreSQL Processes #

Sometimes, even if the service shows active (exited), old PostgreSQL processes might still be holding the port. Check for running processes:

ps aux | grep postgres

Kill all PostgreSQL processes (replace <pid> with the actual process IDs):

sudo kill -9 <pid>

Then restart the service and verify it’s running:

sudo systemctl restart postgresql
sudo systemctl status postgresql

5. Validate pg_hba.conf Syntax #

A misconfigured pg_hba.conf can prevent PostgreSQL from starting or accepting connections. Check for syntax errors with:

sudo pg_hba_check -f /etc/postgresql/9.5/main/pg_hba.conf

Fix any reported errors, then restart the service.

Final Connection Test #

Once the service shows active (running), test your Python connection again. If you still get errors, try connecting directly from the client server using psql to rule out Python-specific issues:

psql -d my_pgpool_db -U postgres -h 172.31.43.128

内容的提问来源于stack exchange，提问作者e7lT2P