From: | Michael Paquier <michael(dot)paquier(at)gmail(dot)com> |
---|---|
To: | Tiffany Thang <tiffanythang(at)gmail(dot)com> |
Cc: | PostgreSQL mailing lists <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Replication questions - read-only and temporary read/write slaves |
Date: | 2018-01-04 23:04:42 |
Message-ID: | CAB7nPqRwA+hgnOoDhJKX6URg-d3E=-fvTnuQ55B8OtzeUtrFTA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, Jan 5, 2018 at 3:58 AM, Tiffany Thang <tiffanythang(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | ning chan | 2018-01-05 03:57:25 | BDR PG9.4 Replica Identity Full |
Previous Message | Jordan Deitch | 2018-01-04 22:03:45 | Re: Bulk Insert/Update Scenario |