we have a problem with our Postgresql 9.0.3 backup database (Ubuntu 10.4).
The backup and restore is done as described here: http://www.postgresql.org/docs/9.0/static/continuous-archiving.html
If you want to know the exact steps of backup and restore,please ask.
We restore the database on the backup host and it's starting. I've append the mainlog.
If we dump or reindex the database we get "could not access status of transaction NNNNNN" errors.
PG tries to access old clog files: "pg_clog/00CD". But in the pg_clog directory the files start at "00E6" and end at "0198".
I've append the pg_controldata output. This clog files doesn't exist on the original database neither.
I could create empty clog files, but it seems that our database is in an inconsistent state?
Any suggestions are welcome.
Regards
Steffen
pg_control version number: 903 Catalog version number: 201008051 Database system identifier: 5531973395283826175 Database cluster state: in production pg_control last modified: Thu 24 Mar 2011 12:22:58 PM UTC Latest checkpoint location: 3FD/3528A5C0 Prior checkpoint location: 3FD/352144C0 Latest checkpoint's REDO location: 3FD/3524ED30 Latest checkpoint's TimeLineID: 2 Latest checkpoint's NextXID: 0/428237218 Latest checkpoint's NextOID: 204294451 Latest checkpoint's NextMultiXactId: 406490 Latest checkpoint's NextMultiOffset: 901370 Latest checkpoint's oldestXID: 242122419 Latest checkpoint's oldestXID's DB: 16409 Latest checkpoint's oldestActiveXID: 0 Time of latest checkpoint: Thu 24 Mar 2011 12:22:23 PM UTC Minimum recovery ending location: 0/0 Backup start location: 0/0 Current wal_level setting: minimal Current max_connections setting: 100 Current max_prepared_xacts setting: 0 Current max_locks_per_xact setting: 64 Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment: 16777216 Maximum length of identifiers: 64 Maximum columns in an index: 32 Maximum size of a TOAST chunk: 1996 Date/time type storage: 64-bit integers Float4 argument passing: by value Float8 argument passing: by value
2011-03-24 11:36:56 UTC LOG: database system was interrupted; last known up at 2011-03-24 05:35:14 UTC 2011-03-24 11:36:56 UTC LOG: incomplete startup packet 2011-03-24 11:36:56 UTC LOG: starting archive recovery 2011-03-24 11:36:56 UTC LOG: restored log file "00000001000003FC000000DC" from archive 2011-03-24 11:36:56 UTC LOG: redo starts at 3FC/DC000020 2011-03-24 11:36:56 UTC FATAL: the database system is starting up 2011-03-24 11:36:56 UTC LOG: restored log file "00000001000003FC000000DD" from archive 2011-03-24 11:36:57 UTC LOG: restored log file "00000001000003FC000000DE" from archive 2011-03-24 11:36:57 UTC FATAL: the database system is starting up 2011-03-24 11:36:57 UTC LOG: restored log file "00000001000003FC000000DF" from archive 2011-03-24 11:36:57 UTC LOG: restored log file "00000001000003FC000000E0" from archive 2011-03-24 11:36:57 UTC LOG: restored log file "00000001000003FC000000E1" from archive 2011-03-24 11:36:57 UTC FATAL: the database system is starting up 2011-03-24 11:36:57 UTC LOG: restored log file "00000001000003FC000000E2" from archive 2011-03-24 11:36:58 UTC LOG: restored log file "00000001000003FC000000E3" from archive 2011-03-24 11:36:58 UTC LOG: restored log file "00000001000003FC000000E4" from archive 2011-03-24 11:36:58 UTC FATAL: the database system is starting up 2011-03-24 11:36:58 UTC LOG: restored log file "00000001000003FC000000E5" from archive 2011-03-24 11:36:58 UTC FATAL: the database system is starting up 2011-03-24 11:36:58 UTC LOG: restored log file "00000001000003FC000000E6" from archive 2011-03-24 11:36:59 UTC LOG: restored log file "00000001000003FC000000E7" from archive 2011-03-24 11:36:59 UTC LOG: restored log file "00000001000003FC000000E8" from archive 2011-03-24 11:36:59 UTC FATAL: the database system is starting up 2011-03-24 11:36:59 UTC LOG: restored log file "00000001000003FC000000E9" from archive 2011-03-24 11:36:59 UTC LOG: restored log file "00000001000003FC000000EA" from archive 2011-03-24 11:36:59 UTC LOG: restored log file "00000001000003FC000000EB" from archive 2011-03-24 11:36:59 UTC FATAL: the database system is starting up 2011-03-24 11:37:00 UTC LOG: restored log file "00000001000003FC000000EC" from archive 2011-03-24 11:37:00 UTC LOG: restored log file "00000001000003FC000000ED" from archive 2011-03-24 11:37:00 UTC FATAL: the database system is starting up 2011-03-24 11:37:00 UTC LOG: restored log file "00000001000003FC000000EE" from archive 2011-03-24 11:37:00 UTC LOG: restored log file "00000001000003FC000000EF" from archive 2011-03-24 11:37:00 UTC LOG: restored log file "00000001000003FC000000F0" from archive 2011-03-24 11:37:00 UTC FATAL: the database system is starting up 2011-03-24 11:37:01 UTC LOG: restored log file "00000001000003FC000000F1" from archive 2011-03-24 11:37:01 UTC LOG: restored log file "00000001000003FC000000F2" from archive 2011-03-24 11:37:01 UTC FATAL: the database system is starting up 2011-03-24 11:37:01 UTC LOG: restored log file "00000001000003FC000000F3" from archive 2011-03-24 11:37:01 UTC LOG: restored log file "00000001000003FC000000F4" from archive 2011-03-24 11:37:01 UTC FATAL: the database system is starting up 2011-03-24 11:37:01 UTC LOG: incomplete startup packet 2011-03-24 11:37:02 UTC LOG: restored log file "00000001000003FC000000F5" from archive 2011-03-24 11:37:02 UTC LOG: restored log file "00000001000003FC000000F6" from archive 2011-03-24 11:37:02 UTC LOG: restored log file "00000001000003FC000000F7" from archive 2011-03-24 11:37:03 UTC LOG: restored log file "00000001000003FC000000F8" from archive 2011-03-24 11:37:03 UTC LOG: restored log file "00000001000003FC000000F9" from archive 2011-03-24 11:37:03 UTC LOG: restored log file "00000001000003FC000000FA" from archive 2011-03-24 11:37:04 UTC LOG: restored log file "00000001000003FC000000FB" from archive 2011-03-24 11:37:04 UTC LOG: restored log file "00000001000003FC000000FC" from archive 2011-03-24 11:37:04 UTC LOG: restored log file "00000001000003FC000000FD" from archive 2011-03-24 11:37:05 UTC LOG: restored log file "00000001000003FC000000FE" from archive 2011-03-24 11:37:05 UTC LOG: restored log file "00000001000003FD00000000" from archive 2011-03-24 11:37:05 UTC LOG: restored log file "00000001000003FD00000001" from archive 2011-03-24 11:37:05 UTC LOG: restored log file "00000001000003FD00000002" from archive 2011-03-24 11:37:06 UTC LOG: restored log file "00000001000003FD00000003" from archive 2011-03-24 11:37:06 UTC LOG: restored log file "00000001000003FD00000004" from archive 2011-03-24 11:37:06 UTC LOG: restored log file "00000001000003FD00000005" from archive 2011-03-24 11:37:07 UTC LOG: restored log file "00000001000003FD00000006" from archive 2011-03-24 11:37:07 UTC LOG: restored log file "00000001000003FD00000007" from archive 2011-03-24 11:37:08 UTC LOG: restored log file "00000001000003FD00000008" from archive 2011-03-24 11:37:08 UTC LOG: restored log file "00000001000003FD00000009" from archive 2011-03-24 11:37:08 UTC LOG: restored log file "00000001000003FD0000000A" from archive 2011-03-24 11:37:09 UTC LOG: restored log file "00000001000003FD0000000B" from archive 2011-03-24 11:37:09 UTC LOG: restored log file "00000001000003FD0000000C" from archive 2011-03-24 11:37:09 UTC LOG: restored log file "00000001000003FD0000000D" from archive 2011-03-24 11:37:10 UTC LOG: restored log file "00000001000003FD0000000E" from archive 2011-03-24 11:37:10 UTC LOG: restored log file "00000001000003FD0000000F" from archive 2011-03-24 11:37:10 UTC LOG: restored log file "00000001000003FD00000010" from archive 2011-03-24 11:37:10 UTC LOG: restored log file "00000001000003FD00000011" from archive 2011-03-24 11:37:11 UTC LOG: restored log file "00000001000003FD00000012" from archive 2011-03-24 11:37:11 UTC LOG: restored log file "00000001000003FD00000013" from archive 2011-03-24 11:37:11 UTC LOG: restored log file "00000001000003FD00000014" from archive 2011-03-24 11:37:12 UTC LOG: restored log file "00000001000003FD00000015" from archive 2011-03-24 11:37:12 UTC LOG: restored log file "00000001000003FD00000016" from archive 2011-03-24 11:37:13 UTC LOG: restored log file "00000001000003FD00000017" from archive 2011-03-24 11:37:13 UTC LOG: restored log file "00000001000003FD00000018" from archive 2011-03-24 11:37:13 UTC LOG: restored log file "00000001000003FD00000019" from archive 2011-03-24 11:37:14 UTC LOG: restored log file "00000001000003FD0000001A" from archive 2011-03-24 11:37:14 UTC LOG: restored log file "00000001000003FD0000001B" from archive 2011-03-24 11:37:14 UTC LOG: restored log file "00000001000003FD0000001C" from archive 2011-03-24 11:37:15 UTC LOG: restored log file "00000001000003FD0000001D" from archive 2011-03-24 11:37:15 UTC LOG: restored log file "00000001000003FD0000001E" from archive 2011-03-24 11:37:15 UTC LOG: restored log file "00000001000003FD0000001F" from archive 2011-03-24 11:37:16 UTC LOG: restored log file "00000001000003FD00000020" from archive 2011-03-24 11:37:16 UTC LOG: restored log file "00000001000003FD00000021" from archive 2011-03-24 11:37:16 UTC LOG: restored log file "00000001000003FD00000022" from archive 2011-03-24 11:37:17 UTC LOG: restored log file "00000001000003FD00000023" from archive 2011-03-24 11:37:17 UTC LOG: restored log file "00000001000003FD00000024" from archive 2011-03-24 11:37:17 UTC LOG: restored log file "00000001000003FD00000025" from archive 2011-03-24 11:37:18 UTC LOG: restored log file "00000001000003FD00000026" from archive 2011-03-24 11:37:18 UTC LOG: restored log file "00000001000003FD00000027" from archive 2011-03-24 11:37:18 UTC LOG: restored log file "00000001000003FD00000028" from archive 2011-03-24 11:37:19 UTC LOG: restored log file "00000001000003FD00000029" from archive 2011-03-24 11:37:19 UTC LOG: restored log file "00000001000003FD0000002A" from archive 2011-03-24 11:37:20 UTC LOG: restored log file "00000001000003FD0000002B" from archive 2011-03-24 11:37:20 UTC LOG: restored log file "00000001000003FD0000002C" from archive 2011-03-24 11:37:20 UTC LOG: restored log file "00000001000003FD0000002D" from archive 2011-03-24 11:37:21 UTC LOG: restored log file "00000001000003FD0000002E" from archive 2011-03-24 11:37:21 UTC LOG: restored log file "00000001000003FD0000002F" from archive 2011-03-24 11:37:21 UTC LOG: restored log file "00000001000003FD00000030" from archive 2011-03-24 11:37:21 UTC LOG: restored log file "00000001000003FD00000031" from archive 2011-03-24 11:37:22 UTC LOG: restored log file "00000001000003FD00000032" from archive 2011-03-24 11:37:22 UTC LOG: restored log file "00000001000003FD00000033" from archive 2011-03-24 11:37:22 UTC LOG: consistent recovery state reached at 3FD/34000000 2011-03-24 11:37:22 UTC LOG: restored log file "00000001000003FD00000034" from archive cp: cannot stat `/tmp/replication/00000001000003FD00000035': No such file or directory 2011-03-24 11:37:23 UTC LOG: could not open file "pg_xlog/00000001000003FD00000035" (log file 1021, segment 53): No such file or directory 2011-03-24 11:37:23 UTC LOG: redo done at 3FD/34017098 2011-03-24 11:37:23 UTC LOG: last completed transaction was at log time 2011-03-24 05:41:50.355309+00 2011-03-24 11:37:23 UTC LOG: restored log file "00000001000003FD00000034" from archive cp: cannot stat `/tmp/replication/00000002.history': No such file or directory 2011-03-24 11:37:23 UTC LOG: selected new timeline ID: 2 cp: cannot stat `/tmp/replication/00000001.history': No such file or directory 2011-03-24 11:37:23 UTC LOG: archive recovery complete 2011-03-24 11:39:23 UTC LOG: autovacuum launcher started 2011-03-24 11:39:23 UTC LOG: database system is ready to accept connections 2011-03-24 11:47:58 UTC ERROR: could not access status of transaction 215826066 2011-03-24 11:47:58 UTC DETAIL: Could not open file "pg_clog/00CD": No such file or directory. 2011-03-24 11:47:58 UTC STATEMENT: COPY domain.publications (publication_id, publication_title, publication_date, publication_type, science_field_keywords, publication_authors, publication_source_key, publication_file, publication_issn, last_modified, publication_file_upload_date, publication_uid) TO stdout; 2011-03-24 12:02:09 UTC ERROR: could not access status of transaction 207617642 2011-03-24 12:02:09 UTC DETAIL: Could not open file "pg_clog/00C5": No such file or directory. 2011-03-24 12:22:36 UTC STATEMENT: REINDEX DATABASE rg_www;
-- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
