Re: Recovery.conf and PITR by recovery_target_time

hi

>> 16:47:12

SELECT pg_start_backup('hot_backup');
"tar cfP" the PG "data" folder
SELECT pg_stop_backup();

regards

On 9 Aug 2013, at 9:55 PM, Albe Laurenz wrote:

> ascot(dot)moss(at)gmail(dot)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