Adam Groves wrote:
I have been ramming my head against the wall for the past two
evenings, trying to get PITR working and it's gotten to the point
where I need to ask someone for help.
I'm basically trying to follow the instructions in the manual in
chapter 23.3.
Which postgres version are you using?
1. pg_ctl start -l logfile
2. SELECT pg_start_backup('label');
3. tar -zcf /mnt/server/archive/file/backup.tar.gz . (the contents of
'data')
4. SELECT pg_stop_backup();
5. pg_ctl stop
6. I now delete the contents of 'data' (making a copy before hand)
7. In 'data': tar xzvf /mnt/server/archive/file/backup.tar.gz
8. echo \"restore_command = 'cp /mnt/server/archive/wal/%f %p'\" >
recovery.conf
9. pg_ctl start -l logfile
Are you doing this all in one row, without any other queries
(especially updates) hitting the database?
The reason that I ask is that postgres 8.1 will *only* archive a
wal segment (a wal file) if it's completly filled. Since every
WAL segment in 8.1 is *exactly* 16MB large, archive_command will
only get called after 16MB worth of wal traffic has happened.
In 8.1, pg_stop_backup() won't trigger archive_command either -
it can't, because postgres 8.1 can't close a wal segment early -
it *has* to write 16MB first. Therefore, the part of the wal
that is bring your filesystem backup into a consistent state
lives in the currently open wal after pg_stop_backup is called.
Now, you remove your datadir, and restore your filesystem backup.
Postgres realizes that it needs the wal segement that was current
when pg_start_backup() was called, and requests this from
restore_command. But since this segement was the current segment
up to the point where you removed your datadir, it got never archived :-(.
Postgres 8.2 improves the situation. It can now switch wal segments
early, and will do so if you call pg_stop_backup().
greetings, Florian Pflug
