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.moss@gmail.com <ascot.moss@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
