2

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

Mustafa Özçetin
  • 1,893
  • 1
  • 14
  • 16
Anuj Gupta
  • 27
  • 6

1 Answers1

2

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

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263