Обсуждение: Queries about PostgreSQL PITR
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."
On Fri, Jul 9, 2010 at 6:47 PM, Jayadevan M <Jayadevan.Maymala@ibsplc.com> wrote: > So recovery happened to a point after I dropped the first table and before > I dropped > the second table. Why ? Because you didn't disable recovery_target_inclusive, I guess. http://www.postgresql.org/docs/8.4/static/continuous-archiving.html#RECOVERY-TARGET-INCLUSIVE > 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. How about setting recovery_target_timeline to the old timeline ID (= 1)? http://www.postgresql.org/docs/8.4/static/continuous-archiving.html#RECOVERY-TARGET-TIMELINE Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
Hi, >Because you didn't disable recovery_target_inclusive, I guess. > http://www.postgresql.org/docs/8.4/static/continuous-archiving.html#RECOVERY-TARGET-INCLUSIVE Thanks. I was almost sure this will fix it. But the issue seems to be something else. Even if I give a time that is a few more minutes before what I got from select now(), it is always moving upto/or just before (depending on the above parameter) transaction id 676. The ooutput reads LOG: recovery stopping before commit of transaction 676, time 2010-07-09 07:49:26.580518+05:30 Is there a way to find out the transaction ids and corresponding SQLs, timeline etc? May be doing the recovery in debug/logging mode or something like that? 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."
On Mon, Jul 12, 2010 at 5:29 PM, Jayadevan M <Jayadevan.Maymala@ibsplc.com> wrote: > Hi, >>Because you didn't disable recovery_target_inclusive, I guess. >> > http://www.postgresql.org/docs/8.4/static/continuous-archiving.html#RECOVERY-TARGET-INCLUSIVE > Thanks. I was almost sure this will fix it. But the issue seems to be > something else. Even if I give a time that is a few more minutes before > what I got from select now(), it is always moving upto/or just before > (depending on the above parameter) transaction id 676. The ooutput reads > LOG: recovery stopping before commit of transaction 676, time 2010-07-09 > 07:49:26.580518+05:30 A recovery stops when the commit time > or >= recovery_target_time. So, unless it moves up to the newer commit than recovery_target_time, it cannot stop. > Is there a way to find out the transaction ids and corresponding SQLs, > timeline etc? May be doing the recovery in debug/logging mode or something > like that? xlogviewer reads WAL files and displays the contents of them. But it's been inactive for several years, so I'm not sure if it's available now. http://pgfoundry.org/projects/xlogviewer/ Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
Hello all, One doubt about how PostgreSQL PITR works. Let us say I have all the archived WALs for the past week with archive_command = 'cp -i %p /home/postgres/archive/%f </dev/null' I took a base backup last night. If I try to recover the server today after copying the base backup from yesterday and providing restore_command = 'cp /home/postgres/archive/%f %p' does PostgreSQL go through all the past week's archived WALS or it can figure out that the base backup is from yesterday, so skip a large number of archived WALs and start only from file xxx ? Either way, are there ways to speed up the restore process? 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."