ascot.m...@gmail.com wrote: > I am trying another way to test PITR: by recovery_target_time. > > The test machine has the same PG version 9.2.4 and same O/S Ubuntu 12.04 64 > bit. All archived WAL > files are shipped and saved in /var/pgsql/data/archive, the latest time stamp > of them is "2013-08-09 > 19:30:01", the full hot backup time is at '2013-08-09 16:47:12'. > > Case 1) I want to recover PG to the state before 18:03:02 that there were 6 > tables deleted > Case 2) Hope to recover PG to the point of time right before table TEST8 was > created > > Transactions in master: > 16:45:01 (create 4 test tables : test1, test2, test3, test4) > 16:47:12 (FULL HOT BACKUP) > 17:50:22 postgres=# CREATE TABLE test5 (id INTEGER PRIMARY KEY); INSERT > INTO test5 VALUES > (generate_series(1,4000000)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test5; > 17:57:13 postgres=# CREATE TABLE test6 (id INTEGER PRIMARY KEY); INSERT > INTO test6 VALUES > (generate_series(1,1000000)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test6; > postgres=# \d > List of relations > Schema | Name | Type | Owner > --------+-------+-------+---------- > public | test1 | table | postgres (created before full > hot backup) > public | test2 | table | postgres (created before full > hot backup) > public | test3 | table | postgres (created before full > hot backup) > public | test4 | table | postgres (created before full > hot backup) > public | test5 | table | postgres > public | test6 | table | postgres > 18:03:02 postgres=# drop table test1; DROP TABLE > postgres=# drop table test2; DROP TABLE > postgres=# drop table test3; DROP TABLE > postgres=# drop table test4; DROP TABLE > postgres=# drop table test5; DROP TABLE > postgres=# drop table test6; DROP TABLE > postgres=# commit; WARNING: there is no transaction in > progress COMMIT > 18:04:34 postgres=# CREATE TABLE test7 (id INTEGER PRIMARY KEY); INSERT > INTO test7 VALUES > (generate_series(1,1000000)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test7; > 18:11:31 postgres=# CREATE TABLE test8 (id INTEGER PRIMARY KEY); INSERT > INTO test8 VALUES > (generate_series(1,1000000)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test8; > postgres=# CREATE TABLE test9 (id INTEGER PRIMARY KEY); > INSERT INTO test9 VALUES > (generate_series(1,1000000)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test9; > postgres=# CREATE TABLE test10 (id INTEGER PRIMARY > KEY); INSERT INTO test10 VALUES > (generate_series(1,1000000)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test10; > 19:26:18 postgres=# vacuum; > VACUUM > postgres=# begin; INSERT INTO test10 VALUES > (generate_series(2000002,3000002));commit; end; BEGIN INSERT 0 1000001 COMMIT > WARNING: there is no > transaction in progress COMMIT > postgres=# CREATE TABLE test11 (id INTEGER PRIMARY > KEY); INSERT INTO test11 VALUES > (generate_series(1,1000000)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test11; > 19:30:01 (ship the WAL file to test machine) > > > > > CASE-1: '2013-08-09 17:57:55' (only 3 lines in recovery.conf) > restore_command = 'cp /var/pgsql/data/archive/%f %p' > recovery_target_time = '2013-08-09 17:57:55' > recovery_target_inclusive = false > Result: > LOG: starting point-in-time recovery to 2013-08-09 > 17:57:55 > LOG: restored log file "000000010000006F00000066" from > archive > LOG: redo starts at 6F/66000020 > LOG: recovery stopping before commit of transaction > 75891, time 2013-08-09 > 18:07:09.547682+08 > LOG: redo done at 6F/66003DF0 > FATAL: requested recovery stop point is before > consistent recovery point > LOG: startup process (PID 15729) exited with exit code > 1 > LOG: terminating any other active server processes > [1]+ Exit 1 ... > > CASE-2: '2013-08-09 18:06:01' (only 3 lines in recovery.conf) > restore_command = 'cp /var/pgsql/data/archive/%f %p' > recovery_target_time = '2013-08-09 18:06:01' > recovery_target_inclusive = false > Result: > LOG: starting point-in-time recovery to 2013-08-09 > 18:06:01 > LOG: restored log file "000000010000006F000000B0" from > archive > LOG: restored log file "000000010000006F0000009B" from > archive > LOG: redo starts at 6F/9B000020 > LOG: recovery stopping before commit of transaction > 75967, time 2013-08-09 > 19:30:10.217888+08 > LOG: redo done at 6F/9B003500 > FATAL: requested recovery stop point is before > consistent recovery point > LOG: startup process (PID 19100) exited with exit code > 1 > LOG: terminating any other active server processes > [1]+ Exit 1 ... > > > So far I can only restore ALL (i.e. up to 19:30:01) but cannot recover PG at > certain Point-of-time.
The error message: FATAL: requested recovery stop point is before consistent recovery point suggests to me that the online backup had not ended at that time. What exactly did you do at 16:47:12? Did you call pg_stop_backup() after your backup? Is there a file "backup_label" in your data directory? You can only recover to a point in time *after* the time of backup completion. Another hint: specify the time zone for recovery_target_time, like 2013-08-09 18:06:01 PST Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
