Question: How do you verify replication in PostgreSQL?

Answer

Replication in PostgreSQL ensures that data is copied and maintained across multiple servers, providing redundancy and increasing data availability. Verifying that replication is functioning correctly is crucial for database reliability and performance. Here are steps to check the status of replication in PostgreSQL:

Step 1: Use the pg_stat_replication View

The pg_stat_replication view provides information on the state of replication from the perspective of the primary server. This view is accessible only by superusers.

SELECT * FROM pg_stat_replication;

This command returns various columns such as:

• pid: Process ID of the WAL sender process.
• state: Current state of the replication connection (e.g., streaming, backup, catchup).
• sync_state: Synchronization state of the replica (e.g., sync, potential, async).
• replication_lag: The time difference between the last WAL position sent and received.

The presence of a row with the state streaming typically indicates an active replication connection.

Step 2: Compare the Write-Ahead Log (WAL) Locations

You can compare the last written WAL location on the primary with the last received or replayed WAL location on the standby.

On the Primary Server:

SELECT pg_current_wal_lsn();

On the Standby Server:

To check the last WAL location received but not necessarily applied:

SELECT pg_last_wal_receive_lsn();

To check the last WAL location applied:

SELECT pg_last_wal_replay_lsn();

In synchronous replication setups, the gap between these LSNs should be minimal or none, indicating a healthy replication.

Step 3: Monitoring Replication Delay

Replication delay can be monitored by comparing the current WAL position between the primary and standby servers. A significant delay might indicate issues with network throughput or load on the standby server.

Query to Check Replication Delay:

SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) AS replication_delay_seconds;

A low value indicates good replication health, whereas a high value suggests issues needing investigation.

Conclusion

Regularly verifying replication in PostgreSQL using these methods helps ensure data integrity and availability. While this guide covers primary aspects, always refer to the PostgreSQL documentation for specific configurations and advanced scenarios.