What are WAL Files?
Write-Ahead Log (WAL) is a record of the changes made to the data. It ensures that when there is a crash in the system or loss of connection, the database can be recovered. When you make changes to the database, WAL files keep building up. WAL logs are stored in the pg_wal directory as a set of segment files.
The purpose of keeping the WAL files is you can recreate the database from scratch by replaying all the changes recorded in the WAL files.
Now let us see why PostgreSQL replication slots are needed.
Why PostgreSQL Replication Slots are Necessary?
If you have a master and some hot or archiving standbys, and you are running streaming replication between them, a replication slot is used to retain the WAL files even when the replica is offline or disconnected.
This feature was introduced in PostgreSQL 9.4. Prior to that, if a standby went offline, until it came back online, a set of WAL files had to be kept by the master. This is done by setting the wal_keep_segment correctly or high enough to keep the replica from falling behind too much.
If this is not done, then the standby can’t keep up with the master after the master deletes the WAL files that the standby is yet to replay and you will get an error.
ERROR: requested WAL segment 000000490000067B00000073 has already been removedThe problem with setting the correct wal_keep_segment is, it is difficult to know exactly how many WAL files need to be kept. This is guesswork and if it is set too high, the master will exhaust the space in the pg_wal directory (WAL files are stored in pg_wal directory. Prior to version 10, they were stored in the pg_xlog directory).
Alternatives to the above method are either WAL archiving which means you have to write a script to move the WAL files to another long term location or create the standby that has fallen behind again from scratch.
After replication slots came in with PostgreSQL 9.4, you don’t have to worry about wal_keep_segments because replication slots make sure that the WAL files are kept forever. If it so happens that the standby goes offline, the master can keep track of how much the standby lags and retain the WAL it needs files until the standby reconnects again. Then the WAL files can be decoded and replayed to the replica.
Limitations of PostgreSQL Replication Slots
The WAL files are retained by the master when the replica disconnects. This also means that the pg_wal directory may run out of space. Imagine a scenario where the replica fails forever and cannot be recovered (an orphaned replication slot), or when a replica cannot replay the WAL segments fast enough. The WAL files will just pile up. So you need to monitor the slots and manually drop them. Only when you do that will the master delete anything form the pg_wal directory. We will see how you can monitor and drop replication slots later.
So while WAL retention is taken care of without manual settings, pg_wal directory space needs to be manually monitored.
Types of Replication Slots
PostgreSQL Replication slots are of two types:
- Physical replication slots
- Logical replication slots
Physical Replication Slots
The changes that take place on the main server via streaming replication are recorded in the WAL segments. These WAL files are sent to the standby server and then replayed.
So a physical replication slot can be created on the primary server and the location up to where the transactions have been sent to the standby is stored. Now when the standby loses connection, the primary server will keep those WAL files.
Logical Replication Slots
Logical replication was introduced in PostgreSQL 10. Logical replication brings over only the SQL-like changes. It does not work without replication slots. Logical replication data has to be decoded using a plugin.
How to Create a Replication Slot?
The function pg_create_physical_replication_slot that is used to create a physical replication slot. This command has to be run in the master node.
Let us create a replication slot by the name ‘ocean’.
postgres=# select pg_create_physical_replication_slot(‘ocean’);How to Monitor a Replication Slot?
The below command displays all the replication slots that exist on the database cluster.
postgres=# select * from pg_replication_slots;These are the columns that you will see in the pg_replication_slots view:
- slot_name: This is a unique identifier of the replication slot which can contain lower-case letters, underscore characters, and numbers.
- plugin: For physical slots, it will be null.
- slot_type: Slot type is a text indicating whether the slot is physical or logical.
- datoid: Physical slots have no associated databases and hence is null. For logical slots, it will be the OID of the database this slot is associated with.
- active: this is a Boolean value. It is True if the slot is currently active and is false if it is inactive.
- xmin: This represents the oldest transaction this slot requires the database to keep.
- catalog_xmin: This is the oldest transaction this slot needs the database to retain that affects the system catalogs.
- restart_lsn: Log Sequence Number (LSN) is a unique identifier in the transaction log. Restart_lsn is the oldest WAL which might be needed by this slot.
How to Delete a Replication Slot?
You already know that replication slots have to be created and deleted manually. Do not let the inactive slots stay because the master will retain the WAL files needed by the inactive slot indefinitely and will fill up space in the disk.
This is the command used to delete a replication slot:
postgres=# select pg_drop_replication_slot(‘ocean’);reference:https://hevodata.com/learn/postgresql-replication-slots/