mrvitej.in
Happy Dussehra! May good always win. Happy Diwali! Wishing you a bright year ahead. Merry Christmas! Warm wishes from Vizag. Happy New Year! Here’s to a great year ahead.

Archive · DevOps

PostgreSQL 16: Install, Secure and Tune on Ubuntu 22.04

A production-minded PostgreSQL 16.4 install from the PGDG repository with scram authentication, pg_hba rules, memory tuning, pgBackRest backups and a streaming replica.

3 min read

  • PostgreSQL 16.4
  • Ubuntu 22.04 LTS
  • pg_hba.conf
  • pgBackRest
  • PgBouncer

PostgreSQL is the default choice for a reliable open-source relational database. This post installs PostgreSQL 16 the way a small production service needs it: a supported package source, safe authentication, sensible memory settings, backups with point-in-time recovery, and a replica.

Versions in use on this date. PostgreSQL 16.4 was released on 8 August 2024 together with the other supported minor updates. PostgreSQL 17 is in its final beta and not yet released. Ubuntu 22.04 ships PostgreSQL 14 in its own repository, so use the official PGDG repository to get 16. Each major version is supported for five years; PostgreSQL 16 is supported until November 2028.

Architecture

flowchart TB
  APP[Applications] -->|6432 pooled| PB[PgBouncer<br/>transaction pooling]
  PB -->|5432 scram-sha-256 TLS| PRI[Primary PostgreSQL 16]
  PRI -->|streaming replication<br/>replication slot| REP[Standby<br/>hot standby reads]
  PRI -->|WAL archive and backups| PGB[pgBackRest repository<br/>object storage or backup host]
  REP -.->|can restore from| PGB
  MON[postgres_exporter and Prometheus] -.-> PRI

Install from PGDG

sudo apt install -y curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt jammy-pgdg main" \
  | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update && sudo apt install -y postgresql-16
sudo systemctl status postgresql@16-main
sudo -u postgres psql -c "select version();"

Ubuntu uses a cluster layout: configuration in /etc/postgresql/16/main/, data in /var/lib/postgresql/16/main. Put data on its own XFS or ext4 volume, and WAL on fast storage where possible.

Create roles and a database

sudo -u postgres psql <<'SQL'
CREATE ROLE app_owner LOGIN PASSWORD 'change-me-long-random';
CREATE DATABASE shop OWNER app_owner;
\c shop
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO app_owner;
SQL

Use separate roles: an owner for migrations, a limited role for the running application with only the privileges it needs. Since PostgreSQL 15 the public schema no longer grants CREATE to everyone, but be explicit anyway.

Network and authentication

/etc/postgresql/16/main/postgresql.conf:

listen_addresses = 'localhost,10.0.1.31'
password_encryption = scram-sha-256
ssl = on
ssl_cert_file = '/etc/ssl/pg/server.crt'
ssl_key_file  = '/etc/ssl/pg/server.key'

pg_hba.conf is read top to bottom, first match wins. Allow only what you need:

# TYPE   DATABASE     USER         ADDRESS          METHOD
local    all          postgres                      peer
hostssl  shop         app_owner    10.0.1.0/24      scram-sha-256
hostssl  replication  replicator   10.0.1.32/32     scram-sha-256
host     all          all          0.0.0.0/0        reject

Reload with sudo systemctl reload postgresql@16-main and test using psql "host=10.0.1.31 dbname=shop user=app_owner sslmode=verify-full sslrootcert=ca.crt". Never use trust for network connections.

Memory and WAL tuning (starting points for 16 GB RAM)

shared_buffers = 4GB
effective_cache_size = 11GB
work_mem = 16MB
maintenance_work_mem = 1GB
max_connections = 200
wal_level = replica
max_wal_size = 8GB
checkpoint_timeout = 15min
random_page_cost = 1.1          # SSD
log_min_duration_statement = 500
log_checkpoints = on

work_mem is per sort/hash node per connection, so a large value multiplies. Keep connections low with PgBouncer in transaction mode instead of raising max_connections. Enable pg_stat_statements (shared_preload_libraries, then CREATE EXTENSION) to find slow queries.

Backups with pgBackRest and WAL archiving

archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
# /etc/pgbackrest.conf
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=<from secret store>
start-fast=y
[main]
pg1-path=/var/lib/postgresql/16/main
sudo -u postgres pgbackrest --stanza=main stanza-create
sudo -u postgres pgbackrest --stanza=main --type=full backup
sudo -u postgres pgbackrest --stanza=main info

Schedule a weekly full and a daily differential backup, keep the repository off the database host (or in S3-compatible storage), and rehearse a point-in-time restore with --type=time --target="2024-09-01 12:00:00" on a spare server.

Streaming replica

On the primary: CREATE ROLE replicator REPLICATION LOGIN PASSWORD '...'; and SELECT pg_create_physical_replication_slot('standby1');. On the standby:

sudo systemctl stop postgresql@16-main && sudo rm -rf /var/lib/postgresql/16/main/*
sudo -u postgres pg_basebackup -h 10.0.1.31 -U replicator -D /var/lib/postgresql/16/main -R -S standby1 -X stream -P
sudo systemctl start postgresql@16-main

-R writes the standby settings. Check SELECT * FROM pg_stat_replication; on the primary. Replication is asynchronous by default, so promote with pg_ctl promote only after confirming the primary is really gone, and use a tool such as Patroni if you need automatic failover.

Failure modes

  • Disk full from WAL: a stopped standby with a replication slot or a failing archive_command makes WAL pile up; alert on pg_replication_slots and archive failures.
  • Connection storms: use PgBouncer and application-side pool limits.
  • Bloat: watch autovacuum and long transactions (pg_stat_activity).