Thread: Replication questions - read-only and temporary read/write slaves
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.
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?
Thanks.
Tiff
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.
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?
Thanks.
Tiff
On Fri, Dec 15, 2017 at 6:03 PM, Tiffany Thang <tiffanythang@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.
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. On Fri, Dec 15, 2017 at 12:55 PM, Magnus Hagander <magnus@hagander.net> wrote:
On Fri, Dec 15, 2017 at 6:03 PM, Tiffany Thang <tiffanythang@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.
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.htmlhttps://www.postgresql.org/docs/10/static/app-pg-ctl.html
2017-12-15 19:30 GMT+01:00 Tiffany Thang <tiffanythang@gmail.com>:
Thanks again.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.On Fri, Dec 15, 2017 at 12:55 PM, Magnus Hagander <magnus@hagander.net> wrote:On Fri, Dec 15, 2017 at 6:03 PM, Tiffany Thang <tiffanythang@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.
--
/* =================================================== */
"Il libero scambio è come la libera volpe nel libero pollaio"
"Il libero scambio è come la libera volpe nel libero pollaio"
Serge Latouche, Bergamo, Maggio 2015
/* =================================================== */
Thanks!
On Fri, Dec 15, 2017 at 1:56 PM, Stefano <stefanocirelli@gmail.com> wrote:
regardsFor 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 --2017-12-15 19:30 GMT+01:00 Tiffany Thang <tiffanythang@gmail.com>:Thanks again.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.On Fri, Dec 15, 2017 at 12:55 PM, Magnus Hagander <magnus@hagander.net> wrote:On Fri, Dec 15, 2017 at 6:03 PM, Tiffany Thang <tiffanythang@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./* =================================================== */
"Il libero scambio è come la libera volpe nel libero pollaio"
Serge Latouche, Bergamo, Maggio 2015/* =================================================== */
Hi happy year, please remove my contact from the list, thank you very much.
Ing. Jonathan Ruiz
De: Stefano <stefanocirelli@gmail.com>
Enviado: viernes, 15 de diciembre de 2017 19:56
Para: pgsql-general@postgresql.org
Asunto: Re: Replication questions - read-only and temporary read/write slaves
Enviado: viernes, 15 de diciembre de 2017 19:56
Para: pgsql-general@postgresql.org
Asunto: 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.htmlhttps://www.postgresql.org/docs/10/static/app-pg-ctl.html
2017-12-15 19:30 GMT+01:00 Tiffany Thang <tiffanythang@gmail.com>:
Thanks again.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.On Fri, Dec 15, 2017 at 12:55 PM, Magnus Hagander <magnus@hagander.net> wrote:On Fri, Dec 15, 2017 at 6:03 PM, Tiffany Thang <tiffanythang@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.
--
/* =================================================== */
"Il libero scambio è come la libera volpe nel libero pollaio"
"Il libero scambio è come la libera volpe nel libero pollaio"
Serge Latouche, Bergamo, Maggio 2015
/* =================================================== */
On Fri, Dec 15, 2017 at 12:03:08PM -0500, Tiffany Thang wrote: > 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. That's what the parameter hot_standby is for in recovery.conf. When a server is in recovery mode and once it has reached a consistent point, then it can be accessed for read-only queries if this parameter is enabled. You need to be careful about how you want to handle replication conflicts though, particularly if you have long read-queries on standbys, which can be tuned with hot_standby_feedback. Be careful though to not bloat too much the primary: retaining a XID horizon older causes tables to retain more past versions of tuples, which costs in space as well as in future VACUUM cleanups. > 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? Unfortunately not. You could reach the same kind of behavior by promoting a standby, and then do your testing. Then you would need to re-create a standby from scratch. What does "discard all the changes" mean? -- Michael
Attachment
Michael,
Thanks for your input. What I meant to say was rolling back all the changes. I was hoping for a way to temporary open the read-only standby in r/w for testing purpose and then rollback all the changes made during the test without having to re-create the standby from scratch.Thanks.
On Wed, Jan 3, 2018 at 11:52 PM, Michael Paquier <michael.paquier@gmail.com> wrote:
On Fri, Dec 15, 2017 at 12:03:08PM -0500, Tiffany Thang wrote:
> 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.
That's what the parameter hot_standby is for in recovery.conf. When a
server is in recovery mode and once it has reached a consistent point,
then it can be accessed for read-only queries if this parameter is
enabled. You need to be careful about how you want to handle replication
conflicts though, particularly if you have long read-queries on
standbys, which can be tuned with hot_standby_feedback. Be careful
though to not bloat too much the primary: retaining a XID horizon older
causes tables to retain more past versions of tuples, which costs in
space as well as in future VACUUM cleanups.
> 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?
Unfortunately not. You could reach the same kind of behavior by
promoting a standby, and then do your testing. Then you would need to
re-create a standby from scratch. What does "discard all the changes"
mean?
--
Michael
On Fri, Jan 5, 2018 at 3:58 AM, Tiffany Thang <tiffanythang@gmail.com> wrote: > Thanks for your input. What I meant to say was rolling back all the changes. > I was hoping for a way to temporary open the read-only standby in r/w for > testing purpose and then rollback all the changes made during the test > without having to re-create the standby from scratch. There is no backend-side feature that allows undo actions, Postgres only supports redo. Recycling an older standby is the speciality of pg_rewind, which supports the possibility of backward timeline lookups from 9.6. So you could emulate the same behavior as Oracle by: 1) Promoting the standby where you want the tests to happen. 2) Run your r/w load on it. 3) Stop the standby. 4) Rewind the standby using pg_rewind, so as it is able to join back the cluster. This needs a new recovery.conf of course. pg_rewind also needs to find in the standby's pg_xlog all the WAL segments from the point where WAL has forked (when the standby has been promoted), up to the point where you run your r/w tests. This can be tricked with wal_keep_segments, with a replication slot or with larger values of checkpoint_timeout and max_wal_size, or by even copying segments from an archive before running the rewind. In all cases be careful of bloat in the partition of pg_xlog. -- Michael