PostgreSQL PITR

PostgreSQL allows you to restore the database to a specific point in time by using

one of the three options:

recovery_target_name,recovery_target_time,recovery_target_xid,pause_at_recovery_target

Go to master node and do the following steps

Edit postgresql.conf and add\edit  the following lines as given below

listen_addresses = '*'

port = 5432

max_connections = 100

wal_level = hot_standby

archive_mode = on

archive_command = 'cp %p /usr/local/pgsql/archive/%f'

logging_collector = on

log_directory = 'pg_log'

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

For demo purpose we install two instance in the same host on two different ports.

Install the second instance on the port 5433

Open the pg_hba.conf in master and add these lines at the bottom

host    all     all     0.0.0.0/0       trust

host    replication     all     0.0.0.0/0       trust

host    replication     postgres        0.0.0.0/0       trust

Start the postgresql on port 5432 and create a test database and connect to it

create database test_pitr;

\c test_pitr

 create table test(col1 int, col2 char(10));

insert into test select generate_series(1,100),'test';

Go to data directory of secondary node and clear it,

 cd /usr/local/pgsql2/data

 rm -rf *

Connect  to secondary node at 5433 and execute pg_basebackup, which will take care of pg_start_backup('label'); and pg_stop_backup();

pg_basebackup -D /usr/local/pgsql2/data/ --write-recovery-conf --xlog-method=fetch --verbose -h localhost

This command will copy data folder from master with recovery.conf

You will get output something like  this

transaction log start point: 4/8D000028 on timeline 1

transaction log end point: 4/8E000050

pg_basebackup: base backup completee

Connect to master node on 5432 and execute the command to create restore point. The point till which the wal log will be replayed

 select pg_create_restore_point('patch_2015_09_17_2');

Now create some more records

insert into test select generate_series(101,1000),'test';

After this the data directory from master will be copied over to secondary. Open the postgresql.conf and edit as given below

hot_standby = on

And leave the rest as same

Open recovery.conf and edit it as below

standby_mode = 'on'

primary_conninfo = 'user=postgres host=localhost port=5432'

pause_at_recovery_target = true

recovery_target_name = patch_2015_09_17_02

Start the server on 5433

If we check the log file, these are the important sections:

entering standby mode..

recovery stopping at restore point "patch_of_2014_07_02", time

2014-07-02 12:08:57.507946+05:30

recovery has paused

Execute pg_xlog_replay_resume() to continue

Connect to psql on the secondary node and check the record

\c test_pitr

 select * from test order by col1 desc limit 2;

Try to create a table and you may find this error,

ERROR: cannot execute CREATE TABLE in a read-only transaction

The database is still in read-only mode.

Execute the following command

 SELECT pg_xlog_replay_resume();

It's done