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

Thanks!

On Fri, Dec 15, 2017 at 1:56 PM, Stefano <stefanocirelli(at)gmail(dot)com> wrote:

> For n.2, you can promote the standby to became a standalone (r/w) server.
> This may be done via "pg_ctl -D $PGDATA promote" or, if in the
> recovery.conf a "triggerfile" definition has been set, touch-ing the
> triggerfile.
> see https://www.postgresql.org/docs/current/static/standby-settings.html
> https://www.postgresql.org/docs/10/static/app-pg-ctl.html
>
> regards
>
> 2017-12-15 19:30 GMT+01:00 Tiffany Thang <tiffanythang(at)gmail(dot)com>:
>
>> 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/>
>>>
>>
>>
>
>
> --
> /* =================================================== */
>
> "Il libero scambio è come la libera volpe nel libero pollaio"
>
> Serge Latouche, Bergamo, Maggio 2015
> /* =================================================== */
>