Thanks very much! I guess you should use the same machine to carry out the PITR while I am using two physical machines. I will try it again.
On 13 Aug 2013, at 12:11 PM, Michael Paquier wrote: > It looks that you are missing something. Similarly to what you did, here is > an example of PITR using a base backup: > 1) Here is my master node doing some archiving: > $ psql -c 'show archive_command' -p 5432 > archive_command > -------------------------------------------------------- > cp -i %p /home/mpaquier/bin/pgsql/archive/node_5432/%f > (1 row) > 2) Creating data folder of new node using a base backup: > pg_basebackup -D ~/bin/pgsql/slave -p 5432 > echo "port = 5433" >> ~/bin/pgsql/slave/postgresql.conf > This node will run with port 5433. > 3) Creating some data: > $ psql -c 'CREATE TABLE aa AS SELECT generate_series(1,1000000) AS a' -p 5432 > SELECT 1000000 > $ date > 2013-08-12 19:47:33 GMT > $ psql -c 'CREATE TABLE bb AS SELECT generate_series(1,1000000) AS a' -p 5432 > SELECT 1000000 > Similarly to what you did, after doing the recovery table bb will not exist > on the node recovered with PITR. > 4) Preparing recovery for slave: > echo "restore_command = 'cp -i /home/mpaquier/bin/pgsql/archive/node_5432/%f > %p'" > ~/bin/pgsql/slave/recovery.conf > echo "recovery_target_time = '2013-08-12 19:47:33 GMT'" >> > ~/bin/pgsql/slave/recovery.conf > 5) Time to perform the PITR: > $ tail -n3 ~/bin/pgsql/slave/pg_log/postgresql-2013-08-12_195441.log > LOG: recovery stopping before commit of transaction 1305, time 2013-08-12 > 19:48:22.436774+00 > LOG: recovery has paused > HINT: Execute pg_xlog_replay_resume() to continue. > Note that in this case the recovery has stopped such as you can check the > status of the node before resuming its activity (you can as well enforce the > resume if you wish) > 6) Now let's check that the node is in a correct state: > $ psql -p 5433 > psql (9.3beta2) > Type "help" for help. > > mpaquier=# \d > List of relations > Schema | Name | Type | Owner > --------+------+-------+---------- > public | aa | table | mpaquier > (1 row) > And only table aa exists. > > Here is more input after resume xlog replay. > mpaquier=# create table bb (a int); -- Node is still in read-only mode > ERROR: 25006: cannot execute CREATE TABLE in a read-only transaction > LOCATION: PreventCommandIfReadOnly, utility.c:270 > mpaquier=# select pg_xlog_replay_resume(); > pg_xlog_replay_resume > ----------------------- > > (1 row) > mpaquier=# create table bb (a int); > CREATE TABLE > > Et voila! > > On Mon, Aug 12, 2013 at 7:34 PM, ascot.m...@gmail.com <ascot.m...@gmail.com> > wrote: > > - check tables from psql: > > postgres=# select count(1) from test26; > > count > > --------- > > 2600000 > > (1 row) > Perhaps you are connecting to the master node and not the node that has been > recovered when querying that? > > Regards, > -- > Michael
