Install PostgreSQL on a VPS: Setup, Remote Access & Fixes

Most of our VPS guides talk about MySQL because most WordPress and PHP stacks expect it. But if you're running a Django, Node, or Rails app — or you just prefer Postgres's stricter typing and JSONB support — you'll hit a different set of setup and connection headaches. Here's how to get PostgreSQL running on a fresh Ubuntu VPS, open it up safely for remote access, and fix the errors that trip people up most.

Installing PostgreSQL

On Ubuntu 22.04/24.04, the default repos usually carry a recent-enough version, but if you want the latest major release, pull it straight from the PostgreSQL APT repo instead of relying on whatever Ubuntu bundles.

sudo apt update
sudo apt install -y curl ca-certificates gnupg
sudo install -d /usr/share/postgresql-common/pgdg
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo tee /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt update
sudo apt install -y postgresql-16

Check it's alive:

sudo systemctl status postgresql
sudo -u postgres psql -c "SELECT version();"

Creating a database and a real user

Don't run your app as the postgres superuser. Create a dedicated role and database per app — it keeps blast radius small if credentials ever leak.

sudo -u postgres psql

CREATE USER appuser WITH PASSWORD 'use-a-strong-password-here';
CREATE DATABASE appdb OWNER appuser;
GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;
\q

Test the local connection with the new role before moving on:

psql -U appuser -d appdb -h 127.0.0.1 -W

Symptom: "psql: error: connection to server ... failed: Connection refused"

Cause: By default, PostgreSQL only listens on localhost. If your app server or a GUI tool like pgAdmin/DBeaver is on a different machine, it can't reach port 5432 at all.

Fix: Edit postgresql.conf (usually /etc/postgresql/16/main/postgresql.conf) and set:

listen_addresses = '*'

Or lock it to a specific private IP instead of * if you want to be tighter about it. Then restart:

sudo systemctl restart postgresql

Symptom: "FATAL: no pg_hba.conf entry for host ..."

Cause: Even with listen_addresses open, Postgres checks pg_hba.conf for a rule that matches the connecting IP, database, and user before it lets the connection through. No matching line means an instant reject — this is the single most common Postgres connection error people hit on a fresh VPS.

Fix: Add a scoped rule in /etc/postgresql/16/main/pg_hba.conf. Don't use 0.0.0.0/0 unless you genuinely need it open to the world — scope it to your app server's IP or your office/VPN range:

# TYPE  DATABASE  USER     ADDRESS          METHOD
host    appdb     appuser  203.0.113.10/32  scram-sha-256

Reload (no restart needed for pg_hba changes):

sudo systemctl reload postgresql

Symptom: "FATAL: password authentication failed for user"

Cause: Either the password is genuinely wrong, or the auth method in pg_hba.conf is set to peer/ident instead of scram-sha-256/md5. Peer auth ignores the password entirely and checks the OS username instead — it's the default for local socket connections and confuses a lot of people testing from the app server itself.

Fix: For password-based connections (which is what your app needs), make sure the matching line uses scram-sha-256:

local   all       all                       peer
host    all       all       127.0.0.1/32    scram-sha-256
host    all       all       ::1/128         scram-sha-256

If you changed the password, reset it explicitly rather than assuming it took:

sudo -u postgres psql -c "ALTER USER appuser WITH PASSWORD 'new-password';"

Opening the firewall

Postgres listening isn't enough if UFW or your cloud provider's security group is blocking port 5432. Scope this the same way you scoped pg_hba.conf — don't open 5432 to the internet.

sudo ufw allow from 203.0.113.10 to any port 5432 proto tcp
sudo ufw status

If you're fronting the app on the same VPS as the database (the common case), you don't need to open 5432 externally at all — just let the app connect over 127.0.0.1 and skip remote access entirely. That's the safest setup by far.

Backups with pg_dump

Set up a daily dump before you need it, not after a bad migration wipes a table:

sudo -u postgres pg_dump appdb | gzip > /var/backups/appdb-$(date +%F).sql.gz

Restore with:

gunzip -c /var/backups/appdb-2026-07-20.sql.gz | sudo -u postgres psql appdb

Automate it with a cron entry and ship the file off the VPS with rclone or similar — a backup that lives only on the server it's protecting isn't much of a backup.

Quick reference: common errors

Prevention checklist

• Keep the database on the same private network as the app when you can — remote access should be the exception, not the default.
• Scope every pg_hba.conf rule and firewall rule to a specific IP or range, never 0.0.0.0/0.
• Use scram-sha-256, not md5, for any new setup — md5 is legacy at this point.
• Automate pg_dump and actually test a restore occasionally; an untested backup is a guess.
• If you expect more than a handful of concurrent app instances, put PgBouncer in front rather than raising max_connections indefinitely.

If you'd rather not manage any of this yourself, Getwebup's managed VPS plans include database setup and monitoring as part of onboarding — worth a look if PostgreSQL is powering a production app and you don't want 2 a.m. pages over a misconfigured pg_hba.conf.