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

[Tiffany Thang <tiffanythang@xxxxxxxxx>]

Thanks!

On Fri, Dec 15, 2017 at 1:56 PM, Stefano <stefanocirelli@xxxxxxxxx> 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@xxxxxxxxx>:

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@xxxxxxxxxxxx> wrote:

On Fri, Dec 15, 2017 at 6:03 PM, Tiffany Thang <tiffanythang@xxxxxxxxx> 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/
 Work: https://www.redpill-linpro.com/

--

/* =================================================== */

"Il libero scambio è come la libera volpe nel libero pollaio"

Serge Latouche, Bergamo, Maggio 2015

/* =================================================== */