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.
On Fri, Dec 15, 2017 at 12:03:08PM -0500, Tiffany Thang wrote: > 1. set up a read-only slave database? The closest solution I could find is > Hot Standby but the slave would not be accessible until after a > failover.
That's what the parameter hot_standby is for in recovery.conf. When a server is in recovery mode and once it has reached a consistent point, then it can be accessed for read-only queries if this parameter is enabled. You need to be careful about how you want to handle replication conflicts though, particularly if you have long read-queries on standbys, which can be tuned with hot_standby_feedback. Be careful though to not bloat too much the primary: retaining a XID horizon older causes tables to retain more past versions of tuples, which costs in space as well as in future VACUUM cleanups. > 2. temporary convert a read-only slave in read-write mode for testing > read/write workloads? Currently in Oracle, we can temporary open our > read-only standby database in read-write mode to occasionally test our > read-write workloads. We would stop the log apply on the standby database, > convert the read-only database to read-write, > perform our read/write test, discard all the changes after testing and > reopen and resync the standby database in read-only mode. Is there a > similar feature in PostgreSQL or are there ways to achieve something close > to our needs?
Unfortunately not. You could reach the same kind of behavior by promoting a standby, and then do your testing. Then you would need to re-create a standby from scratch. What does "discard all the changes" mean? -- Michael
