Re: Recovery.conf PITR by recovery_target_time

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(dot)moss(at)gmail(dot)com <ascot(dot)moss(at)gmail(dot)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