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 <tiffanyth...@gmail.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 <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/> >> > > -- /* =================================================== */ "Il libero scambio รจ come la libera volpe nel libero pollaio" Serge Latouche, Bergamo, Maggio 2015 /* =================================================== */
