Point-In-Time Recovery not working

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux