Emplacements de réplication et gestion de WAL

At the core of PostgreSQL's reliability and replication lies the Write-Ahead Log (WAL). Think of WAL as a journal that records every change made to your database.

• Every transaction writes to WAL before data is committed to disk.
• This ensures data integrity, even if the server crashes.
• Standby servers use WAL to reconstruct changes from the primary, staying in sync.

By default, PostgreSQL reclaims old WAL files to save disk space. This is fine for a standalone server, but it creates a challenge for replication.

If a standby server falls too far behind, the necessary WAL files might have already been removed from the primary. This leads to:

• Replication failure: the standby can't catch up.
• Manual intervention: requiring a full re-sync of the standby.

Replication Slots are PostgreSQL's solution to the WAL retention problem. A replication slot is a persistent object on the primary server that prevents WAL segments required by a standby or logical decoder from being automatically removed.

• They guarantee that WAL files are kept until consumed.
• They track the progress of each connected consumer.
• Slots ensure that a standby can always catch up, even after a long disconnection.

When a replication slot is active, the primary server will not delete any WAL files that the slot's consumer (e.g., a standby server or a logical decoding process) has not yet confirmed as processed.

This creates a 'bookmark' in the WAL stream. The primary only purges WAL segments once all active slots have advanced past that point.

Physical replication slots are used with physical streaming replication. They track the LSN (Log Sequence Number) of the standby, ensuring all necessary WAL files are retained for that standby.

To create a physical slot, you use the pg_create_physical_replication_slot() function. Replace my_physical_slot with a descriptive name.

Logical replication slots are used for logical replication (PostgreSQL's pub/sub model) or other logical decoding applications. They decode WAL into a stream of logical changes (e.g., INSERT, UPDATE, DELETE).

You specify a plugin (like pgoutput for built-in logical replication) to define how WAL records are transformed. Replace my_logical_slot with your desired name.

It's crucial to monitor your replication slots to ensure they are active and not holding onto excessive WAL files. The pg_replication_slots view provides detailed information:

• slot_name: The name of the slot.
• active: True if a consumer is currently connected.
• restart_lsn: The oldest WAL LSN still required by the slot.
• confirmed_flush_lsn: For logical slots, the LSN confirmed by the consumer.

Try running this query to see existing slots:

When a standby server or logical consumer is no longer needed, it's vital to drop its replication slot. An unused slot will continue to accumulate WAL files indefinitely, potentially filling up your primary server's disk space!

Use the pg_drop_replication_slot() function, providing the slot's name. Be careful: dropping an active slot will disconnect its consumer.

Replication slots are powerful, but they come with a responsibility: managing disk space. Inactive slots are a common cause of disk space exhaustion on primary servers.

• Regularly check pg_replication_slots for inactive slots.
• If a standby is permanently offline, drop its slot immediately.
• Monitor the wal_lag (as shown in the monitoring query) to catch slow consumers.

Proactive management prevents outages!

In this lesson, we learned about PostgreSQL's Replication Slots, a critical feature for robust replication and logical decoding.

• Slots guarantee WAL retention for connected consumers.
• There are two types: physical for streaming replication and logical for logical decoding.
• You can create, monitor (using pg_replication_slots), and critically, drop slots.
• Always manage your slots carefully to prevent disk space issues caused by inactive slots!