promoting a streaming warm standby once it catches up to the master.

From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: promoting a streaming warm standby once it catches up to the master.
Date: 2016-10-28 17:53:44
Message-ID: CAMkU=1zab-hSMGYTZxgShtQJZ7W4Am555kOdOry25Ryr6rrHuA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

When developing or testing application code, I like to do it against a
pretty fresh snapshot of the production server. I have a script which
refreshes my dev server by cloning the production server from its most
recent base backup, and recovering it forward from files in the wal
archive. With a few tricks thrown in to pre-stage a copy of the wal and a
copy of the base backup, this can be extremely fast, less than 15 seconds
if the base backup it is working from is reasonable recent.

This works pretty well, except that the dev copy can be out of date by up
to archive_timeout, which is annoying.

I was thinking of making the dev server become a warm standby so it can end
the cloning process by connecting to the production server and fetching the
WAL records since the last log file was archived. I would want it to be
promoted as soon as it asks for more WAL records from the master, but the
master doesn't have any more. But, there doesn't seem to be any option to
do that cleanly, i.e. no setting is available like:

recovery_target = 'caught-up'

Is there any way to achieve this cleanly? The best thing I can think of is
to create a background job which sleeps for a number of seconds that
"should be sufficient" and then creates a trigger file. That seems pretty
grotty.

Is there a better solution?

Cheers,

Jeff

Browse pgsql-general by date

  From Date Subject
Next Message Joanna Xu 2016-10-28 17:54:00 Re: Remove Standby (SLAVE) from Primary (MASTER) - Postgres9.1
Previous Message Scott Marlowe 2016-10-28 17:36:45 Re: How to hint 2 coulms IS NOT DISTINCT FROM each other