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

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 <magnus(at)hagander(dot)net>
wrote:

> On Fri, Dec 15, 2017 at 6:03 PM, Tiffany Thang <tiffanythang(at)gmail(dot)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/>
>