Here is a step by step guide on "How to setup standby with PostgreSQL 8.4. This article will also give you fair enough idea about the log shipping to remote server AKA log archiving.
Pre-requisites:
1. Two machines with same OS and version.
2. PostgreSQL 8.4 should be installed on the both the machines.
3. Passwordless authentication for Postgres user(From server A to Server B and vice versa).
To start with what all you need is 2 hosts with PostgreSQL 8.4 installed on it.
Host Details
Host-1: RHEL1.ajay.com IP: 192.168.178.100
Host-2: RHEL2.ajay.com IP: 192.168.178.101
OS and version
.jpg){kind=small}
PostgreSQL version
.jpg){kind=small}
Step-1
Edit postgresql.conf file, modify below parameters and save the file.
Note: Make sure location we are using for the archives do exist. If not, we need to create directory/directories.
archive_mode = on # allows archiving to be done
archive_command = 'cp %p /data/archive/%f && scp %p postgres@192.168.178.101:/
archive_timeout = 100 # force a logfile segment switch after this
Step-2
As archiving requires restart of the cluster, we have to stop and start the PostgreSQL cluster.
Stop the cluster
/opt/postgres_8.4/bin/pg_ctl -D /data/8.4_data/ stop
Start the cluster
/opt/postgres_8.4/bin/pg_ctl -D /data/8.4_data/ start
Or
/opt/postgres_8.4/bin/pg_ctl -D /data/8.4_data/ restart
Step-3
Now connect to psql to make sure all the parameters we have modified are reflecting the new values. You can also verify the wal archive locations on master and slave whether archives are getting copied or not.
.jpg)
Step-4
Assuming that's everything is working fine now we are ready to create the initial snapshot(Backup) of the master.
Connect to Pgsql and put the database in begin backup mode:
[postgres@RHEL1 ~]$ /opt/postgres_8.4/bin/psql -p 5444 -d postgres
psql (8.4.18)
Type "help" for help.
postgres=#SELECT pg_start_backup('label', true);
Step-5
Now we are going to zip/tar the complete data directory and transfer the backup file handle to the slave.
cd /data
tar -vcf /backup_location/backup_of_
scp /backup_location/backup_of_
Step-6
Once the backup is transferred we need to put the cluster out of the backup mode.(On Master)
postgres=#SELECT pg_stop_backup();
Step-7
On the slave untar the backup of the master to location which is going to be your datadirectpry, in my case "/data/standby8.4"
mv /u01/backup_of_cluster_8.4.tar /data
tar -xvf backup_of_cluster_8.4.tar
Rename directory backup_of_cluster_8.4 to standby8.4
Step-8
Clean up of files which are not required:
rm -rf postmaster.pid postmaster.opts
rm -rf pg_log/*
rm -rf pg-xlog/*
rm -rf backup_label
Step-9
Create recovery.conf file, add below enties and save the file.
restore_command = '/opt/postgres_8.4/bin/pg_
recovery_end_command = 'rm -f /tmp/pgsql.trigger.5444'
Step-10
Edit postgresql.conf file and comment below entries(As we have copied everything from the master.):
#archive_mode = on # allows archiving to be done
#archive_command = 'cp %p /data/archive/%f && scp %p postgres@192.168.178.101:/
#archive_timeout = 100 # force a logfile segment switch after this
Step-11
We are all set to start our slave now. Open 2 sessions, 1 to start the cluster and another one we are going to use the tail log file of cluster.
/opt/postgres_8.4/bin/pg_ctl -D /data/standby8.4 start
Step-12
Log file should look like below:
[postgres@RHEL2 pg_log]$ tail -f postgresql-2014-12-11_040146.
[postgres@RHEL2 pg_log]$ more postgresql-2014-12-11_040146.
LOG: database system was interrupted; last known up at 2014-12-11 03:54:53 IST
LOG: starting archive recovery
LOG: restore_command = '/opt/postgres_8.4/bin/pg_
LOG: recovery_end_command = 'rm -f /tmp/pgsql.trigger.5444'
Trigger file : /tmp/pgsql.trigger.5444
Waiting for WAL file : 00000001.history
WAL file path : /data/standby8.4/prodarchive/
Restoring to... : pg_xlog/RECOVERYHISTORY
Sleep interval : 5 seconds
Max wait interval : 0 forever
Command for restore : cp "/data/standby8.4/prodarchive/
Keep archive history : 000000000000000000000000 and later
running restore :cp: cannot stat `/data/standby8.4/prodarchive/
cp: cannot stat `/data/standby8.4/prodarchive/
cp: cannot stat `/data/standby8.4/prodarchive/
cp: cannot stat `/data/standby8.4/prodarchive/
not restored : history file not found
Trigger file : /tmp/pgsql.trigger.5444
Waiting for WAL file : 00000001000000000000003C
WAL file path : /data/standby8.4/prodarchive/
Restoring to... : pg_xlog/RECOVERYXLOG
Sleep interval : 5 seconds
Max wait interval : 0 forever
Command for restore : cp "/data/standby8.4/prodarchive/
Keep archive history : 000000000000000000000000 and later
running restore : OK
LOG: restored log file "00000001000000000000003C" from archive
LOG: automatic recovery in progress
LOG: redo starts at 0/3C000068
LOG: consistent recovery state reached
Trigger file : /tmp/pgsql.trigger.5444
Waiting for WAL file : 00000001000000000000003D
WAL file path : /data/standby8.4/prodarchive/
Restoring to... : pg_xlog/RECOVERYXLOG
Sleep interval : 5 seconds
Max wait interval : 0 forever
Command for restore : cp "/data/standby8.4/prodarchive/
Keep archive history : 00000001000000000000003C and later
running restore : OK
removing "/data/standby8.4/prodarchive/
removing "/data/standby8.4/prodarchive/
removing "/data/standby8.4/prodarchive/
removing "/data/standby8.4/prodarchive/
