Ubuntu 16.04 LTS下远程连接PostgreSQL无响应求助
Let’s break down what’s happening here: your local PostgreSQL connections work perfectly, but remote connections hang indefinitely instead of throwing a "connection refused" error. That tells us the network request is reaching your server (so firewall/port rules aren’t the immediate blocker) but getting stuck somewhere in PostgreSQL’s stack or the underlying system. Let’s walk through targeted checks to narrow this down:
1. Confirm PostgreSQL is Listening on Public Interfaces
It’s easy to edit postgresql.conf but forget to restart the service, or mistype the listen_addresses value. Run this command to verify what addresses PostgreSQL is actually monitoring:
ss -tulpn | grep 5432
You should see output like LISTEN 0 128 0.0.0.0:5432 0.0.0.0:* users:(("postgres",pid=xxxx,fd=xx))—0.0.0.0 means it’s listening on all network interfaces. If you only see 127.0.0.1, PostgreSQL is still restricted to local connections. Double-check postgresql.conf for the correct listen_addresses = '*' line, then restart the service with:
sudo systemctl restart postgresql
2. Check Mounted Data Directory Permissions & Health
Even though local connections work, remote connections might trigger edge cases with your non-default data directory. First, confirm the directory is owned by the postgres user/group with strict permissions:
ls -ld /path/to/your/mounted/data/dir
The output should show drwx------ 19 postgres postgres ...—if permissions are looser (like 755) or owned by another user, fix it with:
sudo chown -R postgres:postgres /path/to/your/mounted/data/dir sudo chmod -R 700 /path/to/your/mounted/data/dir
Next, check your /etc/fstab entry for the mounted disk—avoid restrictive options like noexec, nodev, or nosuid which can interfere with PostgreSQL’s operation. Also, run df -h to ensure the disk isn’t full, and iostat -x 1 5 to check for high IO latency that could cause hangs.
3. Dig Into PostgreSQL Logs for Connection Clues
PostgreSQL logs will tell you if the remote connection is even reaching the database process. Logs are usually located at /var/log/postgresql/postgresql-9.5-main.log (adjust the version number if needed) or inside your data directory’s pg_log folder. Look for entries starting with connection received: or any error messages around the time you tried the remote connection.
If there are no entries at all, the connection isn’t reaching PostgreSQL—this points to a network issue beyond your server’s firewall.
4. Test Network Reachability Beyond Local Firewall
Your server’s firewall is set to ACCEPT, but intermediate blocks (like ISP filtering, router NAT misconfiguration, or cloud security groups) could be the culprit. From the remote machine, run:
telnet 45.67.82.123 5432
Or if telnet isn’t installed:
nc -zv 45.67.82.123 5432
If this hangs too, double-check that your server’s public IP is correctly mapped to its private IP (if using NAT), and that your cloud provider (if applicable) has 5432 open in their security group. Some ISPs block common database ports by default—try switching to a non-standard port like 5433 as a test.
5. Validate pg_hba.conf Configuration
A misconfigured pg_hba.conf can cause silent hangs (though it usually throws an auth error). Ensure you have a line allowing your remote IP (or all IPs for testing) with the correct authentication method:
host all all 0.0.0.0/0 md5
After editing, reload PostgreSQL with sudo systemctl reload postgresql—no full restart is needed for pg_hba.conf changes.
6. Confirm PostgreSQL is Using the Correct Data Directory
It’s possible the PostgreSQL service is still running with the default data directory instead of your mounted one. Check the process arguments:
ps aux | grep postgres
Look for the -D parameter—it should point to your mounted data directory. If not, update the service configuration (usually in /etc/postgresql/9.5/main/postgresql.conf or the systemd service file) to specify the correct -D path, then restart the service.
内容的提问来源于stack exchange，提问作者jsstuball
