X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 7E9259DCC19 for ; Wed, 22 Feb 2006 12:26:55 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 38214-10 for ; Wed, 22 Feb 2006 12:26:53 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from perth.mailnetwork.co.uk (perth.mailnetwork.co.uk [80.175.30.94]) by postgresql.org (Postfix) with ESMTP id B4DCB9DCC2A for ; Wed, 22 Feb 2006 12:26:52 -0400 (AST) Received: from [213.146.143.243] (port=58898 helo=[172.16.208.176]) by perth.mailnetwork.co.uk with esmtpa (Exim 4.60) (envelope-from ) id 1FBwmT-0007z7-Oc for pgsql-admin@postgresql.org; Wed, 22 Feb 2006 16:23:57 +0000 Message-ID: <43FC90C9.3090204@mailnetwork.co.uk> Date: Wed, 22 Feb 2006 16:26:49 +0000 From: Andy Shellam Reply-To: andy.shellam@mailnetwork.co.uk Organization: www.mailnetwork.co.uk User-Agent: Thunderbird 1.5 (Windows/20051201) MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: WAL recovery Content-Type: multipart/alternative; boundary="------------080601030908030505080805" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.713 required=5 tests=[AWL=-0.233, HTML_10_20=0.945, HTML_MESSAGE=0.001] X-Spam-Score: 0.713 X-Spam-Level: X-Archive-Number: 200602/262 X-Sequence-Number: 20683 This is a multi-part message in MIME format. --------------080601030908030505080805 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hi, I'm trying to get a WAL recovery system set up so I have a hot-spare database server standing by should my first one fail. The idea is that every night, over night, the WAL logs for that day will be shipped from the main server to the standby, and the standby will replay them so it is up to date. Every week a full backup will be taken of the live system, and stored off-site. So far I've got it working so that: - My full, base backup from yesterday has been loaded onto the spare - The WAL logs up to 2PM today have been shipped and replayed onto the spare - all OK to here However, whenever I try to ship more logs and play them, I get the following error in the final file: 2006-02-22 15:50:00 GMT LOG: starting archive recovery 2006-02-22 15:50:00 GMT LOG: restore_command = "cp /mndata/archive/xlog_archive/%f %p" cp: cannot stat `/mndata/archive/xlog_archive/00000001.history': No such file or directory 2006-02-22 15:50:00 GMT LOG: restored log file "0000000100000000000000D9" from archive 2006-02-22 15:50:00 GMT LOG: invalid record length at 0/D9FFDB84 2006-02-22 15:50:00 GMT LOG: invalid primary checkpoint record 2006-02-22 15:50:00 GMT LOG: restored log file "0000000100000000000000D9" from archive 2006-02-22 15:50:00 GMT LOG: restored log file "0000000100000000000000DA" from archive 2006-02-22 15:50:00 GMT LOG: invalid resource manager ID in secondary checkpoint record 2006-02-22 15:50:00 GMT PANIC: could not locate a valid checkpoint record 2006-02-22 15:50:00 GMT LOG: startup process (PID 20792) was terminated by signal 6 2006-02-22 15:50:00 GMT LOG: aborting startup due to startup process failure 2006-02-22 15:50:00 GMT LOG: logger shutting down However, if I delete my PG data directory, restore the same base backup from yesterday, and begin recovery, it recovers right up until the last log file, which the previous roll-forward attempt fails. The log files were fully archived off the live server to begin with so I can't see it's that they've changed or anything. Is this scenario possible - that you can keep rolling forward over log files as long as necessary, or do you always have to start from a base backup? Nothing is changing on the spare, it's literally a sitting duck. Thanks Andy --------------080601030908030505080805 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Hi,

I'm trying to get a WAL recovery system set up so I have a hot-spare database server standing by should my first one fail.
The idea is that every night, over night, the WAL logs for that day will be shipped from the main server to the standby, and the standby will replay them so it is up to date.

Every week a full backup will be taken of the live system, and stored off-site.

So far I've got it working so that:

- My full, base backup from yesterday has been loaded onto the spare
- The WAL logs up to 2PM today have been shipped and replayed onto the spare - all OK to here

However, whenever I try to ship more logs and play them, I get the following error in the final file:

2006-02-22 15:50:00 GMT LOG:  starting archive recovery
2006-02-22 15:50:00 GMT LOG:  restore_command = "cp /mndata/archive/xlog_archive/%f %p"
cp: cannot stat `/mndata/archive/xlog_archive/00000001.history': No such file or directory
2006-02-22 15:50:00 GMT LOG:  restored log file "0000000100000000000000D9" from archive
2006-02-22 15:50:00 GMT LOG:  invalid record length at 0/D9FFDB84
2006-02-22 15:50:00 GMT LOG:  invalid primary checkpoint record
2006-02-22 15:50:00 GMT LOG:  restored log file "0000000100000000000000D9" from archive
2006-02-22 15:50:00 GMT LOG:  restored log file "0000000100000000000000DA" from archive
2006-02-22 15:50:00 GMT LOG:  invalid resource manager ID in secondary checkpoint record
2006-02-22 15:50:00 GMT PANIC:  could not locate a valid checkpoint record
2006-02-22 15:50:00 GMT LOG:  startup process (PID 20792) was terminated by signal 6
2006-02-22 15:50:00 GMT LOG:  aborting startup due to startup process failure
2006-02-22 15:50:00 GMT LOG:  logger shutting down

However, if I delete my PG data directory, restore the same base backup from yesterday, and begin recovery, it recovers right up until the last log file, which the previous roll-forward attempt fails.
The log files were fully archived off the live server to begin with so I can't see it's that they've changed or anything.

Is this scenario possible - that you can keep rolling forward over log files as long as necessary, or do you always have to start from a base backup?  Nothing is changing on the spare, it's literally a sitting duck.

Thanks

Andy
--------------080601030908030505080805--