Rolling Back to a Full or Cumulative Backup

This section explians how to roll back a database when you are using Yellowbrick backups for disaster recovery. For information about rolling back a target database for replication, see Rolling Back a Replicated Database.

Note: In order to allow a database to revert to its state when a specific backup was taken, you must use the --rollback-to-snapshot option in the ybbackup command. You cannot roll back to an incremental backup snapshot. The backup type must be full or cumulative.
Follow these steps to roll back a database to the state it was in when a specific full or cumulative backup was taken:
  1. Take a full or cumulative backup and specify the --rollback-to-snapshot option in the ybbackup command. For example:
    $ ybbackup -W --username yb100 -d premdb --cumu --name March18FullRollback --chain March2020 /home/yb100/40backups --rollback-to-snapshot
    Password for user yb100: 
    ...
  2. Use the database as normal. In this example, a table is dropped:
    premdb=# drop table newmatchstats;
    DROP TABLE
    premdb=# \d
              List of relations
     Schema |   Name   | Type  |  Owner  
    --------+----------+-------+---------
     public | awayteam | table | brumsby
     public | hometeam | table | brumsby
     public | match    | table | brumsby
     public | season   | table | brumsby
     public | team     | table | brumsby
    (5 rows)
  3. Determine the rollback point that you need to use in the ROLLBACK DATABASE command. The following function returns the oldest rollback point that will work for the specified backup chain.
    premdb=# select sys.oldest_rollback_point_id_in_chain('March2020');
     oldest_rollback_point_id_in_chain 
    -----------------------------------
     March18FullRollback
    (1 row)
  4. Run the ROLLBACK DATABASE command and specify the name of the backup snapshot (--name in the ybbackup command) as the rollback point:
    premdb=# rollback database to snapshot 'March18FullRollback' hot_standby;
    ROLLBACK DATABASE TO SNAPSHOT
  5. Check that the state of the database has reverted. For example:
    premdb=# \d
                List of relations
     Schema |     Name      | Type  |  Owner  
    --------+---------------+-------+---------
     public | awayteam      | table | brumsby
     public | hometeam      | table | brumsby
     public | match         | table | brumsby
     public | newmatchstats | table | brumsby
     public | season        | table | brumsby
     public | team          | table | brumsby
    (6 rows)

    Note that the newmatchstats table is now present because it was dropped after the cumulative backup was taken.

  6. Depending on how you intend to use the database now that it is rolled back, you can either leave it in HOT_STANDBY mode or take it out of that mode. For example:
    premdb=# alter database premdb set hot_standby off;
    WARNING:  This database will no longer accept restore and replication operations
    ALTER DATABASE