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

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
/* =================================================== */