Both InnoDB and PostgreSQL - as well as many other databases - use a technique called multi-version concurrency control (MVCC) to provide transaction isolation: transactions should not see the work of other, uncommitted transactions. MVCC means that, when a row is updated, the database stores both the old and new versions of the row.


Thursday, October 14, 2010

Postgresql 9.0 Replication - Step by Step

Postgresql 9.0 Streaming Replication - A Big Competition  

 Now Postgresql 9.0 has come with in-built replication features.  NOW A BIG COMPETITOR FOR MYSQL

This method for those who wants to satisfy them by implementing Asynchronous mode of replicationWho wants Synchronous method, and then have to wait for 9.1 to come in existence.I tried to build a Postgresql Master-Slave replication system with new feature “Streaming Replication (SR)”, and this I have to say, this is very easy and stable method for asynchronous method for replication.While building the SR, I felt, this type of SR is like Mysql Master-slave replication,  Slaves are getting updates from master from log files (binlog in cas e of mysql and xlog in postgres)

The above statement can be taken as rough idea when comparing  Mysql and Postgresql replication method

Steps Included:

1.       Download and build, install Postgresql from source.

2.       Initialize database on master

3.       Some tweaks in postgresql.conf (On Master)

4.       Start backup mode on master

5.       Take snapshot of Data (/usr/local/postgres/data) directory on master

6.       Sync snapshot to slave and remove postmaster.pid  on slave

7.       Some tweaks in postgresql.conf and recovery.conf (On Slaves)

8.       At end start the Database on Master and slave

Download the Database:  (On Master)

# wget http://wwwmaster.postgresql.org/redir/198/h/source/v9.0.0/postgresql-9.0.0.tar.bz2
# tar xzf postgresql-9.0.0.tar.bz2

If you have fresh CentOS/other linux version, then maybe you have to download and install  required packages…  In my case I am Using CentOS 5.3 64 Bit

Run the below command as “ROOT”
# yum install -y glibc gcc libtermcap-devel readline-devel zlib-devel
# useradd postgres
# chown –R postgres:postgres postgresql-9.0.0
# su – postgres
# cd postgresql-9.0.0  (extracted directory)

Run the following command to compile and install Postgresql

# ./configure –prefix=/usr/local/postgres –with-perl
# gmake
# su –
# gmake install
# chown -R postgres:postgres /usr/local/postgres
# mkdir /usr/local/postgres/data
# chown postgres /usr/local/postgres/data
# su  - postgres
# /usr/local/postgres/bin/initdb -D /usr/local/postgres/data

After the initializing the data directory, we have to tweak---
“/usr/local/postgres/data/postgresql.conf“ file
I am showing you required things only

listen_addresses = '*'        
max_connections = 500
shared_buffers = 32MB
wal_level = hot_standby
archive_mode = on
archive_command = 'cp %p /usr/local/postgres/data/pg_xlogarch/%f'
max_wal_senders = 1    ####( this setting for  1 slave can connect with Master)
wal_keep_segments = 32
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
# mkdir  /usr/local/postgres/data/pg_xlogarch/
# su – postgres
# cd /usr/local/postgres/
# vi data/pg_hba.conf (add the below line)

host    replication             all               trust

Now start the Database Instance on Master:

As user postgres

# /usr/local/postgres/bin/pg_ctl -D /usr/local/postgres/data -l logfile start

After starting the database Instance, start the backup mode on DB Master
# cd/usr/local/postgres/bin/
# ./psql –c "SELECT pg_start_backup('initial backup for SR')" = database name for which you want replication

# cd /usr/local/postgres/
# tar  -czf data.tar.gz        data/

Exit from Backup mode 
# /usr/local/postgres/bin/psql –c "SELECT pg_stop_backup()"

# rsync –avz  data.tar.gz postgres@  (run this command after the slave’s Postgresql installation)

Same as master download and install Postgresql on slave
Just do not initialize the database:

As user Postgres
# cd  /usr/local/postgrsql
# tar xzf data.tar.gz
# chown –R postgres:postgres  /usr/local/postgres/data/pg_xlogarch
# cd data
# rm –rf postmaster.pid

# vi  /usr/local/postgrsql/data/Postgresql.conf

In short:
listen_addresses = '*'          # Which IP address(es) to listen on
max_connections = 500        # (change requires restart)shared_buffers = 32MB    #min 128kB
hot_standby = ondatestyle = 'iso, mdy'lc_messages = 'en_US.UTF-8'   # locale for system error message
lc_monetary = 'en_US.UTF-8'        # locale for monetary formatting
lc_numeric = 'en_US.UTF-8'          # locale for number formatting
lc_time = 'en_US.UTF-8'  # locale for time formatting
default_text_search_config = 'pg_catalog.english'

Now create a “recovery.conf under /usr/local/postgres/data/ and put the below lines: (In Slave)

standby_mode = 'on'
primary_conninfo = 'host= port=5432 user=readonly'
restore_command = 'cp /usr/local/postgres/data/pg_xlog/%f  %p'


That's it we are done with configurations.

Start the database instance on slave machine
# /usr/local/postgres/bin/pg_ctl -D /usr/local/postgres/data -l logfile start
If all goes well, the on master, you will see like this on Master:
LOG: replication connection authorized: user=readonly host= port=55811

And on slave’s log file (logfile)
LOG: streaming replication successfully connected to primary
If fails, check "logfile" and send me error (if in case you feel so)

New Addition: If you want to check, which pgsql server is Master or Slave, then run this command:

#  /usr/local/postgres/bin/pg_controldata /usr/local/postgres/data/   (running on Master)
pg_control version number:            903
Catalog version number:               201008051
Database system identifier:           5572911747953590263
Database cluster state:               in production  (This shows, it is master server)
pg_control last modified:             Fri 06 May 2011 05:09:20 PM IST
Latest checkpoint location:           2/97482E60

On Slave:

Same Line will show as:
Database cluster state:               in archive recoveryYou can also use below one: to check

Slave Status:
Postgres=# select pg_last_xlog_replay_location();
(1 row)

isten_addresses = '*'          # what IP address(es) to listen on;
max_connections = 500                   # (change requires restart)
shared_buffers = 32MB                   # min 128kB
hot_standby = on
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'                     # locale for system error message
lc_monetary = 'en_US.UTF-8'                     # locale for monetary formatting
lc_numeric = 'en_US.UTF-8'                      # locale for number formatting
lc_time = 'en_US.UTF-8'                         # locale for time formatting

Linux Commands - Not Usual

Linux Command Tips

Check Validity of a Certificate File (PEM File)

# openssl x509 -in Certificates.pem -inform PEM -text -noout -enddate


What is IaaS or Private Cloud ?

What is IaaS or Private Cloud? And why Enterprise should consider it?

The shortest definition could be. ‘Virtualization on top of an HPC’. Let’s define it like this. What do you think is the biggest limitation to virtualization?

Monitor File Activities - Using iWatch

Monitor File Activities - iWatch

We frequently meet situation, like some files or directories that supposed to secure are having some operations on it, like read, open, close, write etc...

And many times people would like to have a monitoring tool to monitor files activities.

Here’s a solution for this, I got a tool “iWatch”, written in Perl and can also work in Daemon mode.

This tool can be used as to monitor/detect unwanted manipulation on file system and report it to system admins.

RSYNC Server - A Remote File Transfer Protocol - Server Migration Tool


Remote file transfer protocol

Rsync is a linux in-built file transfer program that is similar to RCP or SCP. It includes the added feature of allowing just the differences between two sets of files on two machines to be transferred across the network.  

Rsync sends un-encrypted traffic over the network, but can this can also be tunneled through SSH.

The Rsync listens on port 873.

Postgrsql 9.0 beta - Relased with New Features

Postgresql 9.0 beta - relased

PostgreSQL 9.0 has been released with new interesting features, hot standby & streaming replication

 How it works

Mysql Backup Script - BASH & PERL

This BASH script will take the backup every day and at the end of the month, it will ARCHIVE the Last Day backup to Another Directory.
This Script will also send E-Mail if any thing goes wrong and Even send mails on Completion of Backup Process with Time taken.

PGCluster - Postgres Clustering & Load Balancing

PGCluster - Postgres Clustering & Load Balancing

Download it from here

# useradd postgres
# useradd -M -g postgres postgres
# mkdir /home/postgres

Create RAID on CentOS


First let's understand the term RAID
RAID basically stands for Redundant Array of Inexpensive Disk
RAID is used for Implementing Fastness in reading, writing, and Redundancy on DISks

RAID types:
1: Software RAID
2: Hardware RAID
3: Fake RAID

PostgreSql Server Migration Using PITR

Migrate PostgreSQL Server to New Server With Minimal Downtime using PITR (Point In Time Recovery)

Source Server

mkdir /archive; chown postgres.postgres /archive;
vi /usr/local/postgres/data/postgresql.conf
Add the following lines:
archive_mode = on
archive_command = 'cp -i %p /archive%f'

View Pre/Post install script under RPM Install

View Pre/Post Install Script Under RPM Install

Here is some description that shows/display, what scripts run behind Installing a RPM
It also tells what Bash scripts runs.

Check This Out

$ rpm -qp --scripts memcached-1.2.8-1.el5.x86_64.rpm

LDAP - Authentication server for SSH and SVN

LDAP as Auth server for Users  ---- With SSH and SVNThis HOW TO belongs to those, who have some funda about LDAP, SVN, Apache

Prepare LDAP Server:

yum install openldap{,-clients,-devel,} nss_ldap