Hello all,
I am learning PostgreSQL PITR. I have PostgreSQL running with the
following parameters
set up for archiving/log switching:
archive_mode = on
archive_command = 'cp -i %p /home/postgres/archive/%f </dev/null'
archive_timeout = 300
I checked the archival process. It was running fine. I took a base backup
as specified. All
this was done yesterday and by today, 200+ archived log files were there.
Now I wanted to
see how I could drop a couple of tables and then restore the database with
those tables in tact.
So I did
postgres=# select now()
postgres-# ;
now
----------------------------------
2010-07-09 07:46:44.109004+05:30
(1 row)
test=# \d
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | myt | table | postgres
public | repthis | table | postgres
public | testmyr | table | postgres
(3 rows)
test=# select count(*) from repthis ;
count
-------
19002
(1 row)
test=# select count(*) from testmyr ;
count
-------
2080
(1 row)
test=# drop table repthis
test-# ;
DROP TABLE
-- The order in which I dropped the tables has significance if you see the
final state of the
-- db after recovery.
test=# drop table testmyr ;
DROP TABLE
test=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | myt | table | postgres
(1 row)
Then I stopped the server and started the recovery process as mentioned in
the document -
i.e. cleaned out the directories (except pg_xlog), created a recovery.conf
file and did a
pg_ctl start.
The relevant parameters in recovery.conf were
restore_command = 'cp /home/postgres/archive/%f %p'
recovery_target_time = '2010-07-09 07:46:44'
The time '2010-07-09 07:46:44' is the time I got by executing select now()
earlier in the process.
( 2010-07-09 07:46:44.109004+05:30). There were a few seconds gap after I
got this time
and I dropped the tables.
The recovery ended with these lines -
LOG: restored log file "0000000100000000000000D4" from archive
LOG: restored log file "0000000100000000000000D5" from archive
LOG: restored log file "0000000100000000000000D6" from archive
LOG: recovery stopping before commit of transaction 676, time 2010-07-09
07:49:26.580518+05:30
LOG: redo done at 0/D6006084
cp: cannot stat `/home/postgres/archive/00000002.history': No such file or
directory
LOG: selected new timeline ID: 2
cp: cannot stat `/home/postgres/archive/00000001.history': No such file or
directory
LOG: archive recovery complete
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
So here goes my first question -
Why did it recover to time 2010-07-09 07:49:26 when I mentioned
'2010-07-09 07:46:44' ?
If I login to the database and do a listing of tables,
test=# \d
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | myt | table | postgres
public | testmyr | table | postgres
(2 rows)
test=# select count(*) from testmyr ;
count
-------
2080
(1 row)
So recovery happened to a point after I dropped the first table and before
I dropped
the second table. Why ? Probably answer is the same as the one to my first
question.
Is there a way in which I can now go back a bit further, and ensure I am
back to the
time line before I dropped either of the tables? From documentation, I
think the answer is 'No'.
Of course, I could try the entire recovery process once more, and provide
a couple of minutes
earlier time as recovery_target_time.
Regards,
Jayadevan
DISCLAIMER:
"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
