Run PostgreSQL properly on a Linux server: install, lock down, back up, monitor and patch

Installing PostgreSQL takes two minutes. Running it well takes a few more decisions: who may connect from where, how much memory it may use, how you know last night’s backup actually restores, and how you stay on the current patch release. This tutorial walks through all of them on one Debian or Ubuntu server, for one application, in the order you would do it on a real machine.

It is written for developers and administrators who have used psql before and now have to own a database server. Everything uses the packages and tools that ship with PostgreSQL; there are no extra products to install.

What you will build

• A PostgreSQL 18 server on db1.example.com (10.0.0.11), installed from the PostgreSQL project’s own apt repository (PGDG).
• A login role appuser that owns a database appdb, with a password stored as a SCRAM-SHA-256 hash.
• A pg_hba.conf that allows exactly one remote client, the application server app1.example.com (10.0.0.21), over SSL, and rejects everyone else.
• A handful of documented settings changed with ALTER SYSTEM: memory, slow-query logging and lock-wait logging.
• A nightly backup job (pg_dump plus global objects) with rotation, and a restore test that loads the newest dump into a scratch database and compares row counts.
• A set of monitoring queries and a routine for applying minor updates.

Before you start

• A server with Ubuntu 24.04 LTS or Debian 12/13, at least 2 CPU cores and 4 GB RAM, sudo access and a static private IP. The examples use 10.0.0.11 for the database server and 10.0.0.21 for the application server.
• A second machine (the app server) to test the remote connection from. Any Linux host with network access to port 5432 works.
• Disk space for the data and at least two copies of your largest database dump. Put backups on a different disk or copy them off the server.
• Time: about 90 minutes for a first run, plus a day to see the first nightly backup.
• Knowledge: basic shell use, editing files with sudo, and simple SQL. It helps to know what a role, a database and a connection string are, and how host-based authentication (pg_hba.conf) decides who may log in.
• Red Hat family systems (RHEL, Rocky, AlmaLinux) work the same way in principle, but package names, paths and the service name differ; use the PGDG download page’s selector for your distribution.

Step 1: Install PostgreSQL 18 from the PGDG repository

Distribution packages are fine, but they pin you to whatever major version the distribution shipped. The PGDG repository carries every supported major version and publishes minor (bug-fix and security) releases as soon as they are out. The postgresql-common package contains a helper script that adds the repository and its signing key for you:

sudo apt update
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt update
sudo apt install -y postgresql-18

On Debian and Ubuntu the package creates a cluster named main and starts it. Check it:

pg_lsclusters
sudo -u postgres psql -c 'SELECT version();'

pg_lsclusters should list version 18, cluster main, port 5432 and status online, with data in /var/lib/postgresql/18/main. The configuration files live in /etc/postgresql/18/main/. The service for this cluster is postgresql@18-main.

sudo -u postgres psql works without a password because the default pg_hba.conf uses peer authentication for local socket connections: the operating-system user postgres maps to the database superuser postgres. Keep it that way; do not give the postgres role a password unless you need one.

Step 2: Create a role and a database for the application

The application should never connect as postgres. Create a login role and let it own its own database. createuser --pwprompt asks for the password and sends it to the server already hashed, so it does not end up in your shell history or the server log the way a literal CREATE ROLE ... PASSWORD '...' statement can:

sudo -u postgres createuser --pwprompt appuser
sudo -u postgres createdb --owner=appuser appdb

By default every role may connect to every database (the CONNECT privilege is granted to PUBLIC). Take that away so that only the owner and roles you name explicitly can connect:

sudo -u postgres psql -d appdb <<'SQL'
REVOKE CONNECT ON DATABASE appdb FROM PUBLIC;
GRANT CONNECT ON DATABASE appdb TO appuser;
SHOW password_encryption;
SELECT rolname, left(rolpassword, 14) AS hash_prefix
FROM pg_authid WHERE rolname = 'appuser';
SQL

password_encryption should say scram-sha-256 (the default) and the stored hash should start with SCRAM-SHA-256$. If it starts with md5, the password was set under an old setting; set it again with \password appuser in psql.

Since PostgreSQL 15, ordinary roles can no longer create objects in the public schema of a new database, but the database owner can. Because appuser owns appdb, it can create its tables there. Create a small table now so later steps have data to back up and count:

psql -h localhost -U appuser -d appdb <<'SQL'
CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer   text        NOT NULL,
  total      numeric(10,2) NOT NULL CHECK (total >= 0),
  created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO orders (customer, total)
SELECT 'customer ' || g, (g % 500) + 0.99
FROM generate_series(1, 10000) AS g;
SELECT count(*) FROM orders;
SQL

This connects over TCP to localhost and asks for the password you set. The final query should print 10000. If you get a peer or no pg_hba.conf entry error instead, look at Step 3 first: the packaged file normally allows password logins from 127.0.0.1.

Step 3: Lock down pg_hba.conf

pg_hba.conf is read from top to bottom, and the first line that matches the connection type, database, user and client address decides; there is no fall-through to later lines. A good file for this server lets the postgres OS user in through the local socket, lets local users log in with passwords, lets exactly one remote host reach exactly one database as exactly one role over SSL, and rejects everything else explicitly.

Make a copy first, then replace the rules in /etc/postgresql/18/main/pg_hba.conf:

sudo cp -a /etc/postgresql/18/main/pg_hba.conf /etc/postgresql/18/main/pg_hba.conf.orig
sudoedit /etc/postgresql/18/main/pg_hba.conf

# 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-256
hostssl appdb     appuser   10.0.0.21/32     scram-sha-256
host    all       all       0.0.0.0/0        reject
host    all       all       ::/0             reject

The hostssl line only matches encrypted connections. The Debian packages normally turn ssl on with a self-signed certificate; check with SHOW ssl; and, for production, replace the certificate with one from your own CA so clients can verify the server (sslmode=verify-full).

Before reloading, ask the server how it parses the file on disk. Any row with a non-empty error column is a line it would ignore:

sudo -u postgres psql -c "SHOW ssl;" -c "SELECT line_number, type, database, user_name, address, auth_method, error FROM pg_hba_file_rules;"
sudo -u postgres psql -c 'SELECT pg_reload_conf();'

A reload is enough for pg_hba.conf; existing sessions keep running and new connections use the new rules.

Step 4: Listen on the network and open the firewall

Out of the box the server listens on localhost only, so no rule in pg_hba.conf can match a remote client yet. Add the private address. ALTER SYSTEM writes the value to postgresql.auto.conf in the data directory, which is read after postgresql.conf; listen_addresses only changes at server start, so restart:

sudo -u postgres psql -c "ALTER SYSTEM SET listen_addresses = 'localhost,10.0.0.11';"
sudo systemctl restart postgresql@18-main
sudo ss -ltnp | grep 5432

ss should now show the server listening on 127.0.0.1:5432 and 10.0.0.11:5432. Never listen on a public interface for a database that only an internal app uses.

Add a host firewall rule as a second layer, so that even a mistake in pg_hba.conf cannot expose the port. With ufw:

sudo ufw allow OpenSSH
sudo ufw allow from 10.0.0.21 to any port 5432 proto tcp
sudo ufw enable
sudo ufw status numbered

Step 5: Connect from the application server

On app1.example.com, install the client and store the password in a ~/.pgpass file of the user the application runs as. libpq ignores the file if group or others can read it:

sudo apt install -y postgresql-client
printf '%s\n' 'db1.example.com:5432:appdb:appuser:CHANGE_ME' > ~/.pgpass
chmod 0600 ~/.pgpass
psql "host=db1.example.com dbname=appdb user=appuser sslmode=require" -c '\conninfo' -c 'SELECT count(*) FROM orders;'

\conninfo should report an SSL connection and the count should be 10000. Your application’s connection string uses the same parameters; most drivers accept sslmode too.

Now prove the negative cases. From any other host (or from app1 with sslmode=disable, or as another database such as postgres) the server must refuse the connection with an error that begins no pg_hba.conf entry for host. Refusals and their reasons also appear in the server log: sudo tail /var/log/postgresql/postgresql-18-main.log.

Step 6: Tune a few documented settings

PostgreSQL’s defaults are deliberately small so it starts on any machine. On a dedicated server, a few changes matter most; everything else can wait until you have measurements. The manual’s guidance:

• shared_buffers: on a dedicated server with 1 GB of RAM or more, a reasonable starting value is 25% of RAM; more than 40% is unlikely to help. Needs a restart.
• work_mem (default 4 MB): memory per sort or hash operation, and one query can use several of them at once, per connection. Raise it carefully, for example to 16 MB, and watch temp_bytes in Step 9.
• maintenance_work_mem (default 64 MB): used by VACUUM, CREATE INDEX and restores. Safe to set much larger than work_mem, but each autovacuum worker may use it too.
• log_min_duration_statement (default -1, off): log every statement slower than the threshold.
• log_lock_waits (default off): log when a session waits longer than deadlock_timeout for a lock.

For a server with 8 GB of RAM:

sudo -u postgres psql <<'SQL'
ALTER SYSTEM SET shared_buffers = '2GB';
ALTER SYSTEM SET work_mem = '16MB';
ALTER SYSTEM SET maintenance_work_mem = '512MB';
ALTER SYSTEM SET log_min_duration_statement = '500ms';
ALTER SYSTEM SET log_lock_waits = on;
SELECT pg_reload_conf();
SELECT name, setting, unit, pending_restart
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem',
               'log_min_duration_statement', 'log_lock_waits');
SQL

After the reload, the four reloadable settings show their new values and shared_buffers shows pending_restart = t. Restart in a quiet moment and check again:

sudo systemctl restart postgresql@18-main
sudo -u postgres psql -c 'SHOW shared_buffers;'

To undo a setting, run ALTER SYSTEM RESET name; and reload or restart. Keeping all changes in postgresql.auto.conf means postgresql.conf stays identical to the package default, which makes upgrades easier to review.

Step 7: Schedule nightly backups

A logical backup with pg_dump is the simplest backup that also survives a move to another server or major version. It does not include roles, so the script also saves global objects with pg_dumpall --globals-only. The custom format (-Fc) is compressed and lets pg_restore pick single tables or run in parallel.

Save this as /usr/local/bin/pg-backup.sh. It writes each dump under a temporary name, checks that pg_restore can read its table of contents, and only then gives it its final name, so a half-written file never looks like a good backup. The last command deletes dumps older than KEEP_DAYS: it is limited to the backup directory and to the two file name patterns the script creates, but check BACKUP_DIR before you first run it.

#!/usr/bin/env bash
set -euo pipefail

BACKUP_DIR=/var/backups/postgresql
KEEP_DAYS=14
DATABASES=(appdb)
STAMP=$(date +%Y-%m-%d_%H%M)

umask 077
mkdir -p "$BACKUP_DIR"

pg_dumpall --globals-only -f "$BACKUP_DIR/globals_$STAMP.sql"

for db in "${DATABASES[@]}"; do
  out="$BACKUP_DIR/${db}_$STAMP.dump"
  pg_dump -Fc -f "$out.partial" "$db"
  pg_restore --list "$out.partial" > /dev/null
  mv "$out.partial" "$out"
  echo "backup ok: $out ($(du -h "$out" | cut -f1))"
done

find "$BACKUP_DIR" -maxdepth 1 -type f \( -name '*.dump' -o -name 'globals_*.sql' \) \
  -mtime +"$KEEP_DAYS" -print -delete

sudo install -m 0755 pg-backup.sh /usr/local/bin/pg-backup.sh
sudo install -d -o postgres -g postgres -m 0700 /var/backups/postgresql
sudo -u postgres /usr/local/bin/pg-backup.sh
sudo ls -l /var/backups/postgresql

The manual run should print backup ok: /var/backups/postgresql/appdb_<date>.dump with a size. Then schedule it as the postgres user, which authenticates through the local socket with peer and needs no stored password:

echo '30 2 * * * postgres /usr/local/bin/pg-backup.sh >> /var/log/pg-backup.log 2>&1' | sudo tee /etc/cron.d/pg-backup
sudo touch /var/log/pg-backup.log
sudo chown postgres:postgres /var/log/pg-backup.log

A backup that stays on the database server is lost together with it. Copy /var/backups/postgresql to another machine or object storage every night as well, for example with your existing backup agent or rsync over SSH.

Step 8: Test a restore into a scratch database

A dump you have never restored is a guess. The script below restores the newest dump into a throwaway database called restore_test, reports how long that took, compares the row count of a key table with the live database and drops the scratch copy again. Warning: it drops any existing database named restore_test; make sure no real database has that name. Adjust the table in count_rows to one that matters for your application.

#!/usr/bin/env bash
set -euo pipefail

BACKUP_DIR=/var/backups/postgresql
SOURCE_DB=appdb
SCRATCH_DB=restore_test

latest=$(ls -1t "$BACKUP_DIR/${SOURCE_DB}"_*.dump | head -n 1)
echo "testing $latest"

dropdb --if-exists "$SCRATCH_DB"
createdb -T template0 "$SCRATCH_DB"

start=$(date +%s)
pg_restore --no-owner --exit-on-error -d "$SCRATCH_DB" "$latest"
echo "restore took $(( $(date +%s) - start )) s"

count_rows() {
  psql -X -At -d "$1" -c "SELECT count(*) FROM public.orders"
}
live=$(count_rows "$SOURCE_DB")
restored=$(count_rows "$SCRATCH_DB")
echo "orders: live=$live restored=$restored"

dropdb "$SCRATCH_DB"

sudo install -m 0755 pg-restore-test.sh /usr/local/bin/pg-restore-test.sh
sudo -u postgres /usr/local/bin/pg-restore-test.sh

Expect lines like testing /var/backups/postgresql/appdb_...dump, restore took 1 s and orders: live=10000 restored=10000. The counts can differ slightly on a busy system because the live table keeps changing after the dump; a restored count of zero or an error from pg_restore is what you are looking for. --exit-on-error makes pg_restore stop at the first problem instead of continuing and printing an error count at the end.

Run it weekly from cron (for example 0 5 * * 0 postgres /usr/local/bin/pg-restore-test.sh) and write down the restore time: it is the minimum outage you would have if you needed this dump for real.

Step 9: Add monitoring queries

PostgreSQL exposes its runtime state in statistics views. These queries are a good first dashboard; run them with sudo -u postgres psql -d appdb, or let your monitoring agent run them as a role that is a member of the predefined role pg_monitor (GRANT pg_monitor TO monitor_role;).

Connections by database and state; many idle in transaction sessions point to application bugs that hold locks:

SELECT datname, usename, state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY datname, usename, state
ORDER BY count(*) DESC;

Statements running longer than five minutes, and what they wait for:

SELECT pid, usename, client_addr, now() - query_start AS runtime,
       wait_event_type, left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
  AND now() - query_start > interval '5 minutes'
ORDER BY runtime DESC;

Cache hit ratio, deadlocks and temporary-file usage per database. A ratio well below 0.99 on an OLTP database, or steadily growing temp_bytes, are reasons to look at memory settings and queries:

SELECT datname, numbackends, xact_commit, deadlocks,
       pg_size_pretty(temp_bytes) AS temp_written,
       round(blks_hit::numeric / nullif(blks_hit + blks_read, 0), 4) AS cache_hit_ratio,
       pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_stat_database
WHERE datname IS NOT NULL;

Tables with many dead rows and when autovacuum last cleaned them:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, seq_scan
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Settings changed but not yet active:

SELECT name, setting FROM pg_settings WHERE pending_restart;

To stop a runaway query, SELECT pg_cancel_backend(pid); cancels the statement and pg_terminate_backend(pid) ends the whole session; use the second only when cancelling does not help.

Step 10: Apply minor updates

Minor releases (18.1, 18.2, …) contain only bug and security fixes. The project recommends always running the current minor release of your major version, and a minor update needs no dump and restore: stop the server, install the new binaries, start it again. Read the release notes first, because occasionally a release asks for an extra step such as reindexing.

sudo -u postgres psql -c 'SHOW server_version;'
sudo apt update
apt list --upgradable 2>/dev/null | grep -i postgresql
sudo -u postgres /usr/local/bin/pg-backup.sh
sudo apt install --only-upgrade postgresql-18 postgresql-client-18
sudo -u postgres psql -c 'SHOW server_version;'

The Debian packaging normally restarts the cluster during the upgrade. If SHOW server_version still prints the old number, restart it yourself with sudo systemctl restart postgresql@18-main. Plan updates for a maintenance window: the restart drops all connections for a few seconds, so the application must reconnect.

Major upgrades (18 to 19) are a different job: the on-disk format changes, so you need pg_upgradecluster, pg_upgrade or a dump and restore. Do not let unattended upgrades install a new major version for you; the versioned package name postgresql-18 keeps you on 18.

Check that it works

1. On db1: pg_lsclusters shows 18/main online, and sudo ss -ltnp | grep 5432 shows only 127.0.0.1 and 10.0.0.11.
2. On db1: SELECT * FROM pg_hba_file_rules WHERE error IS NOT NULL; returns no rows.
3. On app1: psql "host=db1.example.com dbname=appdb user=appuser sslmode=require" -c '\conninfo' connects over SSL without prompting for a password.
4. From any other host, or to any other database, the connection is refused.
5. sudo ls -l /var/backups/postgresql shows a dump from last night, and /var/log/pg-backup.log contains a backup ok line.
6. sudo -u postgres /usr/local/bin/pg-restore-test.sh restores it and prints matching row counts.
7. SHOW shared_buffers; returns your value and SELECT name FROM pg_settings WHERE pending_restart; returns nothing.

If something goes wrong

• The app server times out instead of getting an error. The packet never reaches PostgreSQL: check listen_addresses (and that you restarted after changing it), ss -ltnp, ufw status and any network firewall between the hosts.
• The error starts with no pg_hba.conf entry for host. The server is reachable but no line matches. Compare the client address, database, user and SSL state in the message with your hostssl line; a client connecting without SSL never matches a hostssl line.
• password authentication failed for user "appuser". A line matched, but the password was wrong, the role has no password, or ~/.pgpass was ignored because its permissions are not 0600. Reset the password with \password appuser as postgres.
• The server does not start after a settings change. Read /var/log/postgresql/postgresql-18-main.log. A typical cause is shared_buffers larger than the machine can provide. Fix it with ALTER SYSTEM RESET shared_buffers; if you can still connect; otherwise remove the line from postgresql.auto.conf in the data directory and start again.
• The backup job runs but no file appears. Cron jobs do not show their errors; look in /var/log/pg-backup.log. The usual causes are a backup directory not owned by postgres and a /etc/cron.d file without the user field.
• The restore test fails with role ... does not exist. The dump references a role that is missing on the target. The test script uses --no-owner to avoid this; for a real restore on a new server, load globals_*.sql with psql first.

Clean up

To remove what this tutorial created but keep PostgreSQL:

sudo rm -f /etc/cron.d/pg-backup
sudo -u postgres dropdb --if-exists restore_test
sudo -u postgres psql -c "ALTER SYSTEM RESET ALL;"
sudo systemctl restart postgresql@18-main
sudo cp -a /etc/postgresql/18/main/pg_hba.conf.orig /etc/postgresql/18/main/pg_hba.conf
sudo -u postgres psql -c 'SELECT pg_reload_conf();'
sudo ufw delete allow from 10.0.0.21 to any port 5432 proto tcp

Warning: the next commands delete the application database, its role and all backups permanently. Make sure you are on the right server (hostname) and that nothing else uses appdb (SELECT * FROM pg_stat_activity WHERE datname = 'appdb';):

sudo -u postgres dropdb appdb
sudo -u postgres dropuser appuser
sudo rm -rf /var/backups/postgresql

Removing the server entirely (sudo apt purge postgresql-18) asks whether to delete the data directories as well; read the prompt carefully.

Next steps

• Add point-in-time recovery with continuous WAL archiving and pg_basebackup, or a tool such as pgBackRest, so you can restore to any moment rather than to last night.
• Set up a streaming-replication standby for faster failover and as a place to run the restore test.
• Enable the pg_stat_statements extension to see which queries use the most time in total, not only the slowest single runs.
• Replace the self-signed certificate with one from your CA and switch clients to sslmode=verify-full.
• Feed the monitoring queries into your monitoring system and alert on backup age, connection count and replication lag.

Official documentation

• PostgreSQL: Linux downloads (Ubuntu)
• PostgreSQL: Resource consumption settings
• PostgreSQL: Versioning policy