Open Standby Postgres database in read-write mode for testing purpose

Question

I am currently using Postgres 12.6 Enterprisedb version. I have a disaster recovery database that is synchronized with the production database in read-only mode. Now, I want to temporarily break the synchronization and open the disaster recovery database in read-write mode for a specific duration. Afterwards, I plan to sync it back to the production database. Are there any mechanisms available in PostgreSQL that allow me to pause or stop the Write-Ahead Logging (WAL) process temporarily? Alternatively, I am also curious if PostgreSQL has a concept similar to Oracle's file flashback feature.

Basically break the sync for temporary open in read-write mode and again back to sync with prod

Answer 1

If you are using 12.6, it is evidence that you don't care much about the health of your data. Update to 12.15 today.

You don't need to pause or stop replication; you have to “promote” the standby database to make it an independent database where you can read and write. One way is to call the pg_promote() function.

The simple way to restore the promoted standby to its original state is to wipe the data directory and start with a new pg_basebackup(). There is, however, a utility that can speed this up considerably: pg_rewind. It will undo all modifications that happened since the standby was promoted.

For pg_rewind to work, there are some prerequisites:

• you need all the WAL that was generated on both sides since the servers drifted apart (you can use wal_keep_segments for that purpose)

• the cluster must have been created with initdb --data-checksums to have data checksums, or wal_log_hints must be set to on