Michael, 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.
Thanks. Tiff On Wed, Jan 3, 2018 at 11:52 PM, Michael Paquier <michael.paqu...@gmail.com> wrote: > 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 >
