Postgresql 9.1 + Repmgr Beta 2 - Standby server.

installation.sh

##	
##		Postgres + Repmgr Installation + PHPPGAdmin
##	
##		This procedure outlines the intallation of Repmgr - Beta 2
##		with Postgresql 9.1. additional information & usage can be found at
##		https://github.com/greg2ndQuadrant/repmgr
##	

sudo apt-get update
sudo apt-get upgrade
sudo apt-get install apache2 build-essential postgresql-9.1 postgresql-server-dev-9.1 postgresql-9.1-plr postgresql-contrib postgresql-plpython-9.1 libxml2-dev libproj-dev libjson0-dev xsltproc docbook-xsl docbook-mathml libgdal1-dev phppgadmin

#	Comment out (add # at beginning of line) "allow from 127.0.0.0/255.0.0.0 ::1/128" 
#	Remove # from the line below it, "allow from all".
sudo nano /etc/apache2/conf.d/phppgadmin
sudo service apache2 reload

#	Install Repmgr 2.0 beta 1
sudo apt-get install libxslt-dev libxml2-dev libpam-dev libedit-dev
wget http://www.repmgr.org/download/repmgr-2.0beta1.tar.gz
tar xvzf repmgr-2.0beta1.tar.gz
cd repmgr-2.0beta1
make USE_PGXS=1
sudo make USE_PGXS=1 install
sudo update-alternatives --install /usr/bin/repmgr repmgr /usr/lib/postgresql/9.1/bin/repmgr 10
sudo update-alternatives --install /usr/bin/repmgrd repmgrd /usr/lib/postgresql/9.1/bin/repmgrd 10

#	Configure SSH Access between postgres accounts on all db servers.
#	Change to postgres, as this user will be connecting to others thru rsync
sudo su postgres

#	Generate a key set for postgres
ssh-keygen -t rsa

#	Copy the key to the remote server (in this case postgres at master)
#	Note you may have to make a password for the postgres account on master

	#	These commands describe making a password while on master
	sudo passwd postgres

ssh-copy-id postgres@master

su main_user

	#	If you made a password, now go and remove it (the postgres linux account typically doesnt have a password)
	sudo passwd -d postgres


# 	Add the following (the 3 comment lines & 2 host... lines) to pg_hba.conf
sudo nano /etc/postgresql/9.1/main/pg_hba.conf

#-------------------------------------------------------------------------
# TRUSTED CLUSTER SERVERS (REPMGR REPLICATION)
#-------------------------------------------------------------------------
host    all             repmgr          192.168.1.0/24          trust
host    replication     all             192.168.1.0/24          trust

#	Change the following in postgresql.conf
sudo nano /etc/postgresql/9.1/main/postgresql.conf

#	1. Remove the # from listen_address = 'localhost' & change 'localhost' to '*'
listen_address = '*'

#	2. Remove the # from hot_standby = off and change to on
hot_standby = on

#	Restart postgres to make sure the configs are correct
sudo service postgresql restart

#	Clone the master to the standby (Postgresql should be off, but if its on typically restarting after the clone is fine)
sudo service postgresql stop

#	In this example:
#	-D: is the destination data director (on the standby/this server)
#	-d: is the monitored database on the remote/master server. Repmgr copies the whole data directory, so this should just be a database is most frequently edited
#	-p:	master postgresql port
#	-U: the postresql user to log in with to the master server
#	-R: the local user to run rsync with (this should match the user you configured SSH access with)
#	master_ip should be replaced with the ip of the current master
#	adding --force to the end of this command may be necessary if there are things to overwrite in the dest directory
repmgr -D /var/lib/postgresql/9.1/main -d database -p 5432 -U repmgr -R postgres --verbose standby clone master_ip

#	If the clone is successful, you can now restart postgresql
sudo service postgresql restart # (or start)

#	Make the repmgr.conf file
sudo nano /etc/postgresql/repmgr/repmgr.conf

#	Add this to the file:
cluster=cluster_name
node=2
node_name=standby_name
conninfo='host=standby_ip user=repmgr dbname=database'

#	Register this server as a standby
repmgr -f /etc/postgresql/repmgr/repmgr.conf --verbose standby register