Hi,
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.
Can you please advise?
regards
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
