Replication diagnostics

The Postgres [pg_stat_replication][postgres-pg-stat-replication-docs] view provides information about each replica. This view is particularly useful for calculating replication lag, which measures how far behind the primary the current state of the replica is. The replay_lag field gives a measure of the seconds between the most recent WAL transaction on the primary, and the last reported database commit on the replica. Coupled with write_lag and flush_lag, this provides insight into how far behind the replica is. The *_lsn fields also provide helpful information. They allow you to compare WAL locations between the primary and the replicas. The state field is useful for determining exactly what each replica is currently doing; the available modes are startup, catchup, streaming, backup, and stopping.

To see the data, on the primary database, run this command:

The output looks like this:

Postgres provides some failover functionality, where the replica is promoted to primary in the event of a failure. This is provided using the [pg_ctl][pgctl-docs] command or the trigger_file. However, Postgres does not provide support for automatic failover. For more information, see the [Postgres failover documentation][failover-docs]. If you require a configurable high availability solution with automatic failover functionality, check out [Patroni][patroni-github].

===== PAGE: https://docs.tigerdata.com/self-hosted/replication-and-ha/about-ha/ =====

Examples:

Example 1 (sql):

SET password_encryption = 'scram-sha-256';

Example 2 (sql):

CREATE ROLE repuser WITH REPLICATION PASSWORD '<PASSWORD>' LOGIN;

Example 3 (yaml):

listen_addresses = '*'
wal_level = replica
max_wal_senders = 2
max_replication_slots = 2
synchronous_commit = off

Example 4 (sql):

SELECT * FROM pg_create_physical_replication_slot('replica_1_slot', true);