ROLLBACK DATABASE TO SNAPSHOT ​

Roll back a database to a backup snapshot.

ROLLBACK DATABASE TO SNAPSHOT 'rollback_point' HOT_STANDBY

DATABASE

This command always rolls back the current database. Before running this command, you must connect to the database that you want to roll back. You cannot roll back the yellowbrick database.

SNAPSHOT 'rollback_point'

The snapshot you specify should be the result of the sys.oldest_rollback_point_id_in_replica function.

HOT_STANDBY

The database that you roll back is placed in HOT_STANDBY mode. If necessary, you can use an ALTER DATABASE command to take the database out of HOT_STANDBY mode.

Use Cases ​

There are two main use cases for a rollback operation:

• 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. You do not need to run the ROLLBACK DATABASE command manually in this case. See 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 Rolling Back a Replicated Database.

Note: You cannot run multiple ALTER DATABASE...ALTER REPLICA...PROMOTE operations or explicit ROLLBACK DATABASE operations in parallel. Because of exclusive locks required on shared dependencies (required by the rollback), the PROMOTE operations will block each other and fail. For example:

alter database repldb1 alter replica repldb1_failover promote;
alter database repldb2 alter replica repldb2_failover promote;
Error:
ERROR: Failed to promote replica
DETAIL: Unable to promote replica “repldb1_failover” Reason: ... ROLLBACK DATABASE failed to lock shared relations
...
ERROR: Failed to promote replica
DETAIL: Unable to promote replica “repldb2_failover” Reason: ... ROLLBACK DATABASE failed to lock shared relations

Example ​

The following command rolls back a target database for replication:

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