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

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