Skip to content
Irregularpedia Logo Irregularpedia

PostgreSQL Server Guide

PostgreSQL Server Guide

Section titled “PostgreSQL Server Guide”

PostgreSQL is the default choice for a serious self-hosted relational database: robust, standards-compliant, and tunable. This guide focuses on the high-impact decisions — install, the handful of settings worth changing, access control, backups you can actually restore, and monitoring — rather than every parameter.

Installation

Section titled “Installation”

Use the official PostgreSQL apt repository (newer than distro packages) or the official Docker image:

Terminal window
# Debian/Ubuntu, official PGDG repo
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt install -y postgresql-17


# or Docker, with a bind-mounted data directory
docker run -d --name pg -e POSTGRES_PASSWORD=secret \
  -v /srv/pgdata:/var/lib/postgresql/data postgres:17

Configuration and tuning

Section titled “Configuration and tuning”

A handful of postgresql.conf settings dominate performance. Start from your RAM and workload; the PGTune tool generates a good baseline.

# postgresql.conf — high-impact settings (example: 16 GB RAM server)
shared_buffers = 4GB              # ~25% of RAM
effective_cache_size = 12GB       # ~50-75% of RAM (a hint to the planner)
work_mem = 32MB                   # per sort/hash op — multiply by connections, be careful
maintenance_work_mem = 1GB        # VACUUM/CREATE INDEX
max_connections = 100             # keep modest; use a pooler instead of raising this
wal_compression = on

Access control (pg_hba.conf)

Section titled “Access control (pg_hba.conf)”

pg_hba.conf decides who can connect, from where, and how. Use scram-sha-256 and restrict by network; never use trust on anything reachable.

# TYPE  DATABASE  USER   ADDRESS         METHOD
local   all       all                    peer
host    appdb     appuser 10.0.0.0/24    scram-sha-256
# host  all       all     0.0.0.0/0      trust   # NEVER do this

Backups

Section titled “Backups”

Have both, and test restores — an untested backup is a guess.

  • Logical — pg_dump (one database) / pg_dumpall (whole cluster). Portable across versions, good for smaller datasets.
  • Physical + PITR — pg_basebackup plus WAL archiving gives point-in-time recovery; tools like pgBackRest or Barman manage it at scale.
Terminal window
pg_dump -Fc appdb > appdb.dump          # custom-format logical backup
pg_restore -d appdb appdb.dump          # restore it

Enable continuous archiving for PITR:

wal_level = replica
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'

Store backups encrypted and off-site — see Backups with Restic or Borg, and fold restores into your Disaster Recovery drills.

Connection pooling

Section titled “Connection pooling”

Each PostgreSQL connection is a process with real overhead, so apps that open many short-lived connections need a pooler. PgBouncer in transaction mode is the common choice (note: transaction pooling disables session features like prepared statements and LISTEN/NOTIFY unless configured for it).

Monitoring

Section titled “Monitoring”
-- Enable once (shared_preload_libraries = 'pg_stat_statements'), then:
SELECT query, calls, mean_exec_time
FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;  -- slowest queries


SELECT pid, state, query FROM pg_stat_activity
WHERE state != 'idle' ORDER BY query_start;                     -- what's running now

Watch pg_stat_activity (long-running queries/locks), pg_stat_user_tables (dead tuples / autovacuum health), and pg_stat_statements (slow queries). Healthy autovacuum is critical — bloat and transaction-ID wraparound are the classic neglected-Postgres failures.

Major version upgrades

Section titled “Major version upgrades”

Use pg_upgrade (fast, in-place) or logical replication (near-zero-downtime). Always test the upgrade on a copy, take a backup first, and have a rollback plan before touching production.

Section titled “Related”