Ubuntu 16.04服务器PostgreSQL远程连接pgAdmin异常求助

Hey there, let's tackle this frustrating issue you're dealing with. Quick clarification first: when you run manage.py runserver My_droplet_IP:5432, that's just telling Django to listen on port 5432 (unusual, since it's typically 8000), but your Django app is almost certainly connecting to PostgreSQL locally (via localhost or a Unix socket in your settings.py). That's why it works! The real problem is getting remote connections (like from pgAdmin) to function properly. Let's break down the fixes step by step:

1. Make Sure PostgreSQL Listens for Remote Connections #

First, confirm PostgreSQL is configured to accept connections outside the server:

• Open your postgresql.conf file (usually at /etc/postgresql/<your-postgres-version>/main/postgresql.conf) and check the listen_addresses line. It should be set to either * (listen on all interfaces) or your Droplet's public IP:

  listen_addresses = '*'
  

• Restart PostgreSQL to apply changes:

  sudo systemctl restart postgresql
  

• Verify the service is listening on the correct port:

  ss -tulpn | grep 5432
  

  You should see output showing 0.0.0.0:5432 or your Droplet IP, not just 127.0.0.1:5432.

2. Update pg_hba.conf to Allow Remote Access #

The pg_hba.conf file controls which hosts can connect to PostgreSQL. Let's adjust it:

• Open the file (same directory as postgresql.conf):

  sudo nano /etc/postgresql/<your-postgres-version>/main/pg_hba.conf
  

• Add a rule to allow remote connections (replace <your-local-ip> with your actual local machine's IP for security, or use 0.0.0.0/0 temporarily for testing):

  host    all             all             <your-local-ip>/32               md5
  # OR for testing only (not recommended for production):
  # host    all             all             0.0.0.0/0               md5
  

• Critical note: Place this rule above any host ... 127.0.0.1/32 or reject rules—pg_hba.conf processes rules top-to-bottom.
• Reload PostgreSQL's configuration without restarting:

  sudo -u postgres psql -c "SELECT pg_reload_conf();"
  

3. Check Firewall Settings (UFW) #

Ubuntu 16.04 uses UFW by default—ensure it's allowing traffic on port 5432:

• Allow PostgreSQL port:

  sudo ufw allow 5432/tcp
  

• Reload UFW and verify the rule is active:

  sudo ufw reload
  sudo ufw status
  

  You should see 5432/tcp listed under allowed services.

4. Test Remote Connection Directly (Bypass pgAdmin) #

To rule out pgAdmin-specific bugs, test the connection from your local machine using psql:

psql -h <your-droplet-ip> -U <your-postgres-user> -d <your-db-name>

• If this command hangs or fails, go back to steps 1-3—your server/PostgreSQL configuration is still blocking remote access.
• If this connects successfully, the issue is with pgAdmin itself. Try these fixes:
  • Update pgAdmin to the latest version (old builds often have connection quirks).
  • Disable any VPN/proxy on your local machine that might interfere with the connection.
  • In pgAdmin's connection settings, uncheck "Save password" temporarily—sometimes credential saving causes unexpected freezes.
  • Create a brand new connection profile instead of editing an existing one.

5. Confirm Django's Local Database Setup #

Just to be clear: Your Django settings.py is probably using HOST: 'localhost' (or leaving it blank, which defaults to a local socket). That's why it works even when remote connections are broken—you don't need to change this unless you're moving Django to a separate server.

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