I have been experimenting with PITR (8.0 beta5), and I have a few
questions.
I started with a fresh cluster (did initdb on data). Then I did the
following sequence of things:
(01) Set in pg_hba.conf:
archive_command = 'test ! -f ..../archive/%f && cp -f
%p ..../archive/%f'
(02) start postmaster
(03) template1=# create database "test";
(02) test=# create table t1(i int, t text);
(05) test=# select pg_start_backup('test.1');
pg_start_backup
-----------------
0/A55290
(1 row)
(06) $ tar cf basebackup/test.1.tar data
(07) test=# select pg_stop_backup();
pg_stop_backup
----------------
0/A552CC
(1 row)
LOG: archived transaction log file
"000000010000000000000000.00A55290.backup"
(08) insert 400k records into t1
LOG: archived transaction log file "000000010000000000000000"
LOG: archived transaction log file "000000010000000000000001"
LOG: archived transaction log file "000000010000000000000002"
(09) test=# drop table t1;
(10) stop postmaster
(11) $ mv data savepgdata/test.1 data # make backup of current state
(12) $ tar xf basebackup/test.1.tar
(13) $ ls savepgdata/test.1/pg_xlog/
000000010000000000000000.00A55290.backup
000000010000000000000003
000000010000000000000004
000000010000000000000005
000000010000000000000006
archive_status/
(13) $ cp savepgdata/test.1/pg_xlog/00000001000000000000000[3456]
data/pg_xlog
(14) make recovery.conf:
restore_command = 'cp ..../%f %p'
# I forgot to 'select now()' above, but the following happened
# to be a lucky guess; it worked the first time
recovery_target_time = '2005-01-08 14:45:00-08'
(15) start postmaster
-- output listed below as "output 1"
(16) test=# select count(*) from t1; -- yields 400000
(17) insert 600k more records into t1
-- lots of errors listed at bottom as "output 2"
(18) test=# select count(*) from t1; -- yields 1000000
So, why the errors after step 15 and 17?
Also, in docs section 22.3.1 I see "...One possible way to handle this
is to set up a cron job that periodically (once a minute, perhaps)
identifies the current WAL segment file and saves it someplace safe.".
How do you identify the current wal segment file? And can you just copy
using "cp" even if it's currently being accessed? And if it is archived
later, you don't need to copy it any more, and you can just start
backing up the new current one? And will there always be at most one
segment (the current one) that needs to be backed up for perfect
recovery ability (within that one minute time frame at least)?
Regards,
Jeff Davis
-----------output 1-----------------
$ ./bin/pg_ctl -D data start
pg_ctl: another postmaster may be running; trying to start postmaster
anyway
LOG: database system was interrupted at 2005-01-08 14:39:29 PST
LOG: starting archive recovery
LOG: restore_command = "cp /home/jdavis/storage/install/pgsql/archive/%
f %p"
LOG: recovery_target_time = 2005-01-08 14:45:00-08
cp: cannot stat
`/home/jdavis/storage/install/pgsql/archive/00000001.history': No such
file or directory
LOG: restored log file "000000010000000000000000.00A55290.backup" from
archive
LOG: restored log file "000000010000000000000000" from archive
LOG: checkpoint record is at 0/A55290
LOG: redo record is at 0/A55290; undo record is at 0/0; shutdown FALSE
LOG: next transaction ID: 497; next OID: 25422
LOG: automatic recovery in progress
LOG: redo starts at 0/A552CC
LOG: restored log file "000000010000000000000001" from archive
postmaster starting
LOG: restored log file "000000010000000000000002" from archive
cp: cannot stat
`/home/jdavis/storage/install/pgsql/archive/000000010000000000000003':
No such file or directory
LOG: recovery stopping before commit of transaction 902, time
2005-01-08 14:45:22 PST
LOG: redo done at 0/30AB010
cp: cannot stat
`/home/jdavis/storage/install/pgsql/archive/00000002.history': No such
file or directory
LOG: selected new timeline ID: 2
cp: cannot stat
`/home/jdavis/storage/install/pgsql/archive/00000001.history': No such
file or directory
LOG: archive recovery complete
LOG: database system is ready
LOG: archived transaction log file "00000002.history"
---------------------------------------------------------
------------ output 2 -----------------
LOG: archived transaction log file "000000020000000000000003"
LOG: archived transaction log file "000000020000000000000004"
LOG: archived transaction log file "000000020000000000000005"
LOG: archive command "test !
-f /home/jdavis/storage/install/pgsql/archive/000000010000000000000000
&& cp
-f /home/jdavis/storage/install/pgsql/data/pg_xlog/000000010000000000000000
/home/jdavis/storage/install/pgsql/archive/000000010000000000000000"failed: return code 256
LOG: archive command "test !
-f /home/jdavis/storage/install/pgsql/archive/000000010000000000000000
&& cp
-f /home/jdavis/storage/install/pgsql/data/pg_xlog/000000010000000000000000
/home/jdavis/storage/install/pgsql/archive/000000010000000000000000"failed: return code 256
LOG: archive command "test !
-f /home/jdavis/storage/install/pgsql/archive/000000010000000000000000
&& cp
-f /home/jdavis/storage/install/pgsql/data/pg_xlog/000000010000000000000000
/home/jdavis/storage/install/pgsql/archive/000000010000000000000000"failed: return code 256
WARNING: transaction log file "000000010000000000000000" could not be
archived: too many failures
LOG: archive command "test !
-f /home/jdavis/storage/install/pgsql/archive/000000010000000000000000
&& cp
-f /home/jdavis/storage/install/pgsql/data/pg_xlog/000000010000000000000000
/home/jdavis/storage/install/pgsql/archive/000000010000000000000000"failed: return code 256
LOG: archive command "test !
-f /home/jdavis/storage/install/pgsql/archive/000000010000000000000000
&& cp
-f /home/jdavis/storage/install/pgsql/data/pg_xlog/000000010000000000000000
/home/jdavis/storage/install/pgsql/archive/000000010000000000000000"failed: return code 256
LOG: archive command "test !
-f /home/jdavis/storage/install/pgsql/archive/000000010000000000000000
&& cp
-f /home/jdavis/storage/install/pgsql/data/pg_xlog/000000010000000000000000
/home/jdavis/storage/install/pgsql/archive/000000010000000000000000"failed: return code 256
WARNING: transaction log file "000000010000000000000000" could not be
archived: too many failures
LOG: archive command "test !
-f /home/jdavis/storage/install/pgsql/archive/000000010000000000000000
&& cp
-f /home/jdavis/storage/install/pgsql/data/pg_xlog/000000010000000000000000
/home/jdavis/storage/install/pgsql/archive/000000010000000000000000"failed: return code 256
LOG: archive command "test !
-f /home/jdavis/storage/install/pgsql/archive/000000010000000000000000
&& cp
-f /home/jdavis/storage/install/pgsql/data/pg_xlog/000000010000000000000000
/home/jdavis/storage/install/pgsql/archive/000000010000000000000000"failed: return code 256
LOG: archive command "test !
-f /home/jdavis/storage/install/pgsql/archive/000000010000000000000000
&& cp
-f /home/jdavis/storage/install/pgsql/data/pg_xlog/000000010000000000000000
/home/jdavis/storage/install/pgsql/archive/000000010000000000000000"failed: return code 256
WARNING: transaction log file "000000010000000000000000" could not be
archived: too many failures
LOG: archive command "test !
-f /home/jdavis/storage/install/pgsql/archive/000000010000000000000000
&& cp
-f /home/jdavis/storage/install/pgsql/data/pg_xlog/000000010000000000000000
/home/jdavis/storage/install/pgsql/archive/000000010000000000000000"failed: return code 256
LOG: archive command "test !
-f /home/jdavis/storage/install/pgsql/archive/000000010000000000000000
&& cp
-f /home/jdavis/storage/install/pgsql/data/pg_xlog/000000010000000000000000
/home/jdavis/storage/install/pgsql/archive/000000010000000000000000"failed: return code 256
LOG: archive command "test !
-f /home/jdavis/storage/install/pgsql/archive/000000010000000000000000
&& cp
-f /home/jdavis/storage/install/pgsql/data/pg_xlog/000000010000000000000000
/home/jdavis/storage/install/pgsql/archive/000000010000000000000000"failed: return code 256
WARNING: transaction log file "000000010000000000000000" could not be
archived: too many failures
