Thursday, September 17, 2015

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

No comments: