On Fri, Jan 5, 2018 at 3:58 AM, Tiffany Thang <tiffanythang@xxxxxxxxx> wrote: > Thanks for your input. What I meant to say was rolling back all the changes. > I was hoping for a way to temporary open the read-only standby in r/w for > testing purpose and then rollback all the changes made during the test > without having to re-create the standby from scratch. There is no backend-side feature that allows undo actions, Postgres only supports redo. Recycling an older standby is the speciality of pg_rewind, which supports the possibility of backward timeline lookups from 9.6. So you could emulate the same behavior as Oracle by: 1) Promoting the standby where you want the tests to happen. 2) Run your r/w load on it. 3) Stop the standby. 4) Rewind the standby using pg_rewind, so as it is able to join back the cluster. This needs a new recovery.conf of course. pg_rewind also needs to find in the standby's pg_xlog all the WAL segments from the point where WAL has forked (when the standby has been promoted), up to the point where you run your r/w tests. This can be tricked with wal_keep_segments, with a replication slot or with larger values of checkpoint_timeout and max_wal_size, or by even copying segments from an archive before running the rewind. In all cases be careful of bloat in the partition of pg_xlog. -- Michael
