Rolling Back a Replicated Database

A rollback is a procedure that reverts a target database to a previous known good snapshot.

The most common use case for rolling back a database is as part of a failover procedure when database replication is in use. A rollback of this kind occurs automatically as part of an ALTER DATABASE...ALTER REPLICA...PROMOTE command, as explained in Replication Failover and Failback.

You can roll back a target database that is being used for replication manually if you have taken a target database out of HOT_STANDBY mode for some reason, such as to run some tests that require writes. After you have run these tests, the target database and source database will be out of sync, and you will not be able to resume replication. Resuming replication in this case requires you to roll back the database to a known good snapshot (a backup snapshot created by the replication process). Rolling back a database also puts the database back into HOT_STANDBY mode, which is required before replication can resume.

See also ROLLBACK DATABASE.

The following procedure contains the steps for rolling back a replicated database by using an explicit ROLLBACK DATABASE command:

On the source system, pause replication:

yellowbrick=# alter database premdb alter replica premdb_replica pause;
PAUSE REPLICA

On the target system, take the target database out of HOT_STANDBY mode.

yellowbrick=# alter database premdb_replicated set hot_standby off;
WARNING:  This database will no longer accept restore and replication operations
ALTER DATABASE

On the target system, do some testing on the target database, including some write operations that update or delete from the database. For example:
```
premdb_replicated=# delete from newmatchstats where seasonid=21;
DELETE 34200
```

On the source system, determine the rollback point that you need to use in the ROLLBACK DATABASE command. Both of the following methods will return the oldest rollback point that will work for the specified replica. Run these commands from the database that owns the replica.

premdb=# select sys.oldest_rollback_point_id_in_replica('premdb_replica');
 oldest_rollback_point_id_in_replica 
-------------------------------------
 premdb_replica_20_02_10_17_56_08
(1 row)

premdb=# select * from sys.backup_chain;
 database_id |   chain_name   | policy |      oldest_backup_point_id      |     oldest_rollback_point_id     |         created_time          
-------------+----------------+--------+----------------------------------+----------------------------------+-------------------------------
       16393 | premdb_replica | {}     | premdb_replica_20_02_10_17_56_08 | premdb_replica_20_02_10_17_56_08 | 2020-02-10 13:31:35.964935-08
(1 row)

On the target system, roll back the replica database:

premdb_replicated=# rollback database to snapshot 'premdb_replica_20_02_10_17_56_08' hot_standby;
ROLLBACK DATABASE TO SNAPSHOT

On the source system, resume replication.

yellowbrick=# alter database premdb alter replica premdb_replica resume;
RESUME REPLICA

Verify that the two databases are in synch. For example, in this case they have been rolled back to the point before the DELETE command was run:
Target system:
```
premdb_replicated=# select count(*) from premdb_replicated.public.newmatchstats where seasonid=21;
 count 
-------
 34200
(1 row)
```
Source system:
```
premdb=# select count(*) from premdb.public.newmatchstats where seasonid=21;
 count 
-------
 34200
(1 row)
```