A database is the part of your system where mistakes are permanent. Files can be re-downloaded and containers rebuilt; a database that has been quietly corrupt for three weeks takes your backups with it. This guide sets up PostgreSQL on a Linux server in the way that avoids the four failures that actually happen: it was exposed to the internet, the disk filled, the backup was never restored, and the application connected as superuser.
What this guide does not do. No replication, failover or high availability — this is one database on one machine, and if that machine dies you restore from backup. No performance work beyond the handful of settings whose defaults are actively wrong. And it is worth saying plainly: if the data matters commercially and nobody on your team wants to be the database administrator, a managed database is often the correct answer. Paying someone else for point-in-time recovery is not a defeat.
Where it should live
| Situation | Put it |
|---|---|
| One application, one server, modest traffic | On the same machine, over a Unix socket |
| Several applications sharing data | Its own machine on a private network |
| Anything where downtime costs money | A managed service |
| Development | A container you can throw away |
The first row covers far more real systems than people expect. A single Postgres beside the application, on a socket, with no network listener at all, is both the fastest option and the one with no attack surface.
1. Install it
sudo apt install postgresql # Debian, Ubuntu
sudo dnf install postgresql-server && sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql
sudo -u postgres psql -c 'SELECT version();'The distribution’s package is the right choice unless you need a version it does not carry, in which case use the PostgreSQL project’s own repository — and read How Packages Work first, because pinning it correctly is what stops it dragging in half the system.
Verify: systemctl status postgresql is active, and the SELECT version() query prints a version.
2. Create a role that is not a superuser
The application gets one role, owning one database, with no ability to create others or read anyone else’s. This costs thirty seconds and contains the damage when the application is compromised.
sudo -u postgres psql
CREATE ROLE myapp LOGIN PASSWORD 'generated-not-chosen';
CREATE DATABASE myapp OWNER myapp;
REVOKE ALL ON DATABASE myapp FROM PUBLIC;
\c myapp
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO myapp;
\du
\qThe two REVOKE lines matter more than they look: by default any role that can connect to a database can also create objects in its public schema. Recent PostgreSQL versions tightened this, but checking is cheap and assuming is not.
Verify: \du shows myapp with no attributes — no Superuser, no Create DB, no Create role. Connect as it and confirm CREATE DATABASE test; is refused.
3. Decide how it is reached — and check
Port 5432 does not go on the internet. Ever. Automated scanners find an exposed database within hours, and a weak password is then the only thing between a stranger and your data. If a database must be reachable from another machine, put it on a private network or a Tailscale interface, require TLS, and firewall it to the specific hosts that need it — in that order, all three.
PostgreSQL listens on localhost by default, which is correct. Two files control access, and they do different jobs: postgresql.conf decides which addresses it binds, and pg_hba.conf decides who may authenticate and how.
# postgresql.conf
listen_addresses = 'localhost' # or '10.0.0.5' for a private interface
# pg_hba.conf — first matching line wins, so order matters
local all postgres peer
local myapp myapp scram-sha-256
host myapp myapp 127.0.0.1/32 scram-sha-256
# hostssl myapp myapp 10.0.0.0/24 scram-sha-256 # only if truly neededlocal means the Unix socket — the best option when the application is on the same machine, since there is no network path to attack at all. Reload after editing, and remember that pg_hba.conf is read top to bottom: a permissive line above a restrictive one wins.
sudo systemctl reload postgresql
ss -tlnp | grep 5432
sudo -u postgres psql -c 'SELECT * FROM pg_hba_file_rules;' # parse errors show hereVerify: ss -tlnp shows 5432 on 127.0.0.1 or a private address only, and from any other machine psql -h your-public-ip -U myapp fails to connect.
4. Back it up properly, and restore once
Copying /var/lib/postgresql while the server is running does not produce a usable backup. Take a logical dump instead — it is consistent as of the moment it starts, regardless of what else is happening.
sudo -u postgres pg_dump -Fc myapp > /var/backups/myapp-$(date +%F).dump
sudo -u postgres pg_dumpall --globals-only > /var/backups/globals.sql
restic backup /var/backups --tag db-Fc is the custom format: compressed, and restorable selectively with pg_restore. The --globals-only dump catches roles and passwords, which a per-database dump does not contain — restore without it and nothing can log in.
Now do the part everyone skips:
sudo -u postgres createdb myapp_restoretest
sudo -u postgres pg_restore -d myapp_restoretest /var/backups/myapp-2026-08-30.dump
sudo -u postgres psql -d myapp_restoretest -c '\dt'
sudo -u postgres psql -d myapp_restoretest -c 'SELECT count(*) FROM users;'
sudo -u postgres dropdb myapp_restoretestVerify: the restored database has the tables you expect and the row counts are plausible. Schedule the dump with a timer that alerts on failure, and repeat this restore test quarterly.
Dumps give you last night. If losing a day of data is unacceptable, you want continuous archiving of the write-ahead log for point-in-time recovery — substantially more machinery, and the moment to reconsider the managed option in the box above.
5. Change the default settings
PostgreSQL ships conservative defaults so it starts on anything. Four are worth revisiting; the rest can wait until you have a measured problem.
# postgresql.conf, for a machine with 8 GB of RAM
shared_buffers = 2GB # about 25% of RAM
effective_cache_size = 6GB # a hint, not an allocation
work_mem = 16MB # PER sort, per connection — be careful
log_min_duration_statement = 500 # log anything slower than 0.5swork_mem is the one that bites: it is allocated per sort operation, so a large value multiplied by many concurrent connections is how a server runs out of memory. Raise it cautiously.
log_min_duration_statement is the highest-value line in the file. Within a day you will know exactly which query is slow, instead of guessing — see Reading Logs.
If you hit “too many connections”, the fix is almost never raising max_connections — each one costs a process and memory. Put PgBouncer in front and let a hundred application connections share twenty real ones.
Verify: after systemctl restart postgresql, SHOW shared_buffers; and SHOW work_mem; return the values you set.
6. Watch the three things that kill databases
df -h /var/lib/postgresql # disk: the number one killer
SELECT pg_size_pretty(pg_database_size('myapp'));
SELECT count(*) FROM pg_stat_activity; # connections
SELECT pid, now()-query_start AS age, state, left(query,60)
FROM pg_stat_activity
WHERE state != 'idle' ORDER BY age DESC LIMIT 5;
SELECT relname, last_autovacuum FROM pg_stat_user_tables ORDER BY 2 NULLS FIRST;A full disk stops PostgreSQL writing and can take the whole machine down with it, so alert on the filesystem holding the data directory at 80% rather than 95% — the check in Monitoring a Server covers it, and Disk Space covers finding what filled it.
Verify: fill a test filesystem to your alert threshold and confirm the alert arrives.
7. Know what a major upgrade involves
Minor updates are ordinary package updates and need only a restart. Major versions change the on-disk format, so the new server cannot read the old data directory — you run pg_upgrade, or dump and restore.
This is the trap in distribution upgrades. Moving a Debian or Ubuntu release forward can install a newer PostgreSQL alongside the old one, leaving your data in the previous version’s cluster and the application connecting to an empty new one. Check which clusters exist before assuming the upgrade did what you wanted:
pg_lsclusters # Debian and Ubuntu
sudo -u postgres psql -c 'SHOW data_directory;'Verify: take a dump before any major upgrade, and after it confirm your row counts match what they were.
If you are running MariaDB instead
| PostgreSQL | MariaDB / MySQL |
|---|---|
pg_hba.conf | Per-user host in the GRANT |
listen_addresses | bind-address |
pg_dump -Fc | mysqldump --single-transaction |
shared_buffers | innodb_buffer_pool_size |
log_min_duration_statement | slow_query_log + long_query_time |
The --single-transaction flag is not optional on InnoDB — without it the dump is not consistent. And use utf8mb4, never utf8, as covered in the LEMP stack guide.
If you want the full picture — all eight stages between a client sending COMMIT and data you could still restore a year later, including the one fsync the client actually waits for, what continuous archiving really involves, why checkpoints stall a server that is otherwise healthy, and transaction ID wraparound — that is a separate, much longer article: Running PostgreSQL Properly: A Transaction’s Path to Durability. It also lists the widely repeated tuning advice that has quietly stopped being true.
Before you call it done
- The application’s role is not a superuser and owns only its own database
ss -tlnpproves nothing is listening on a public address- A nightly dump runs, alerts on failure, and includes the globals
- You have restored that dump into a scratch database and counted rows
- Disk space on the data directory is monitored, and alerts before it is critical
- Slow queries are being logged
- You know which PostgreSQL version and which cluster your data is actually in
Related
- Set Up a LEMP Stack — the MariaDB equivalent, in context
- Automated Backups — scheduling and offsite copies
- restic — where the dumps should end up
- Monitoring a Server — disk alerts, which is the one that matters here