DigitalOcean部署站点Postgres数据库无法通过Postico连接的解决方法

Let’s work through this connection timeout issue step by step—you’ve already nailed the first critical step by setting listen_addresses = '*' and restarting Postgres, so let’s focus on the other common blockers that might be stopping your connection:

1. Check Your Droplet’s Local Firewall (ufw) #

DigitalOcean droplets use ufw by default, and it’s almost certainly not allowing incoming traffic on port 5432 yet:

• Run this command to view current firewall rules:

  sudo ufw status
  

• If you don’t see a line like 5432/tcp ALLOW Anywhere, add the rule with:

  sudo ufw allow 5432/tcp
  

• Recheck the status with sudo ufw status to confirm the rule is active.

2. Update pg_hba.conf to Allow Remote Connections #

Even if Postgres is listening on all IPs, its access control list (pg_hba.conf) might be blocking your connection:

• Open the config file:

  sudo nano /etc/postgresql/10/main/pg_hba.conf
  

• Add a line at the bottom to allow your local public IP (find your public IP by searching "what is my ip" in your browser) or temporarily allow all IPs for testing:

  # Allow your specific local IP (recommended for security)
  host    all             all             YOUR_LOCAL_IP/32               md5
  # OR allow all IPs (only for testing—lock this down later!)
  host    all             all             0.0.0.0/0               md5
  

• Save the file (press Ctrl+O, hit Enter, then Ctrl+X in nano) and restart Postgres:

  sudo systemctl restart postgresql
  

3. Verify DigitalOcean Cloud Firewall Rules #

Don’t overlook the cloud-level firewall DigitalOcean applies to your droplet—this is a super common oversight:

1. Log into your DigitalOcean dashboard
2. Navigate to your droplet’s page, then go to the Firewalls tab
3. Make sure there’s an inbound rule allowing traffic on port 5432 from your local IP (or 0.0.0.0/0 for testing)
4. If no such rule exists, create one and save it.

4. Test Network Connectivity #

Before jumping back to Postico, confirm the port is actually reachable from your local machine:

• Use nc (netcat) to test:

  nc -zv 165.22.216.110 5432
  

• Or telnet if you have it installed:

  telnet 165.22.216.110 5432
  

• If you get a timeout or "connection refused," double-check both your local and cloud firewall rules. If the connection succeeds, the issue is likely with your Postico configuration—double-check your username, password, and target database name.

5. Confirm Postgres is Listening on Port 5432 #

On your droplet, verify Postgres is actually bound to the correct port and IP:

sudo ss -plnt | grep postgres

You should see output like LISTEN 0 128 0.0.0.0:5432 0.0.0.0:*—this confirms it’s listening on all IP addresses for port 5432.

Critical Security Reminder #

Once you get the connection working, replace the 0.0.0.0/0 rules in pg_hba.conf and your cloud firewall with your specific local IP—exposing your Postgres database to the entire internet is a major security risk.

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