Thread: warm standby question

warm standby question

From

Sebastian Böhm

Date:

28 February 2013, 23:35:24

Hi,

I have a primary server (9.1), which does wal archiving like this:

wal_level = hot_standby
archive_mode = on
archive_command = 'test ! -f /home/autobackup/wal/%f && cp %p /home/autobackup/wal/%f'


then I have a warm standby with this configuration in recovery.conf:

restore_command = 'cp /opt/WAL/%f %p'
standby_mode = on



when I then start the server it applies all WAL files. But when I then stop the server after a few minutes and start it
again,it asks for a WAL file it already processed. 

Logfile:



2013-02-28 16:21:00 CET LOG:  database system was shut down in recovery at 2013-02-28 16:20:53 CET
2013-02-28 16:21:00 CET LOG:  entering standby mode
2013-02-28 16:21:00 CET LOG:  incomplete startup packet
---> 2013-02-28 16:21:00 CET LOG:  restored log file "0000000100000007000000C0" from archive
2013-02-28 16:21:00 CET LOG:  redo starts at 7/C0F9A650
2013-02-28 16:21:00 CET LOG:  consistent recovery state reached at 7/C0FFE398
cp: cannot stat `/opt/WAL/0000000100000007000000C1': No such file or directory
cp: cannot stat `/opt/WAL/0000000100000007000000C1': No such file or directory
2013-02-28 16:21:01 CET FATAL:  the database system is starting up
2013-02-28 16:21:01 CET FATAL:  the database system is starting up
2013-02-28 16:21:02 CET FATAL:  the database system is starting up
2013-02-28 16:21:02 CET FATAL:  the database system is starting up
2013-02-28 16:21:03 CET FATAL:  the database system is starting up
2013-02-28 16:21:03 CET FATAL:  the database system is starting up
2013-02-28 16:21:04 CET FATAL:  the database system is starting up
2013-02-28 16:21:04 CET FATAL:  the database system is starting up
2013-02-28 16:21:05 CET FATAL:  the database system is starting up
cp: cannot stat `/opt/WAL/0000000100000007000000C1': No such file or directory
2013-02-28 16:21:05 CET FATAL:  the database system is starting up
2013-02-28 16:21:06 CET FATAL:  the database system is starting up
2013-02-28 16:21:06 CET LOG:  incomplete startup packet
2013-02-28 16:21:08 CET LOG:  received fast shutdown request
2013-02-28 16:21:08 CET LOG:  shutting down
2013-02-28 16:21:08 CET LOG:  database system is shut down
2013-02-28 16:21:12 CET LOG:  database system was shut down in recovery at 2013-02-28 16:21:08 CET
2013-02-28 16:21:12 CET LOG:  entering standby mode
----> 2013-02-28 16:21:12 CET LOG:  restored log file "0000000100000007000000C0" from archive
2013-02-28 16:21:12 CET LOG:  redo starts at 7/C0F9A650
2013-02-28 16:21:12 CET LOG:  consistent recovery state reached at 7/C0FFE398
cp: cannot stat `/opt/WAL/0000000100000007000000C1': No such file or directory
cp: cannot stat `/opt/WAL/0000000100000007000000C1': No such file or directory
2013-02-28 16:21:12 CET LOG:  incomplete startup packet
2013-02-28 16:21:13 CET FATAL:  the database system is starting up
2013-02-28 16:21:13 CET FATAL:  the database system is starting up
2013-02-28 16:21:14 CET FATAL:  the database system is starting up
2013-02-28 16:21:14 CET FATAL:  the database system is starting up
2013-02-28 16:21:15 CET FATAL:  the database system is starting up
2013-02-28 16:21:15 CET FATAL:  the database system is starting up
2013-02-28 16:21:16 CET FATAL:  the database system is starting up
2013-02-28 16:21:17 CET FATAL:  the database system is starting up
2013-02-28 16:21:17 CET FATAL:  the database system is starting up
cp: cannot stat `/opt/WAL/0000000100000007000000C1': No such file or directory
2013-02-28 16:21:18 CET FATAL:  the database system is starting up
2013-02-28 16:21:18 CET FATAL:  the database system is starting up
2013-02-28 16:21:18 CET LOG:  incomplete startup packet
cp: cannot stat `/opt/WAL/0000000100000007000000C1': No such file or directory
cp: cannot stat `/opt/WAL/0000000100000007000000C1': No such file or directory


thanks
sebastian

Re: warm standby question

From

Albe Laurenz

Date:

01 March 2013, 12:46:45

Sebastian Böhm wrote:
> I have a primary server (9.1), which does wal archiving like this:
>
> wal_level = hot_standby
> archive_mode = on
> archive_command = 'test ! -f /home/autobackup/wal/%f && cp %p /home/autobackup/wal/%f'
>
>
> then I have a warm standby with this configuration in recovery.conf:
>
> restore_command = 'cp /opt/WAL/%f %p'
> standby_mode = on
>
> when I then start the server it applies all WAL files. But when I then stop the server after a few
> minutes and start it again, it asks for a WAL file it already processed.
>
> Logfile:

[cleaned out irrelevant lines]

> 2013-02-28 16:21:00 CET LOG:  database system was shut down in recovery at 2013-02-28 16:20:53 CET
> 2013-02-28 16:21:00 CET LOG:  entering standby mode
> ---> 2013-02-28 16:21:00 CET LOG:  restored log file "0000000100000007000000C0" from archive
> 2013-02-28 16:21:00 CET LOG:  redo starts at 7/C0F9A650
> 2013-02-28 16:21:00 CET LOG:  consistent recovery state reached at 7/C0FFE398
> cp: cannot stat `/opt/WAL/0000000100000007000000C1': No such file or directory
> cp: cannot stat `/opt/WAL/0000000100000007000000C1': No such file or directory
> cp: cannot stat `/opt/WAL/0000000100000007000000C1': No such file or directory
> 2013-02-28 16:21:08 CET LOG:  received fast shutdown request
> 2013-02-28 16:21:08 CET LOG:  shutting down
> 2013-02-28 16:21:08 CET LOG:  database system is shut down
> 2013-02-28 16:21:12 CET LOG:  database system was shut down in recovery at 2013-02-28 16:21:08 CET
> 2013-02-28 16:21:12 CET LOG:  entering standby mode
> ----> 2013-02-28 16:21:12 CET LOG:  restored log file "0000000100000007000000C0" from archive
> 2013-02-28 16:21:12 CET LOG:  redo starts at 7/C0F9A650
> 2013-02-28 16:21:12 CET LOG:  consistent recovery state reached at 7/C0FFE398
> cp: cannot stat `/opt/WAL/0000000100000007000000C1': No such file or directory
> cp: cannot stat `/opt/WAL/0000000100000007000000C1': No such file or directory
> cp: cannot stat `/opt/WAL/0000000100000007000000C1': No such file or directory
> cp: cannot stat `/opt/WAL/0000000100000007000000C1': No such file or directory
> cp: cannot stat `/opt/WAL/0000000100000007000000C1': No such file or directory

A consistent recovery state was reached at 7/C0FFE398, which is somewhere
in 0000000100000007000000C0 (see the "7" before and the "C0" immediately
after the slash).

After restart, recovery continues from this point (or any subsequent "recovery
restart point").  Ro recover the transactions after 7/C0F9A650, PostgreSQL
needs the rest of the WAL file, so it will be restored again.

Yours,
Laurenz Albe