Re: pg_standby / WAL archive-restore through system restarts

Just wanted to see if others would confirm my impressions about running
WAL archiving and pg_standby restore.

Server A (Primary): Runs PG 8.3 with WAL archiving enabled. Each WAL is
copied over the network to Server B. (A previous 'tar backup' of the
database along with the requisite psql commands
pg_start_backup()/pg_stop_backup() was done and restored on Server B.)

Server B (Backup): Runs PG 8.3 in 'recovery mode' (recovery.conf), with
pg_standby feeding the WAL archives.

The idea is that if Server A dies, Server B will have a relatively close
snapshot of the most recent database changes and could take over as the
new primary database server.

During regular operations, Server A and Server B may independently need
to be rebooted or postgresql restarted, etc. Is there any sync-ing
between Server A and Server B I have to worry about when doing this?

That is, will Server B need to know anything about the fact that the
postmaster on Server A going up and down? Will it just run normally,
waiting for the restarted Server A to send over new WAL archives?

Can I shutdown Server B simply by killing the postmaster and restart it
back in recovery mode to continue re-syncing where it left off? Or does
stopping Server B in recovery mode require any manual re-sync steps
before it can resume recovery?

If I ever tell Server B to stop recovery (assuming I didn't intend for
it to become the new primary), I cannot just resume recovery mode unless
I do the full data backup again from Server A and restore on Server B in
start in recovery mode. That is, once you tell PG to end recovery, it
becomes a primary and no longer can resume recovery mode without redoing
the recovery setup steps.

Are these correct?

Thanks,
David

David Wall a écrit :
> [...]
> During regular operations, Server A and Server B may independently need
> to be rebooted or postgresql restarted, etc. Is there any sync-ing
> between Server A and Server B I have to worry about when doing this?
> That is, will Server B need to know anything about the fact that the
> postmaster on Server A going up and down?

Server B doesn't need to know anything.

> Will it just run normally,
> waiting for the restarted Server A to send over new WAL archives?
>

Yes, it will. Anyways, be careful on the command line options you use
with pg_standby.

> Can I shutdown Server B simply by killing the postmaster and restart it
> back in recovery mode to continue re-syncing where it left off? Or does
> stopping Server B in recovery mode require any manual re-sync steps
> before it can resume recovery?
>

I think you'll need to redo the whole process : restore the full data
backup, create the recovery.conf file, etc.

> If I ever tell Server B to stop recovery (assuming I didn't intend for
> it to become the new primary), I cannot just resume recovery mode unless
> I do the full data backup again from Server A and restore on Server B in
> start in recovery mode. That is, once you tell PG to end recovery, it
> becomes a primary and no longer can resume recovery mode without redoing
> the recovery setup steps.
>

Correct.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

Thanks, Guillaume. Can anyone confirm his answer to the question below
about restarting the backup postmaster?

It seems counter-intuitive since the backup server is only waiting for
new WAL files to restore, it would seem it could be killed and restarted
at any time and it should just recover as it has been all along. I can
see if I end recovery mode this would be the case, but can't the
recovery postmaster be killed without having it leave recovery mode?

>> Can I shutdown Server B (backup/recovery postmaster) simply by
>> killing the postmaster and restart it back in recovery mode to
>> continue re-syncing where it left off? Or does stopping Server B
>> while in recovery mode require any manual re-sync steps before it can
>> resume recovery?
>
> I think you'll need to redo the whole process : restore the full data
> backup, create the recovery.conf file, etc.

someone wrote:

>>> Can I shutdown Server B (backup/recovery postmaster) simply by
>>> killing the postmaster and restart it back in recovery mode to
>>> continue re-syncing where it left off? Or does stopping Server B
>>> while in recovery mode require any manual re-sync steps before it can
>>> resume recovery?
>>
>> I think you'll need to redo the whole process : restore the full data
>> backup, create the recovery.conf file, etc.

No, you don't. The server can continue replaying files.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

David Wall <d(dot)wall(at)computer(dot)org> writes:
> Thanks, Guillaume. Can anyone confirm his answer to the question below
> about restarting the backup postmaster?

IIRC, it works conveniently in 8.2 and up. Just do pg_ctl stop -m fast.

regards, tom lane

>> Thanks, Guillaume. Can anyone confirm his answer to the question below
>> about restarting the backup postmaster?
>>
>
> IIRC, it works conveniently in 8.2 and up. Just do pg_ctl stop -m fast.
>

Thanks, Tom. What about if the postmaster is just killed (kill pid, or
kill -9 pid) or the server/OS crashes? Will PG 8.3 in recovery mode be
able to come back up okay and resume recovery?

David

David Wall <d(dot)wall(at)computer(dot)org> writes:
>> IIRC, it works conveniently in 8.2 and up. Just do pg_ctl stop -m fast.

> Thanks, Tom. What about if the postmaster is just killed (kill pid, or
> kill -9 pid) or the server/OS crashes? Will PG 8.3 in recovery mode be
> able to come back up okay and resume recovery?

Server crash is okay. kill -9 on a postmaster is never a good idea, for
reasons I don't feel like repeating --- see the archives.

regards, tom lane

Alvaro Herrera a écrit :
> someone wrote:
>
>>>> Can I shutdown Server B (backup/recovery postmaster) simply by
>>>> killing the postmaster and restart it back in recovery mode to
>>>> continue re-syncing where it left off? Or does stopping Server B
>>>> while in recovery mode require any manual re-sync steps before it can
>>>> resume recovery?
>>> I think you'll need to redo the whole process : restore the full data
>>> backup, create the recovery.conf file, etc.
>
> No, you don't. The server can continue replaying files.
>

Oops, sorry about this. I thought it would have a hard time to go back
to recovery mode. That's great to know. Thanks.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

On Tuesday 13 May 2008 12:48:38 Guillaume Lelarge wrote:
> Alvaro Herrera a écrit :
> > someone wrote:
> >>>> Can I shutdown Server B (backup/recovery postmaster) simply by
> >>>> killing the postmaster and restart it back in recovery mode to
> >>>> continue re-syncing where it left off? Or does stopping Server B
> >>>> while in recovery mode require any manual re-sync steps before it can
> >>>> resume recovery?
> >>>
> >>> I think you'll need to redo the whole process : restore the full data
> >>> backup, create the recovery.conf file, etc.
> >
> > No, you don't. The server can continue replaying files.
>
> Oops, sorry about this. I thought it would have a hard time to go back
> to recovery mode. That's great to know. Thanks.
>
>

There is a caveat here, in that you need to keep around n number of xlogs,
where n is determined based on the last restart point processed on the slave.
If you are deleting all xlogs as they are processed, any shutdown will likely
cause you to have to start the whole thing over again. Note pg_standby and
8.3 give some pretty convenient tools to manage this.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL