PostgreSQL operations: configuration, maintenance and replication

PostgreSQL is widely considered the most feature-complete open-source relational database. It is the first choice for applications with high demands on data integrity, complex queries and extensibility. This article covers what sets it apart and how to operate it.

What makes PostgreSQL different

Real transactional safety: full MVCC (multiple versions concurrently, readers never block writers). The serializable isolation level is actually usable, not nominally present.
Extensibility: custom data types, functions and index methods can be added — PostGIS for geodata, pg_trgm for text similarity, TimescaleDB for time series.
JSONB: document data with index support. Enough to avoid a separate document database in many cases.
Full-text search: built in, with stemming and language support.
Window functions and CTEs: very powerful query constructs, consistently implemented.
Strict typing: a type mismatch is an error, not a silently wrong result.

Connection and basic setup

# Connect
psql -h db.example.org -U app -d appdb
psql "postgresql://app:This email address is being protected from spambots. You need JavaScript enabled to view it.:5432/appdb"

# Password without exposing it in the process list
export PGPASSWORD='secret'
psql -h db.example.org -U app -d appdb
# or better: ~/.pgpass with chmod 600
#   db.example.org:5432:appdb:app:*secret*

# Key settings in postgresql.conf
#   listen_addresses = 'localhost'        local only (default is safe!)
#   max_connections = 100                 limit connections
#   shared_buffers = 25% of RAM           buffer cache
#   effective_cache_size = 50-75% of RAM  hint for the planner
#   work_mem = 16MB                       PER sort/hash operation!
#   maintenance_work_mem = 256MB          for VACUUM and index builds
#   wal_level = replica                   for replication
#   max_wal_size = 2GB                    avoid frequent checkpoints

# In pg_hba.conf: who may connect and how
# local   all   postgres            peer
# host    all   app     10.0.0.0/24 md5
# hostnossl all all    0.0.0.0/0    reject

Day-to-day operations

# State and activity
SELECT version();
SELECT * FROM pg_stat_activity WHERE state != 'idle';
SELECT pid, usename, application_name, state, wait_event_type, wait_event,
       now() - query_start AS runtime, left(query, 80)
FROM pg_stat_activity
WHERE state = 'active' ORDER BY runtime DESC LIMIT 10;

# Find blocking (wait chains and deadlocks)
SELECT blocked.pid AS blocked,
       blocking.pid AS blocking,
       left(blocked.query, 60)  AS blocked_query,
       left(blocking.query, 60) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

# Cancel a query
SELECT pg_cancel_backend(12345);      # gentle: query only
SELECT pg_terminate_backend(12345);   # hard: whole connection

# Maintenance
VACUUM (ANALYZE, VERBOSE) tablename;
VACUUM (FULL, ANALYZE) tablename;     # LOCKS the table! Maintenance window only
ANALYZE tablename;                    # statistics only
REINDEX INDEX CONCURRENTLY idx_name;  # without a lock (since 12)

# Sizes and bloat
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;
SELECT pg_size_pretty(pg_database_size('appdb'));

# Dead tuples per table (bloat signal)
SELECT relname, last_vacuum, last_autovacuum, n_dead_tup, n_live_tup
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

Autovacuum is not optional. PostgreSQL writes a new row version on every update; the old one stays until collected. Without autovacuum the database grows without bound and queries slow down. For heavily updated tables, tune per table:

ALTER TABLE requests SET (
  autovacuum_vacuum_scale_factor = 0.01,     # instead of 0.2
  autovacuum_analyze_scale_factor = 0.005
);

JSONB: documents in the relational world

CREATE TABLE events (
  id        bigserial PRIMARY KEY,
  ts        timestamptz NOT NULL DEFAULT now(),
  kind      text NOT NULL,
  data      jsonb NOT NULL
);

-- Index on the whole document
CREATE INDEX idx_events_data ON events USING gin (data);

-- GIN index on specific paths only (smaller)
CREATE INDEX idx_events_host ON events ((data ->> 'host'));

INSERT INTO events (kind, data)
VALUES ('login', '{"user":"max","host":"web01","ok":true}'::jsonb);

SELECT * FROM events WHERE data ->> 'host' = 'web01';
SELECT * FROM events WHERE data @> '{"ok": true}'::jsonb;
SELECT data ->> 'user' AS user_name, count(*)
FROM events GROUP BY 1 ORDER BY 2 DESC;

-- Typical mistake: wrong index for the access pattern
--   GIN (data)                 helps with @>, ?, ?|
--   B-tree ((data->>'host'))   helps with equality on one field
--   For <, > on a numeric field: index (data->>'x')::int

Backup and restore

# Logical dump (portable, good for single databases)
pg_dump -Fc -h db -U app appdb > appdb_$(date +%F).dump      # custom format
pg_dump -Fp -h db -U app appdb > appdb.sql                   # plain SQL
pg_dumpall -h db -U postgres > all_$(date +%F).sql           # incl. roles

# Restore
pg_restore -h db -U app -d appdb_new -j 4 appdb.dump
pg_restore -l appdb.dump | head -30            # inspect the table of contents
pg_restore -h db -U app -d appdb -t server appdb.dump   # single table

# ALWAYS verify a restore - into a test database
createdb -h db -U app appdb_probe
pg_restore -h db -U app -d appdb_probe -j 4 appdb.dump
psql -h db -U app -d appdb_probe -c "SELECT count(*) FROM customers;"
dropdb -h db -U app appdb_probe

# Physical backup (large volumes, point-in-time recovery)
pg_basebackup -h db -D /srv/backup/base -Ft -z -P

Replication

# On the primary: replication role and WAL shipping
CREATE ROLE repl LOGIN REPLICATION PASSWORD 'secret';
# postgresql.conf:
#   wal_level = replica
#   max_wal_senders = 5
#   wal_keep_size = 1GB

# On the standby: take a base backup and start
pg_basebackup -h primary -D /var/lib/postgresql/16/main -U repl -Fp -Xs -P -R
systemctl start postgresql

# Verify on BOTH sides
psql -c "SELECT client_addr, state, sync_state, replay_lag FROM pg_stat_replication;"
psql -c "SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn();"

# How far behind is the standby?
psql -c "SELECT now() - pg_last_xact_replay_timestamp() AS lag;"

Security

Never expose the database directly to the internet. Internal networks only; external access via a tunnel or a connection broker.
Do not operate as the database superuser. The application gets its own role with rights on its own tables.
Grant narrowly. Instead of GRANT ALL, grant only what is needed and distinguish read from write.
Use Row Level Security for multi-tenant tables — enforcement then lives in the database, not only in application code.
Encrypt connections. Use hostssl in pg_hba.conf with certificate verification.
Log what stands out. log_min_duration_statement = 500 writes slow queries to the log — the basis of any tuning effort.

Operations checklist

Is autovacuum running and keeping up? Watch n_dead_tup.
Any queries running longer than a minute? pg_stat_activity.
Are indexes still useful? pg_stat_user_indexes with idx_scan = 0 shows unused candidates for removal.
Was a backup taken in the last 24 hours — and has one ever been restored?
Is replication lagging, and how far may it fall behind?
Is there enough disk space? VACUUM needs room for the new version.
Are connections leaking? A connection leak is the usual cause of too many connections.