PostgreSQL is the default choice for serious relational data — standards-compliant, extensible, and rock-solid. This guide runs it three ways and covers the administration that keeps it healthy.

Prerequisites

  • A Linux machine with sudo; Docker and a Kubernetes cluster for those sections.
  • Basic SQL comfort.

1. On Linux

sudo apt update && sudo apt install -y postgresql
sudo systemctl enable --now postgresql
sudo -u postgres psql            # superuser shell

Data lives under /var/lib/postgresql/<version>/main, config in postgresql.conf and pg_hba.conf (which controls who can connect, from where, and how — the #1 "why can't I log in" culprit).

2. In Docker

docker run -d --name pg \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=app \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  postgres:16

The named volume keeps your data across container restarts.

3. On Kubernetes with CloudNativePG

Use an operator — CloudNativePG is excellent and Kubernetes-native:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata: {name: app}
spec:
  instances: 3                 # 1 primary + 2 replicas, automatic failover
  storage: {size: 50Gi, storageClass: fast-ssd}
  backup:
    barmanObjectStore:         # continuous backup to S3 for point-in-time recovery
      destinationPath: s3://backups/pg
      s3Credentials: {accessKeyId: {name: s3, key: id}, secretAccessKey: {name: s3, key: secret}}

CloudNativePG handles failover, rolling upgrades, and continuous backup from one manifest.

Everyday administration

Roles and least privilege:

CREATE ROLE app LOGIN PASSWORD 'strong-password';
GRANT CONNECT ON DATABASE app TO app;
GRANT USAGE ON SCHEMA public TO app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app;

Backups — two kinds, know both:

pg_dump -Fc app > app.dump         # logical: portable, per-database
pg_restore -d app app.dump

For production you also want Point-In-Time Recovery: a base backup plus archived WAL lets you restore to any second — the thing that saves you after an accidental DELETE. Tools: pgBackRest, Barman (or the operator's built-in backup).

Replication in Postgres streams WAL to replicas — read scaling plus a failover target. Operators automate promotion; manually it's a primary_conninfo on a standby with a replication slot.

MVCC, index types & connection pooling

Three concepts explain most of Postgres's behavior in production.

MVCC (Multi-Version Concurrency Control) is why Postgres readers never block writers and vice versa: instead of updating a row in place, it writes a new version and marks the old one dead. That gives excellent concurrency, but it's also why VACUUM exists (below) and why an UPDATE that touches every row temporarily doubles a table's size. Understanding MVCC demystifies both.

Index types — Postgres has more than the default B-tree, and picking the right one is a superpower:

  • B-tree — the default; equality and range on ordinary columns.
  • GIN — for "does this contain..." queries over jsonb, arrays, and full-text search.
  • BRIN — tiny indexes for huge, naturally-ordered tables (time-series): great value on an append-only ts column.
  • partial (... WHERE status = 'active') and expression (... (lower(email))) indexes cover common query shapes cheaply.

Matching the index to the query is often a bigger win than any server-config tuning.

Connection pooling is not optional. Each Postgres connection is a full OS process — a few hundred is a lot. An app that opens connections freely will exhaust the server long before it runs out of real work. Put PgBouncer (in transaction-pooling mode) in front so thousands of app clients share a small pool of real database connections. This single change fixes the most common Postgres scaling wall.

-- see who is connected and what they are doing:
SELECT pid, state, wait_event_type, query FROM pg_stat_activity WHERE state <> 'idle';

VACUUM — the Postgres-specific must-know

Postgres marks deleted/updated rows as dead and reclaims them with VACUUM. Autovacuum usually handles it, but on write-heavy tables watch for bloat and tune autovacuum thresholds. Ignoring this is the classic "why is my Postgres slow and huge" story.

SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;

High availability & automatic failover

Postgres replication is rock-solid, but Postgres itself doesn't promote a replica for you — you need an orchestrator. The common choices:

  • Patroni — the de-facto standard for self-managed HA. It runs beside each Postgres node, uses a consensus store (etcd/Consul) to elect a leader, and promotes a replica automatically on failure. Pair it with HAProxy or PgBouncer so apps always reach the current primary.
  • repmgr — a lighter-weight replication manager with manual or automated failover.
  • CloudNativePG / operators on Kubernetes — the operator is the orchestrator: it manages streaming replicas, promotes on failure, and reconfigures endpoints, all declaratively (the Cluster manifest earlier). This is the easiest path on Kubernetes.

Whichever you use, the pattern is the same: multiple streaming replicas, an external component that detects failure and promotes, and a connection router so the app doesn't need to know who's primary. And — say it with me — test the failover before you rely on it.

Extensions that make Postgres special

Postgres's superpower is extensibility. A few extensions turn it into several specialized databases:

  • pg_stat_statements — aggregates query statistics so you can find your worst queries by total time. Turn it on in every production database; it's how you know what to optimize.
  • PostGIS — best-in-class geospatial support (distance, geometry, spatial indexes). If you touch maps/locations, Postgres + PostGIS is the standard.
  • pgvector — vector similarity search, the backbone of AI/semantic-search features. Store embeddings and query nearest neighbors right in your primary database.
  • TimescaleDB — turns Postgres into a high-performance time-series database (automatic partitioning, continuous aggregates) without leaving SQL.
  • pg_partman — automates table partitioning for large, time-ranged tables.

Reaching for an extension often beats adopting a whole new database — you keep transactions, SQL, and your existing tooling.

Zero-downtime schema migrations

Some ALTER TABLEs in Postgres take a strong lock and can freeze your app. Modern Postgres made many operations non-blocking (adding a nullable column with a default is fast now), but others still aren't. The safe playbook:

  • CREATE INDEX CONCURRENTLY builds an index without blocking writes (never plain CREATE INDEX on a busy table).
  • Add columns nullable, backfill in batches (not one giant UPDATE that bloats the table and holds locks), then add constraints NOT VALID and VALIDATE separately.
  • Use expand/contract: deploy schema changes that are backward compatible, migrate the app, then remove the old shape — never a coordinated big-bang flip.
  • Set a short lock_timeout on migrations so a migration that can't get a lock fails fast instead of queueing behind it and stalling every query.

Backups & point-in-time recovery in depth

Postgres gives you two backup styles, and production wants both understood:

  • Logical (pg_dump / pg_dumpall) exports SQL or a custom-format archive. Portable, per-database, easy to restore selectively — ideal up to moderate sizes.
  • Physical + WAL archiving is how you get point-in-time recovery: take a base backup, continuously archive the WAL (write-ahead log), and you can restore to any second. This is what saves you after an accidental DELETE. Don't hand-roll it — use pgBackRest or Barman, which manage base backups, WAL, retention, and even parallel/incremental backups. On Kubernetes, CloudNativePG's barmanObjectStore does continuous backup to S3 for you.

As with any database: automate it, store it off-box, and test restores regularly. Define your RPO/RTO and confirm your setup actually meets them by rehearsing a full restore.

Configuration tuning that matters

Postgres ships conservative defaults. A handful of settings deliver most of the gains on a dedicated server:

  • shared_buffers ≈ 25% of RAM — Postgres's own cache.
  • effective_cache_size ≈ 50–75% of RAM — hints the planner how much OS cache exists.
  • work_mem — memory per sort/hash operation; raise it for analytics, but carefully (it's per operation per connection, so it multiplies).
  • maintenance_work_mem — speeds up VACUUM and index builds.
  • max_connections — keep it modest and use PgBouncer; thousands of backends is an anti-pattern.
  • wal_compression, checkpoint settings — smooth out write spikes.

Change a few, measure with real load, repeat. Tools like PGTune give sane starting points for your RAM/CPU.

Table design, partitioning & JSONB

Good schema design prevents most performance problems before they start:

  • Right-size types and add NOT NULL and constraints — they help the planner and catch bugs.
  • Index for your queries, using the right type (B-tree, GIN for jsonb/full-text, BRIN for huge time-ordered tables), and drop unused indexes (they slow writes).
  • Partition big tables (declarative partitioning by range/list, or pg_partman) so a huge time-series table becomes manageable chunks — old partitions drop instantly, and queries prune to the relevant ones.
  • Use jsonb judiciously — it's excellent for flexible/semi-structured data with GIN indexing, but don't model everything as one giant JSON blob; columns you filter or join on should be real columns.

Postgres rewards thoughtful modeling: the right types, indexes, and partitioning routinely outperform any amount of after-the-fact server tuning.

Security: roles, least privilege & row-level security

Postgres has a rich permission model — use it rather than connecting everything as a superuser:

  • Least-privilege roles. Give each application its own login role with only the privileges it needs (SELECT/INSERT/UPDATE/DELETE on its tables), and use ALTER DEFAULT PRIVILEGES so future tables inherit the grants. Keep superuser access for admins only.
  • Group roles. Create NOLOGIN group roles (e.g. readonly, readwrite) and grant them to individual users, so permissions are managed in one place.
  • Row-Level Security (RLS). For multi-tenant apps, RLS enforces isolation in the database: a policy restricts which rows a role can see, so a query can't accidentally leak another tenant's data even if the application has a bug.
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::int);

Combined with TLS, keeping Postgres off the public internet, and scram-sha-256 password auth in pg_hba.conf, these give you defense in depth — the database enforces security even when the application slips.

Upgrades: minor vs major

Two very different operations hide under "upgrade Postgres." Minor upgrades (15.4 → 15.6) are bug/security fixes with no format change — low risk, just restart on the new binaries; apply them promptly. Major upgrades (15 → 16) change the on-disk format and need a real plan: either pg_dump/restore (simple, slower, fine for small databases) or pg_upgrade (fast, in-place, for large ones), or logical replication for near-zero-downtime by streaming into a new-version cluster and cutting over. Always read the release notes for behavior changes, test on a copy with your real workload, and keep the old cluster until you've verified the new one. Don't run an end-of-life major version — each Postgres release is supported for five years, and staying current keeps you on security fixes. Managed services streamline this, but the rehearse-then-cut-over discipline is the same.

Query tuning

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;
CREATE INDEX ON orders (customer_id);      -- fix a sequential scan on a filtered column

Check pg_stat_statements for your worst queries by total time.

Monitoring: what to watch

Key Postgres health signals (export with the Prometheus postgres_exporter): connections vs max_connections (pool with PgBouncer before this bites), replication lag (pg_stat_replication), cache hit ratio (should be high — low means too little RAM for the working set), dead tuples / autovacuum activity (bloat), long-running queries and locks (pg_stat_activity, pg_locks), transaction ID age (to avoid wraparound emergencies), and disk usage (data + WAL). Alert on connection saturation, replication lag, and any transaction running for hours.

Verify it worked

SELECT version();
SELECT pg_is_in_recovery();                       -- t on a replica, f on the primary
\du                                                -- roles exist with the right privileges

Troubleshooting

  • FATAL: no pg_hba.conf entry → the client's host/user/method isn't allowed. Add a line to pg_hba.conf and SELECT pg_reload_conf();.
  • too many connections → Postgres connections are heavy; put PgBouncer in front rather than raising max_connections indefinitely.
  • Table keeps growing despite deletes → bloat; autovacuum isn't keeping up. Tune autovacuum_vacuum_scale_factor or VACUUM (VERBOSE) manually.
  • Replica lag → a long transaction on the primary or slow disk on the standby; check pg_stat_replication.
  • Slow query after data grew → missing index; EXPLAIN ANALYZE reveals the sequential scan.
  • Disk full from WAL → archiving is failing, so WAL can't be recycled. Fix the archive command.

Where to go next

Our free course installs Postgres all three ways, then covers roles, pg_dump vs PITR, standing up a replica with CloudNativePG, and fixing a slow query with EXPLAIN ANALYZE — the fastest path to confident Postgres administration, with labs.