PostgreSQL is a powerful open-source relational database used by Django, Rails, many Node.js apps and self-hosted tools. This guide installs it, secures access, applies sensible tuning and sets up backups.
1. Install
Ubuntu and Debian
The distribution package is fine for most uses:
apt update
apt install -y postgresql
systemctl status postgresqlFor the newest major version, add the official PostgreSQL APT repository (PGDG):
apt install -y postgresql-common
/usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
apt install -y postgresql-17AlmaLinux and Rocky Linux
Install the PGDG repository RPM for your release (from postgresql.org), then:
dnf -qy module disable postgresql
dnf install -y postgresql17-server
/usr/pgsql-17/bin/postgresql-17-setup initdb
systemctl enable --now postgresql-172. Create a role and database
PostgreSQL creates a postgres superuser that authenticates via the system account:
sudo -u postgres psqlCREATE ROLE appuser WITH LOGIN PASSWORD 'a-long-random-password';
CREATE DATABASE appdb OWNER appuser;
\qTest: psql -h 127.0.0.1 -U appuser -d appdb.
On PostgreSQL 15+, ordinary users can’t create objects in the public schema of databases they don’t own. Making the app role the database owner (as above) avoids permission surprises.
3. Understand authentication (pg_hba.conf)
Find the config files:
sudo -u postgres psql -c 'SHOW hba_file;' -c 'SHOW config_file;'Typical secure entries:
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer
local all all scram-sha-256
host all all 127.0.0.1/32 scram-sha-256
host all all ::1/128 scram-sha-256scram-sha-256 is the modern password method; avoid trust and md5.
4. Remote access (only if needed)
By default PostgreSQL listens on localhost only. To allow an app server to connect:
-
In
postgresql.conf:listen_addresses = 'localhost,10.10.0.2'(prefer a private/tunnel address). -
In
pg_hba.conf, allow just that client — and require TLS if it’s not on a private network:hostssl appdb appuser 10.10.0.1/32 scram-sha-256 -
Firewall port 5432 to that IP only:
ufw allow from 10.10.0.1 to any port 5432 proto tcp -
Reload:
systemctl reload postgresql.
Never expose 5432 to the whole internet. A WireGuard tunnel between servers is the cleanest option — see site-to-site VPN tunnel.
5. Basic tuning
Defaults are conservative. For a server with 4 GB RAM shared with an application:
shared_buffers = 1GB # ~25% of RAM
effective_cache_size = 3GB # what the OS can cache (~50–75% of RAM)
work_mem = 16MB # per sort/hash operation — keep modest
maintenance_work_mem = 256MB
max_connections = 100
wal_compression = on
log_min_duration_statement = 500 # log queries slower than 500 msRestart after changing shared_buffers. Use PgBouncer if your app opens many short-lived connections. Find slow queries with EXPLAIN ANALYZE and the pg_stat_statements extension.
6. Backups
Logical dumps are simple and portable:
sudo -u postgres pg_dump -Fc appdb > /var/backups/appdb-$(date +%F).dump
# restore
sudo -u postgres pg_restore -d appdb --clean /var/backups/appdb-2026-11-04.dumpFor large or busy databases, consider pgBackRest or Barman for physical backups with point-in-time recovery. Always copy backups off the server — see backing up a VPS to cloud storage.
7. Upgrades
Minor versions (17.1 → 17.2) install with normal package updates. Major versions (16 → 17) need pg_upgrade or dump/restore; on Debian/Ubuntu, pg_upgradecluster automates it. Test on a copy first.
Troubleshooting
password authentication failed— check the user/password and that apg_hba.confline matches the connection type (local vs host).connection refused—listen_addresses, firewall, or the service isn’t running.permission denied for schema public— make the app role the database owner, or grantCREATEon the schema.
Prefer MariaDB? See installing MariaDB on Linux. Deploying a whole app? See full-stack deployment on a VPS.