Re: Replication questions - read-only and temporary read/write slaves

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(dot)paquier(at)gmail(dot)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
>