Surveillance de l’état de la réplication

PostgreSQL replication is key for high availability and read scalability. But how do you know if it's working correctly?

Monitoring replication health is crucial to ensure your standby servers are up-to-date and ready to take over if the primary fails. Without it, you might discover issues only when it's too late!

When monitoring replication, you'll want to keep an eye on a few key metrics:

• Replication Status: Is the standby connected and actively receiving data?
• Replication Lag: How far behind is the standby compared to the primary?
• WAL Activity: Are Write-Ahead Log (WAL) files being transferred and applied correctly?

PostgreSQL provides a powerful system view called pg_stat_replication. This view offers detailed information about each active standby server connected to your primary.

It's your go-to source for real-time replication status and progress.

Let's see what information pg_stat_replication provides. Run this query on your primary server:

The state and sync_state columns are vital:

• state: Shows the standby's current activity (e.g., streaming, catching up, backup). streaming means it's actively receiving WAL records.
• sync_state: Indicates if the standby is async (asynchronous) or sync (synchronous). Synchronous standbys guarantee data durability across multiple servers.

Replication lag is the delay between when a transaction commits on the primary and when it's applied on the standby.

High lag means your standby isn't up-to-date. In a failover scenario, this could lead to data loss. We want lag to be as low as possible, ideally zero.

You can calculate replication lag in bytes using pg_wal_lsn_diff(). This function measures the difference between two Log Sequence Numbers (LSNs).

A common way to see lag is by comparing the primary's current LSN to the standby's replay_lsn from pg_stat_replication.

What about checking the standby server itself? You can use the pg_is_in_recovery() function to confirm if a server is currently acting as a standby and applying WAL records.

It returns true if the server is in recovery mode (i.e., a standby) and false if it's a primary.

Beyond simple lag, you can track the progress of WAL application more granularly. The pg_wal_lsn_diff() function is also useful for comparing any two LSNs.

For example, you could compare write_lsn, flush_lsn, and replay_lsn from pg_stat_replication to pinpoint where delays are occurring (writing to disk, flushing, or replaying).

You run SELECT client_addr, state, sync_state FROM pg_stat_replication; on your primary and see the following output for one standby:

client_addr | state    | sync_state
------------+----------+-----------
192.168.1.5 | streaming | async

Which of the following statements are true about this standby?

You've learned how to monitor PostgreSQL replication health! We explored:

• Why monitoring lag and status is critical.
• Using the pg_stat_replication view on the primary.
• Interpreting state and sync_state.
• Calculating replication lag with pg_wal_lsn_diff().
• Checking standby recovery status with pg_is_in_recovery().

Regularly checking these metrics helps ensure your high availability setup is robust and reliable!