Skip to content

How-to Guides

Scope

Task recipes for PostgreSQL 18: installing and upgrading, deploying (including Kubernetes with CloudNativePG), tuning, backup and recovery, monitoring, security setup, vector search with pgvector, and troubleshooting. Parameter defaults and version facts are in Reference. The reasons behind the recipes are in Explanation.

Install and Upgrade

Install PostgreSQL 18 from the PGDG Repository (Debian/Ubuntu)

The PGDG apt repository ships every supported major version, which distro repositories often lag behind.

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh   # adds apt.postgresql.org
sudo apt install -y postgresql-18
pg_lsclusters                                                  # shows 18/main on port 5432
sudo -u postgres psql -c "SELECT version();"

Run PostgreSQL 18 in Docker

docker run -d --name pg18 \
  -e POSTGRES_PASSWORD='change-me' \
  -v pgdata:/var/lib/postgresql \
  -p 5432:5432 postgres:18

Docker image layout changed in 18

In the official postgres:18 image, PGDATA is /var/lib/postgresql/18/docker and the VOLUME is /var/lib/postgresql. Earlier images used /var/lib/postgresql/data. Mount the parent directory, as shown above. Otherwise data ends up in an anonymous volume.

Apply a Minor Update

Minor releases (for example 18.4 → 18.6) only need new binaries and a restart. Read the release notes' "Migration" section first: 18.6, for example, requires output_plugin_libraries changes if you use third-party logical decoding plugins.

sudo apt update && sudo apt install --only-upgrade postgresql-18
sudo systemctl restart postgresql@18-main
psql -c "SHOW server_version;"

Major Upgrade with pg_upgrade

pg_upgrade rewrites only the system catalogs and reuses the data files. Since PG 18 it keeps planner statistics, so the new cluster does not start with a cold planner.

# 1. Install the new major version and initialize an empty cluster.
#    Checksums must match the old cluster. PG 18 initdb enables them by default,
#    so pass --no-data-checksums if the old cluster has none.
/usr/lib/postgresql/18/bin/initdb -D /var/lib/postgresql/18/main

# 2. Dry run: checks compatibility, extensions, and (18.6+) output_plugin_libraries
/usr/lib/postgresql/18/bin/pg_upgrade \
  -b /usr/lib/postgresql/17/bin -B /usr/lib/postgresql/18/bin \
  -d /var/lib/postgresql/17/main -D /var/lib/postgresql/18/main \
  --check --jobs 4

# 3. Real run: --link (hard links) or --swap (PG 18+, moves directories) avoids copying data
/usr/lib/postgresql/18/bin/pg_upgrade ... --link --jobs 4

# 4. Rebuild only the statistics that were not carried over (extended statistics, and so on)
vacuumdb --all --analyze-in-stages --missing-stats-only

On Debian/Ubuntu the pg_upgradecluster 17 main wrapper does the same steps. For near-zero downtime, replicate to a new-version cluster with logical replication (or pg_createsubscriber) and then switch over.

Upgrade checklist

Confirm that every extension (PostGIS, pgvector, TimescaleDB, Citus) has a build for the target major. Reindex full-text and pg_trgm indexes if the cluster uses ICU or the builtin collation provider (see the PG 18 compatibility notes). Test the application against the new version's compatibility changes.

Deployment Patterns

Replication Topologies

Pattern Consistency Failover Use case
Streaming (async) Eventual (replica lag) Manual, or automatic with an HA manager Standard HA and read scaling
Streaming (sync, synchronous_standby_names) No committed-data loss on failover Automatic with an HA manager Zero data loss (RPO = 0)
Logical replication Eventual Manual Cross-version upgrades, selective tables, CDC
Patroni + etcd/Consul/Kubernetes DCS Depends on synchronous_mode Automatic leader election VMs and bare metal, or Kubernetes
CloudNativePG (Kubernetes operator) Async by default, sync or quorum optional Automatic, done by the operator Kubernetes-native deployments
PgBouncer + HAProxy / VIP N/A (proxy layer) Follows the HA manager Connection pooling and routing

Connection Pooling

# pgbouncer.ini - common starting point (PgBouncer 1.26)
[pgbouncer]
pool_mode = transaction         ; best for most web workloads
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
server_lifetime = 3600
server_idle_timeout = 600
max_prepared_statements = 200   ; protocol-level prepared statements in transaction mode (1.21+)

Connection limits

max_connections defaults to 100. Each backend is a separate process whose memory grows with work_mem use, catalog caches and temporary buffers. A few MB when idle is common, and far more under load (the exact figure varies). Use PgBouncer to multiplex thousands of application connections onto a few dozen backends. PgBouncer 1.26.0 (2026-09-23) fixes three CVEs and now tracks search_path by default on PG 18+, so upgrade poolers too.

Deploy on Kubernetes with CloudNativePG

CloudNativePG (CNCF Sandbox, Apache-2.0) runs PostgreSQL as a Cluster custom resource. It manages the primary and standbys directly, without Patroni or StatefulSets. Version 1.30.1 (2026-09-23) supports PostgreSQL 14–18 on Kubernetes 1.34–1.36.

# Install the operator (server-side apply is required because of CRD size)
kubectl apply --server-side -f \
  https://raw.githubusercontent.com/cloudnative-pg/cloudnative-pg/release-1.30/releases/cnpg-1.30.1.yaml
kubectl rollout status deployment -n cnpg-system cnpg-controller-manager

# cluster.yaml - 3 instances with quorum-based synchronous replication
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: pg-main
spec:
  instances: 3
  imageName: ghcr.io/cloudnative-pg/postgresql:18.6-system-trixie
  postgresql:
    parameters:
      shared_buffers: "1GB"
      max_slot_wal_keep_size: "10GB"
    synchronous:
      method: any
      number: 1
  storage:
    size: 50Gi

kubectl apply -f cluster.yaml
kubectl get pods -l cnpg.io/cluster=pg-main
kubectl get secret pg-main-app -o jsonpath='{.data.uri}' | base64 -d   # app connection URI

The operator creates pg-main-rw, pg-main-ro and pg-main-r Services for the primary, the replicas and any instance. Point applications at -rw. The operator does failover by promoting the most advanced standby. Backups go through the Barman Cloud plugin or volume snapshots.

Performance Tuning

Memory Configuration

Parameter Formula Example (32Gi RAM)
shared_buffers 25% of RAM 8GB
effective_cache_size 50–75% of RAM 24GB
work_mem RAM / max_connections / 4 (it applies per sort or hash node) 32MB
maintenance_work_mem RAM / 16, capped at about 2GB 2GB
wal_buffers Leave at -1 (auto: 1/32 of shared_buffers, capped at one 16MB WAL segment) 16MB (auto)

WAL & Checkpoint Tuning

-- Production WAL settings
ALTER SYSTEM SET wal_level = 'replica';          -- default; 'logical' for CDC
ALTER SYSTEM SET max_wal_size = '4GB';
ALTER SYSTEM SET min_wal_size = '1GB';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;  -- default since PG 14
ALTER SYSTEM SET checkpoint_timeout = '15min';
ALTER SYSTEM SET wal_compression = 'zstd';       -- lz4/zstd since PG 15
SELECT pg_reload_conf();                         -- wal_level needs a restart

Enable io_uring Asynchronous I/O (PG 18)

The default io_method = worker works everywhere. On Linux builds compiled with liburing, io_uring removes the hop through the worker processes.

pg_config --configure | tr ' ' '\n' | grep -i liburing   # must print --with-liburing

ALTER SYSTEM SET io_method = 'io_uring';   -- or keep 'worker' and raise io_workers
-- restart required, then verify:
SHOW io_method;
SELECT * FROM pg_aios;                     -- in-flight async I/O handles
SELECT backend_type, object, context, reads, read_bytes, read_time
FROM pg_stat_io WHERE reads > 0 ORDER BY read_bytes DESC LIMIT 10;

Containers and seccomp

Many container runtimes block io_uring syscalls in their default seccomp profile. If the server fails to start with io_method = io_uring, keep worker, or adjust the profile deliberately.

Query Performance

-- Enable query statistics (add pg_stat_statements to shared_preload_libraries first)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find slow queries
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Identify tables that may be missing indexes
SELECT schemaname, relname, seq_scan, seq_tup_read,
       idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan AND seq_tup_read > 10000
ORDER BY seq_tup_read DESC;

-- Inspect a plan, including I/O timing and buffer usage
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;

Backup & Recovery

pg_basebackup (Physical)

# Full base backup, tar format, gzip-compressed, WAL streamed alongside
pg_basebackup -h primary -U replicator -D /backup/base \
  -Ft -z --checkpoint=fast --wal-method=stream -P

For point-in-time recovery (PG 12+), restore the base backup, then set these in postgresql.conf and create an empty recovery.signal file in the data directory:

restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-09-20 10:00:00+00'
recovery_target_action = 'promote'

# Full backup
pgbackrest --stanza=main --type=full backup

# Incremental backup
pgbackrest --stanza=main --type=incr backup

# Restore to a point in time
pgbackrest --stanza=main --type=time \
  --target="2026-09-20 10:00:00+00" --target-action=promote restore

# Check archiving and repository health
pgbackrest --stanza=main check
pgbackrest info

Monitoring

Key Metrics

-- Active connections vs limit
SELECT count(*), (SELECT setting FROM pg_settings WHERE name='max_connections')
FROM pg_stat_activity;

-- Replication lag (on replica)
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

-- Replication lag per standby (on primary)
SELECT application_name, state, sync_state, write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

-- Slots retaining WAL (inactive slots are a disk-full risk)
SELECT slot_name, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

-- Cache hit ratio (aim for > 99% on OLTP)
SELECT sum(blks_hit) / nullif(sum(blks_hit) + sum(blks_read), 0) AS cache_hit_ratio
FROM pg_stat_database;

-- Transaction rate
SELECT xact_commit + xact_rollback AS total_txn FROM pg_stat_database WHERE datname = current_database();

-- XID wraparound headroom
SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database ORDER BY 2 DESC;

Security Setup

Enable SCRAM-SHA-256

scram-sha-256 has been the default password_encryption since PG 14. Clusters upgraded from older versions may still hold MD5 hashes.

# postgresql.conf
password_encryption = scram-sha-256

-- Find roles still using MD5 hashes
SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%';
-- Resetting a password re-hashes it with SCRAM
ALTER ROLE app_user PASSWORD 'new_password';

Configure pg_hba.conf

# pg_hba.conf
# TYPE   DATABASE     USER        ADDRESS          METHOD

# Local admin via peer auth (OS user = postgres)
local    all          postgres                     peer

# Application connections via SCRAM-SHA-256 over TLS
hostssl  appdb        app_user    10.0.0.0/8       scram-sha-256

# Replication connections with certificate auth
hostssl  replication  replicator  192.168.1.0/24   cert

# Read-only connections from reporting subnet
hostssl  appdb        reporting   172.16.0.0/16    scram-sha-256

# Deny all other connections
host     all          all         0.0.0.0/0        reject

SELECT pg_reload_conf();                               -- no restart needed
SELECT line_number, type, database, user_name, auth_method, error
FROM pg_hba_file_rules;                                -- verify parsing

Create Roles and Grant Privileges

-- Application role with limited attributes
CREATE ROLE app_user LOGIN
    PASSWORD 'secure_password'
    VALID UNTIL '2027-01-01'
    CONNECTION LIMIT 20;

-- Administrative role
CREATE ROLE db_admin LOGIN CREATEDB CREATEROLE
    PASSWORD 'admin_password';
GRANT db_admin TO senior_engineer;

-- Granular privileges
GRANT CONNECT ON DATABASE appdb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE ON TABLE orders TO app_user;
GRANT USAGE, SELECT ON SEQUENCE orders_id_seq TO app_user;

-- Default privileges for tables created later in the schema
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE ON TABLES TO app_user;

Enable Row-Level Security

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;   -- make the table owner comply too

CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.current_tenant')::INTEGER)
    WITH CHECK (tenant_id = current_setting('app.current_tenant')::INTEGER);

GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_user;

-- Per request (set by middleware, never from raw client input)
SET app.current_tenant = '42';

Configure TLS

# postgresql.conf
ssl = on
ssl_cert_file = '/etc/postgresql/server.crt'
ssl_key_file  = '/etc/postgresql/server.key'
ssl_ca_file   = '/etc/postgresql/ca.crt'        # for client certificate verification
ssl_min_protocol_version = 'TLSv1.2'            # default since PG 14; use TLSv1.3 if all clients support it

Require client certificates with the cert method (the certificate CN must match the role, or be mapped through pg_ident.conf):

hostssl  all  all  0.0.0.0/0  cert

Client connection string that verifies the server and presents a client certificate:

postgresql://user@host:5432/db?sslmode=verify-full&sslcert=/path/client.crt&sslkey=/path/client.key&sslrootcert=/path/ca.crt

Enable pgAudit

# postgresql.conf (restart required)
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'write, ddl, role'
pgaudit.log_catalog = off
pgaudit.log_parameter = on
pgaudit.log_relation = on
pgaudit.log_statement_once = off

CREATE EXTENSION pgaudit;

pgaudit.log = 'all' is very verbose on busy systems. Start with write, ddl, role and add read only for sensitive schemas, through object auditing (pgaudit.role).

Encrypt Columns with pgcrypto

CREATE EXTENSION pgcrypto;

-- Hash a password (bcrypt); PG 18 also supports gen_salt('sha512crypt')
SELECT crypt('user_password', gen_salt('bf'));

-- Encrypt a value with AES-256 (PGP symmetric)
INSERT INTO secrets (id, data)
VALUES (1, pgp_sym_encrypt('sensitive data', 'encryption_key', 'cipher-algo=aes256'));

-- Decrypt
SELECT pgp_sym_decrypt(data, 'encryption_key') FROM secrets WHERE id = 1;

Check old pgcrypto data after upgrading to 18.6

Before 18.6 and the other August 2026 minors (CVE-2026-14663), PGP encryption with a cipher that OpenSSL refused (FIPS mode, or legacy bf/cast5/3des) produced data that was not really encrypted. On the fixed versions, decrypting such data fails. Recover it with pgp_sym_decrypt(col, key, 'ignore-cipher-failure=1') and re-encrypt it with cipher-algo=aes256.

Vector Search with pgvector

pgvector 0.8.6 (2026-07-29) supports PostgreSQL 13+ and is packaged in the PGDG repositories and most managed services.

sudo apt install -y postgresql-18-pgvector   # PGDG package; or build from source:
git clone --branch v0.8.6 https://github.com/pgvector/pgvector.git
cd pgvector && make && sudo make install

CREATE EXTENSION vector;

CREATE TABLE items (
  id bigserial PRIMARY KEY,
  embedding vector(1536)
);

-- HNSW index for cosine distance (build after bulk load for speed)
SET maintenance_work_mem = '4GB';
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);

-- Top-5 nearest neighbours; <=> is cosine distance, <-> is L2
SELECT id FROM items ORDER BY embedding <=> '[0.01, 0.02, ...]' LIMIT 5;

-- With a selective WHERE filter, let HNSW keep scanning (0.8.0+)
SET hnsw.iterative_scan = relaxed_order;

Common Issues

Issue Diagnosis Fix
Connections exhausted SELECT count(*) FROM pg_stat_activity Use PgBouncer. Raise max_connections only with memory headroom
Bloated tables SELECT pg_size_pretty(pg_table_size('orders')), pgstattuple Tune autovacuum. pg_repack, or REPACK CONCURRENTLY on PG 19. VACUUM FULL takes an exclusive lock
Slow queries pg_stat_statements, EXPLAIN (ANALYZE, BUFFERS) Add indexes, rewrite the query, update statistics
WAL disk full SELECT pg_current_wal_lsn(), pg_replication_slots Fix archiving or drop the stale slot. Set max_slot_wal_keep_size
Replication lag pg_stat_replication (write_lag, replay_lag) Check the network and standby I/O, and long queries on the standby (hot_standby_feedback)
Logical decoding fails after 18.6 update Error mentions output_plugin_libraries Add the plugin (for example wal2json) to output_plugin_libraries
XID wraparound warnings age(datfrozenxid) approaching 2 billion Remove what blocks vacuum (old transactions, slots), then run VACUUM (FREEZE)

Commands & Recipes

Connection & Basics

# Connect
psql -h localhost -U postgres -d mydb

# Create database
createdb mydb

# Import SQL
psql -d mydb -f schema.sql

# Dump
pg_dump mydb > backup.sql
pg_dump -Fc mydb > backup.custom  # compressed custom format, restore with pg_restore
pg_restore -d mydb_restored -j 4 backup.custom

Essential Queries

-- Database size
SELECT pg_size_pretty(pg_database_size('mydb'));

-- Table sizes
SELECT relname, pg_size_pretty(pg_total_relation_size(oid))
FROM pg_class WHERE relkind = 'r'
ORDER BY pg_total_relation_size(oid) DESC LIMIT 10;

-- Active connections
SELECT pid, usename, application_name, state, query
FROM pg_stat_activity WHERE state = 'active';

-- Kill a long-running query (use the pid from pg_stat_activity)
SELECT pg_cancel_backend(12345);     -- cancel the query
SELECT pg_terminate_backend(12345);  -- end the whole session

-- Index usage
SELECT relname, idx_scan, seq_scan,
  ROUND(100.0 * idx_scan / NULLIF(idx_scan + seq_scan, 0), 1) AS idx_pct
FROM pg_stat_user_tables ORDER BY seq_scan DESC;

PostgreSQL 18 Features

-- UUIDv7 primary keys (time-ordered, so inserts land at the right edge of the B-tree)
CREATE TABLE events (
  id uuid DEFAULT uuidv7() PRIMARY KEY,
  data jsonb NOT NULL,
  created_at timestamptz DEFAULT now()
);

-- Virtual generated column (computed on read; VIRTUAL is the default in 18)
ALTER TABLE products ADD COLUMN price_with_tax numeric
  GENERATED ALWAYS AS (price * 1.1) VIRTUAL;

-- OLD/NEW in RETURNING
UPDATE products SET price = price * 1.05 WHERE id = 7
RETURNING old.price AS before, new.price AS after;

-- Temporal primary key (needs btree_gist for the scalar column)
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE room_booking (
  room_id int,
  during  tstzrange,
  PRIMARY KEY (room_id, during WITHOUT OVERLAPS)
);

# postgresql.conf - asynchronous I/O (v18)
io_method = 'io_uring'    # or 'worker' (default) or 'sync'

Replication

# postgresql.conf on the primary (defaults shown; raise if you need more)
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
max_slot_wal_keep_size = '20GB'

# On the replica: clone the primary and write standby config (-R creates standby.signal)
pg_basebackup -h primary -U replicator -D /var/lib/postgresql/18/main -R -P \
  --slot=replica1 --create-slot

postgresql.conf Baseline

# Starting point for a dedicated 16 GB host on SSD/NVMe
shared_buffers = '4GB'           # 25% of RAM
effective_cache_size = '12GB'    # 75% of RAM
work_mem = '64MB'
maintenance_work_mem = '1GB'
max_wal_size = '4GB'
random_page_cost = 1.1           # SSD
effective_io_concurrency = 200   # SSD/NVMe (PG 18 default is 16)

Sources