Thanks Magnus. I did not realize I could use the Hot Standby in read-only mode.
For #2, would it be possible to open the Hot Standby in read/write after breaking the replication and taking a snapshot or can Hot Standby only be open in read/write after a failover? I hoping I can use the same Hot Standby for both #1 and #2. Thanks again. On Fri, Dec 15, 2017 at 12:55 PM, Magnus Hagander <mag...@hagander.net> wrote: > On Fri, Dec 15, 2017 at 6:03 PM, Tiffany Thang <tiffanyth...@gmail.com> > wrote: > >> Hi, >> In PostgreSQL, would it be possible to >> >> 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. >> > > Hot Standby will give you a standby database that is accessible, but in > read-only mode. This sounds like what you're looking for. > > > >> 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? >> > > No, you can't do this with postgres natively. > > You could snapshot your filesystem before opening it and then roll back to > that snapshot, or something like that, but you cannot do it with just > PostgreSQL functionality. > > -- > Magnus Hagander > Me: https://www.hagander.net/ <http://www.hagander.net/> > Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/> >
