PostgreSQL 12 High Availability Cookbook - Third Edition

Built-in delay

When we said PostgreSQL has no way to delay applying WAL data to a streaming replica, we weren't quite telling the whole story. There actually is a setting in versions of PostgreSQL 9.4 and higher that will delay COMMIT of transactions from the upstream primary server.

If we wanted to use this with versions of PostgreSQL between 9.4 and 11, we could simply add this line to the standby's recovery.conf file to wait for an hour before committing data:

recovery_min_apply_delay = 3600 

For PostgreSQL 12 or greater, we would simply add this to postgresql.conf instead.

This parameter acts very similarly to our cumbersome WAL marshaling, but with one key difference. All data in the replication stream is applied as quickly as possible, ...