keepcalculator

06 Numbers and Powers

Connect pgAdmin to PostgreSQL on a Remote VPS

Card No. 06·79182 · Updated · Standard formula, shown below

This guide walks you through connecting pgAdmin (the popular PostgreSQL management tool) to a PostgreSQL database running on a remote VPS. By the end, you’ll be able to browse tables, run queries, and manage your database from your local machine, just as if the database were installed locally. You’ll need your VPS’s IP address, the PostgreSQL port (default 5432), and a database user with a password.

What You’ll Need

  • A VPS with PostgreSQL installed and running (Ubuntu/Debian commands shown, but the steps are similar on other Linux distros).
  • pgAdmin installed on your local computer (version 4 or later).
  • SSH access to your VPS (to edit config files and create users).
  • The VPS’s public IP address and the credentials of a PostgreSQL user (usually postgres or a user you create).

Step-by-Step

  1. Find your PostgreSQL version and configuration files.
    SSH into your VPS and run psql --version to confirm PostgreSQL is installed. The main config files are usually in /etc/postgresql/<version>/main/. Note the version number—you’ll need it for the file paths below.
  2. Edit postgresql.conf to listen on all interfaces.
    Open the file with sudo nano /etc/postgresql/<version>/main/postgresql.conf. Find the line #listen_addresses = 'localhost' and change it to listen_addresses = '*' (remove the #). This tells PostgreSQL to accept connections on any network interface, not just localhost.
  3. Edit pg_hba.conf to allow remote connections.
    Open sudo nano /etc/postgresql/<version>/main/pg_hba.conf. Scroll to the bottom. Add a line like this to allow password-authenticated connections from anywhere (or restrict to your IP for better security):
    host all all 0.0.0.0/0 md5
    If you prefer to only allow your own IP, replace 0.0.0.0/0 with your public IP followed by /32 (e.g., 203.0.113.5/32).
  4. Restart PostgreSQL.
    Run sudo systemctl restart postgresql (or sudo service postgresql restart on older systems). This applies the config changes.
  5. Create a dedicated database user (optional but recommended).
    Connect as the postgres superuser: sudo -u postgres psql. Then create a user and password:
    CREATE USER myuser WITH PASSWORD 'mypassword';
    Give it database access (e.g., to the default postgres database):
    GRANT CONNECT ON DATABASE postgres TO myuser;
    Exit with \q.
  6. Open the firewall port.
    If you have UFW enabled, allow PostgreSQL: sudo ufw allow 5432/tcp. If you use a cloud provider’s firewall (AWS, DigitalOcean, etc.), add an inbound rule for TCP port 5432 from your IP or anywhere.
  7. Find your VPS’s public IP.
    Run curl ifconfig.me or check your provider’s dashboard. Write it down.
  8. Open pgAdmin on your local machine.
    Launch pgAdmin. In the left sidebar, right-click on Servers and select Register > Server….
  9. Fill in the connection details.
    In the dialog, go to the General tab and give the server a name (e.g., “My VPS DB”). Then go to the Connection tab and enter:
    • Host name/address: your VPS’s public IP
    • Port: 5432 (or whatever you set in postgresql.conf)
    • Maintenance database: postgres (or your specific database)
    • Username: myuser (or postgres)
    • Password: the password you set

    Check Save password if you don’t want to re-enter it each time.

  10. Click Save.
    pgAdmin will attempt to connect. If successful, you’ll see your server listed in the sidebar, and you can expand it to browse databases, schemas, and tables.

Common Mistakes

  • Forgetting to edit listen_addresses – Even if pg_hba.conf allows remote connections, PostgreSQL won’t listen on external interfaces if this is left as ‘localhost’.
  • Using the wrong pg_hba.conf line – Make sure the line is added before any restrictive lines, or at least that it’s not overridden by a later rule. The file is read top-down; the first match wins.
  • Firewall still blocking the port – Even after editing configs, a cloud firewall or UFW can silently drop connections. Test with telnet your_vps_ip 5432 from your local machine.
  • Using the wrong username – The default postgres user might have a password you never set. It’s easier to create a new user with a known password.
  • Not restarting PostgreSQL – Config changes only take effect after a restart.

Tips

  • Use SSH tunneling for extra security – Instead of opening port 5432 to the world, you can keep PostgreSQL bound to localhost and use an SSH tunnel. In pgAdmin, set the host to localhost and the port to a local port (e.g., 5433), then create the tunnel with ssh -L 5433:localhost:5432 user@vps_ip.
  • Restrict by IP – In pg_hba.conf, use a specific IP range instead of 0.0.0.0/0 to reduce attack surface.
  • Use SSL – If your PostgreSQL has SSL enabled, you can check the SSL tab in pgAdmin and select Require to encrypt the connection.
  • Test with psql first – Before using pgAdmin, try connecting from your local machine using psql -h your_vps_ip -U myuser -d postgres. This isolates whether the issue is PostgreSQL or pgAdmin.

Troubleshooting

Connection timed out – This usually means the firewall is blocking the port. Double-check UFW and any cloud security groups. Also verify that PostgreSQL is listening on 0.0.0.0 by running sudo netstat -tulpn | grep 5432 on the VPS.

Password authentication failed – Ensure the user exists and the password is correct. If you’re using the postgres user, you may need to set a password first: sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'newpassword';".

No pg_hba.conf entry for host – This error appears when no rule matches your connection. Check that your rule uses the correct authentication method (md5 or scram-sha-256, depending on your PostgreSQL version). For PostgreSQL 10+, scram-sha-256 is often required; you can use md5 only if you’ve set password_encryption = md5 in postgresql.conf.

Could not connect: Connection refused – PostgreSQL isn’t listening on the expected port or interface. Verify the listen_addresses change and restart. Also check that the port in pgAdmin matches the port setting in postgresql.conf.

SSL error – If your server requires SSL and pgAdmin isn’t set to use it, you’ll get an SSL-related error. Go to the SSL tab in the server registration and set SSL mode to Require or Verify-CA.

Frequently Asked Questions

Can I connect to a PostgreSQL database that’s not on the default port?
Yes. Just change the port in postgresql.conf and restart. Then enter that port in pgAdmin’s connection settings.

Is it safe to expose PostgreSQL to the internet?
It’s not ideal. If you must, use strong passwords, restrict by IP, and consider SSL. Better yet, use an SSH tunnel or a VPN.

Why does pgAdmin show “Server does not listen on TCP/IP”?
This means PostgreSQL is not listening on TCP. Check listen_addresses in postgresql.conf – it must be set to ‘*’ or your specific interface, and the server must be restarted.

What if I don’t know the PostgreSQL version on my VPS?
Run psql --version as any user, or sudo -u postgres psql -c 'SELECT version();'. The version number is the first part of the output (e.g., 14.5).

Can I use pgAdmin to connect to multiple remote databases?
Yes. Register each server separately in pgAdmin. Each will appear as a separate entry under the Servers tree.

How do I change the password for a PostgreSQL user?
Connect via psql as a superuser and run: ALTER USER username WITH PASSWORD 'newpassword'; Then update the password in pgAdmin.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →