From: gais <gais@alpenjodel.de>
To: pgsql-admin@postgresql.org
Subject: Point-In-Time Recovery not working
Date: Fri, 26 Aug 2011 18:21:45 +0200
Message-ID: <4E57C819.1010209@alpenjodel.de> (raw)
Hi, new to the list here.
I'm running a caldav server with postgresql backend. For some reason a
user removed his agenda, I want to revert to a state before 20th of
August in order to restore this. I have a configuration with
archive_mode = on in postgresql.conf and a timeout of 24h. I have a dump
from the original database and all WAL files from the last 18 months. I
want to restore the database and run all WAL to restore to it's last
working state.
I've tried two options based on the documentation:
http://www.postgresql.org/docs/8.3/static/continuous-archiving.html
option one:
- clear /var/lib/postgresql/8.3/main
- create a new main folder as user postgres:
[code]initdb -d /var/lib/postgresql/8.3/main[/code]
- start the server starten (this is where it goes wrong I suppose)
- backup the original DB:
[code]psql -f backup.dump postgres[/code]
- stop the server
- create a recovery script /var/lib/postgresql/8.3/main/recovery.conf
[code]restore_command = 'cp /BACKUP/davical/postgresql/%f %p'
recovery_target_time = '2011-08-20 22:39:00 EST'
recovery_target_inclusive = 'false'[/code]
-start the server
This gives the foll
[code]2011-08-26 15:31:47 CEST LOG: database system was shut down at
2011-08-26 15:31:40 CEST
2011-08-26 15:31:47 CEST LOG: could not open file
"pg_xlog/000000010000000000000000" (log file 0, segment 0): No such file
or directory
2011-08-26 15:31:47 CEST LOG: invalid primary checkpoint record
2011-08-26 15:31:47 CEST LOG: could not open file
"pg_xlog/000000010000000000000000" (log file 0, segment 0): No such file
or directory
2011-08-26 15:31:47 CEST LOG: invalid secondary checkpoint record
2011-08-26 15:31:47 CEST PANIC: could not locate a valid checkpoint record
2011-08-26 15:31:47 CEST LOG: startup process (PID 6193) was terminated
by signal 6: Aborted
2011-08-26 15:31:47 CEST LOG: aborting startup due to startup process
failure
[/code]
I suppose by starting the server the xlog-entries are no longer
consistent. But I am not able to restore the dump while the server is
not running?
second option:
- remove /var/lib/postgresql/8.3/main
- create a new main folder from scratch
[code]initdb -d /var/lib/postgresql/8.3/main[/code]
- recover the original DB from a tar.gz file by copying the contents
directly into /var/lib/postgresql/8.3/main/base
- create a recovery script /var/lib/postgresql/8.3/main/recovery.conf:
[code]restore_command = 'cp /BACKUP/davical/postgresql/%f %p'
recovery_target_time = '2011-08-20 22:39:00 EST'
recovery_target_inclusive = 'false'[/code]
-restart server
2011-08-26 16:59:07 CEST LOG: database system was shut down at
2011-08-26 16:54:54 CEST
2011-08-26 16:59:07 CEST LOG: starting archive recovery
2011-08-26 16:59:07 CEST LOG: restore_command = 'cp
/BACKUP/davical/postgresql/%f %p'
2011-08-26 16:59:07 CEST LOG: recovery_target_time = '2011-08-21
05:39:00+02'
2011-08-26 16:59:07 CEST LOG: recovery_target_inclusive = false
cp: cannot stat `/BACKUP/davical/postgresql/00000001.history': No such
file or directory
cp: cannot stat `/BACKUP/davical/postgresql/000000010000000000000000':
No such file or directory
2011-08-26 16:59:07 CEST LOG: automatic recovery in progress
2011-08-26 16:59:07 CEST LOG: record with zero length at 0/440C10
2011-08-26 16:59:07 CEST LOG: redo is not required
cp: cannot stat `/BACKUP/davical/postgresql/000000010000000000000000':
No such file or directory
cp: cannot stat `/BACKUP/davical/postgresql/00000002.history': No such
file or directory
2011-08-26 16:59:07 CEST LOG: selected new timeline ID: 2
cp: cannot stat `/BACKUP/davical/postgresql/00000001.history': No such
file or directory
2011-08-26 16:59:08 CEST LOG: incomplete startup packet
2011-08-26 16:59:08 CEST LOG: archive recovery complete
2011-08-26 16:59:08 CEST LOG: autovacuum launcher started
2011-08-26 16:59:08 CEST LOG: database system is ready to accept
connections
2011-08-26 16:59:08 CEST LOG: archive command failed with exit code 1
2011-08-26 16:59:08 CEST DETAIL: The failed archive command was:
/usr/local/bin/pg_backup pg_xlog/00000002.history 00000002.history
2011-08-26 16:59:09 CEST LOG: archive command failed with exit code 1
2011-08-26 16:59:09 CEST DETAIL: The failed archive command was:
/usr/local/bin/pg_backup pg_xlog/00000002.history 00000002.history
2011-08-26 16:59:10 CEST LOG: archive command failed with exit code 1
2011-08-26 16:59:10 CEST DETAIL: The failed archive command was:
/usr/local/bin/pg_backup pg_xlog/00000002.history 00000002.history
2011-08-26 16:59:10 CEST WARNING: transaction log file
"00000002.history" could not be archived: too many failures
This gives no errors, but it also does not recover any records in the
database? What am I missing?
Gijs
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: gais@alpenjodel.de
Subject: Re: Point-In-Time Recovery not working
In-Reply-To: <4E57C819.1010209@alpenjodel.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox