Monitoring, Replication and High Availability

Metrics to watch

SELECT round(100.0 * sum(blks_hit) / nullif(sum(blks_hit) + sum(blks_read), 0), 2) AS cache_hit_pct
FROM pg_stat_database;

Use postgres_exporter with Prometheus and Grafana, or your provider's dashboard, and alert on the thresholds above.

Memory settings worth knowing

Tools like PGTune suggest values for your hardware. Change one setting at a time and measure.

Streaming replication

A replica continuously applies the primary's WAL. Uses:

• Read scaling: send reports and analytics to the replica.
• High availability: promote it if the primary fails.
• Safe heavy queries: long exports do not slow the primary.

Basic setup on the replica:

sudo systemctl stop postgresql
sudo -u postgres rm -rf /var/lib/postgresql/16/main
sudo -u postgres pg_basebackup -h 10.0.0.5 -U replicator \
  -D /var/lib/postgresql/16/main -R -X stream -P
sudo systemctl start postgresql

-R writes the connection settings that make it a standby. On the primary, create the replicator role with REPLICATION and allow it in pg_hba.conf.

Replication is not a backup: a DROP TABLE replicates in milliseconds. You still need the backups from module 3.

Failover

Promoting a replica manually: SELECT pg_promote();. Automatic failover needs a tool such as Patroni that uses a consensus store to make sure only one primary exists at a time (two primaries, "split brain", corrupts data). Managed services offer this as a "high availability" option.

Final project

On two practice servers: install PostgreSQL with least-privilege roles and strict pg_hba.conf, set up pgBackRest with WAL archiving to object storage, perform a point-in-time restore, add a streaming replica and promote it, enable pg_stat_statements and fix one slow query with an index. Document RPO and RTO you measured. That is the core job of a database administrator.