Thursday, 24 December 2009


Install PROJ
If you are missing proj based on above or running a version below 4.5, then install by following these steps.
tar xvzf proj-4.6.0.tar.gz
cd proj-4.6.0
./configure && make clean && make
make install
cd ..
install GEOS
If you are missing geos based on above or running a version below 3.0, then install by following these steps.
tar xvjf geos-3.0.3.tar.bz2
cd geos-3.0.3
./configure && make clean && make
make install
cd ..

Install Postgresql 8.3.8
Exclude Postgresql from the CentOS base repository as Postgresql 8.3 is not included in the base repository.
echo "exclude=postgresql* >> " /etc/yum.repos.d/CentOS-Base.repo
Download and install the RPMs from
rpm -ivh pgdg-centos-8.3-6.noarch.rpm
yum install postgresql postgresql-server postgresql-contrib postgresql-devel

to "/etc/" and run "ldconfig".

Retrieve the PostGIS source archive from Uncompress and untar the archive.
# gzip -d -c postgis-1.4.0.tar.gz | tar xvf -
Then run:
# ./configure
If you want support for coordinate reprojection, you must have the Proj4 library installed. If ./configure didn't find it, try using --with-proj=PATH switch specify a specific Proj4 installation directory.
If you want to use GEOS functionality, you must have the GEOS library installed. Geos 3.0.3+ is preferred and is required for some functions such as ST_SimplifyPreserveTopology to be available. If ./configure didn't find it, try using --with-geos=PATH to specify the full path to the geos-config program full path.
• Run the compile and install commands.
# make
# make install
All files are installed using information provided by pg_config
Libraries are installed [pkglibdir]/lib/contrib.
Important support files such as lwpostgis.sql are installed in [prefix]/share/contrib.
Loader and dumper binaries are installed in [bindir]/.
PostGIS requires the PL/pgSQL procedural language extension. Before loading the lwpostgis.sql file, you must first enable PL/pgSQL. You should use the createlang command. The PostgreSQL Programmer's Guide has the details if you want to this manually for some reason.
# createlang plpgsql template_postgis
Now load the PostGIS object and function definitions into your database by loading the lwpostgis.sql definitions file.
# psql -Upostgres -d template_postgis -f /usr/share/pgsql/contrib/postgis.sql
The PostGIS server extensions are now loaded and ready to use.
For a complete set of EPSG coordinate system definition identifiers, you can also load the spatial_ref_sys.sql definitions file and populate the SPATIAL_REF_SYS table.
# psql -Upostgres -d template_postgis -f /usr/share/pgsql/contrib/spatial_ref_sys.sql

Wednesday, 23 December 2009

Master Slave Setup


1. Master Host IP is:
2. Slave Host IP is:

--Preparing the Master:

1. Create Replication User on Master
a. Logon to Master database server and issue following command from MySQL prompt.
$ mysql –uroot –p
-> TO 'repl'@’' IDENTIFIED BY 'slavepass';
mysql> flush privileges;
mysql> quit;
2. Update my.cnf file using your favourite editor (vi or nano).
3. Add following lines under [mysqld] section:


4. Restart Master database server
$ /etc/init.d/mysql restart

5. Get the master status information
a. Logon to MySQL and run following commands
$ mysql –uroot –p
b. Leave this session running until you have taken the data snapshot

6. Create a data snapshot [using mysqldump tool]
a. In another ssh session use mysqldump to create a snapshot of all the databases. For example:
$ mysqldump –uroot –p --all-databases --master-data=1 > fulldump.db

7. In the session/window where you acquired the read lock, release the lock:
8. Copy the backup file i.e. fulldump.db onto the replication database server] using scp/rsync:
$ scp fulldump.db root@

--Preparing the Slave:

1. Update my.cnf file using your favourite editor (vi or nano).
a) Add following lines under [mysqld] section

master-password = slavepass

b) Restart database server
$ /etc/init.d/mysql restart
c) Restore backup from file
$ mysql –uroot –p < fulldump.db
2. Start Replication Slave
3. Monitor Replication Status
$ mysql> show slave status\G
4. The slave is catching up Master database if the Slave_IO_Running and Slave_SQL_Running values are both Yes

Monday, 21 December 2009

Installing PostgreSQL Standby Server


1. password less authentication is enabled between Primary and Standby database servers.
2. rsync is installed on both servers
3. vhosts file (i.e. /etc/vhosts) contain entry for ‘standby’ server
4. pg_standby script is installed onto standby server
5. This path exists on 'standby' server i.e. /backup/psql_wal. And postgres user has permissions to read/write
6. PostgreSQL 8.3.8 is installed on both primary and standby servers.

Primary Database Setup:

--Edit postgresql.config file. Set the following parameters

archive_command = '/usr/bin/rsync -a %p standby:/backup/psql_wal/%f </dev/null'
archive_timeout = 120

--Restart postgres to apply chnages:

su postgres
pg_ctl -D /data/pgdata restart

--Start hot backup:
echo "SELECT pg_start_backup('mybackup');" | psql -U postgres

--Backup data directory using tar

tar -cvf /tmp/backup.tar /data/pgdata

--Copy backup onto standby server:

rsync /tmp/backup.tar standby:/backup

--Stop hotbackup, clear the flag
echo "SELECT pg_stop_backup();" | psql -U postgres

Standby Database Setup:

--Stop postgres:
su postgres
pg_ctl -D /data/pgdata stop

--Restore the backup:
rm -fR /data/pgdata/*
tar -xvf /tmp/backup.tar /data/pgdata
rm -fR /data/pgdata/pg_xlog/*
mkdir /data/pgdata/pg_xlog/archive_dir

--Create recovery file: /data/pgdata/recovery.conf
--add following line:
restore_command = 'pg_standby -l -d -s 2 -t /tmp/pgsql.trigger.5432 /backup/psql_wal/ %f %p %r 2>>standby.log'

--Correct permissions:
chown -R postgres:postgres /data/pgdata

--Start postgres on standby server:
su postgres
pg_ctl -D /data/pgdata -l /data/pgdata/pg.log start

Monitor Standby Server:
tail –f /data/pgdata/standby.log

Using MySQL with memcached

Installing memcached on Centos 5 (x86_64)

$ wget

--Install a new RPM server

$ rpm -Uvh rpmforge-release-0.3.6-1.el5.rf.x86_64.rpm

--Now we can simply use yum to install Memcached:
$ yum install memcached

--Starting memcached . Note memcached runs on port 11211

$ memcached -d -u nobody -l

--install libmemcached

$ wget
$ tar -zxf libmemcached-0.35.tar.gz
$ cd libmemcached-0.35
$ ./configure --prefix=/usr/local/memcached
$ make clean && gmake
$ gmake install

--Add following lines into /etc/


-- Creates the necessary links and cache to the most recent shared libraries

$ ldconfig

-- install memcached UDFs
$ wget
$ tar -zxf memcached_functions_mysql-0.9.tar.gz
$ cd memcached_functions_mysql-0.9
$ ./configure --with-mysql=/usr/bin/mysql_config --libdir=/usr/lib64/mysql --with-libmemached=/usr/local/memcached/lib
$ make clean && gmake
$ gmake install

-- Once this is done, initialize memcached functions with MySQL.

shell# cd sql
shell# echo install_functions.sql | mysql

-- Connect to MySQL

mysql> SELECT memc_servers_set('');
mysql> SELECT memc_set('mykey', 'Getting this with SELECT means all works well');
| memc_set('mykey', 'Getting this with SELECT means all works well') |
| 0 |
1 row in set (0.03 sec)

mysql> SELECT memc_get('mykey');
| memc_get('mykey') |
| Getting this with SELECT means all works well |
1 row in set (0.00 sec)

Tuesday, 1 December 2009

replication monitoring script

This script monitors mysqld and replication status. If mysql is not running or replication slave is not running then it will log messages into log file. you can customize this script to send an alert via email etc.


HOST=`hostname -s`



MYSQL="/usr/bin/mysql -h$HOST -u$USR -p$PASS"



MYSQLADMIN="/usr/bin/mysqladmin -h$HOST -u$USR -p$PASS"

MAXBEHIND=7200 # [Critical] Two hours behind

MINBEHIND=3600 # [Warning] One hour behind

# Check if mysqld is alive and accepting connections

IS_ALIVE=`$MYSQLADMIN ping | grep -c 'alive'`

if [ "$IS_ALIVE" != "1" ]; then

ERRMSG="[Critical] MySQL Down. `date`"

echo $ERRMSG >> $LOG



# Replication Monitoring

$MYSQL -e"show slave status\G" > $TMP

SB=`grep Seconds_Behind_Master $TMP | cut -f2 -d: | tr -d " "`


if [ "$SB" = "NULL" ]; then

ERRMSG="[Critical] Replication Down. `date`"

echo $ERRMSG >> $LOG

$MYSQL -e"show slave status\G" >> $LOG

elif [ $SBNUM -ge $MAXBEHIND ]; then

ERRMSG="[Critical] Replication behind ${SBNUM}s. `date`"

echo $ERRMSG >> $LOG

elif [ $SBNUM -ge $MINBEHIND ]; then

ERRMSG="[Warning] Replication behind ${SBNUM}s. `date`"

echo $ERRMSG >> $LOG



Check log file for warnings

shell# tail -f /var/logs/monitoring.txt