PostgreSQL ERROR: requested wal segment has already been removed

Hello colleagues.

If you use streaming replication in your PostgreSQL database without continuous archiving of WAL logs in your robot, then the database server may reuse these old WAL segments over time before the standby server receives them. If the standby server connected to the sending server (Primary) falls very far behind the sending server (Primary), then the sending server (Primary) can remove the WAL segment still needed by the standby server (Standby), in which case the replication connection will be broken.

As a result, connections between the sending (Primary) and standby (Standby) servers will eventually stop working, and on the standby database server you will see an error in the logs - " requested WAL segment 00000002000000170000006A has already been removed".

This problem can be avoided by making the Primary database store as many of its wal segments as possible. In older versions of the PostgreSQL database, namely up to version 14, the wal_keep_size parameter is responsible for this, and from version 14 and higher, the wal_keep_segments parameter is responsible. The wal_keep_size and wal_keep_segments parameters specify the minimum size of past log file segments stored in the pg_wal directory in case the standby needs to retrieve them for streaming replication.

By default, the settings are 0, which means the system does not store any additional WAL segments for backup purposes, so the number of old WAL segments available for backup servers depends on the location of the previous checkpoint and the state of WAL log archiving. If the parameter values ​​are specified without units of measurement, then the database will understand these values ​​in megabytes.

These parameters must be changed in the main configuration file postgresql.conf of the database (Primary).

Examples.

PostgreSQL version prior to 14:

conf> wal_keep_size = 4096

PostgreSQL version 14 and above:

conf> wal_keep_segments = 4096

After the change, do not forget to restart the database.

Thank you all, I hope my article was of some help to you.