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                   =20
> --------------------------------------------------------
>  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 =3D 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 =3D 'cp -i =
/home/mpaquier/bin/pgsql/archive/node_5432/%f %p'" > =
~/bin/pgsql/slave/recovery.conf
> echo "recovery_target_time =3D '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.
>=20
> mpaquier=3D# \d
>         List of relations
>  Schema | Name | Type  |  Owner  =20
> --------+------+-------+----------
>  public | aa   | table | mpaquier
> (1 row)
> And only table aa exists.
>=20
> Here is more input after resume xlog replay.
> mpaquier=3D# 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=3D# select pg_xlog_replay_resume();
>  pg_xlog_replay_resume=20
> -----------------------
> =20
> (1 row)
> mpaquier=3D# create table bb (a int);
> CREATE TABLE
>=20
> Et voila!
>=20
> On Mon, Aug 12, 2013 at 7:34 PM, ascot.moss@gmail.com =
<ascot.moss@gmail.com> wrote:
> >         - check tables from psql:
> >                 postgres=3D# 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?
>=20
> Regards,
> --=20
> Michael