Word “Replication” means a process of sharing information so as to
ensure consistency between redundant resources, such as software or
hardware components, to improve reliability, fault-tolerance, or
Replication is very interesting subject in any databases. In database
competition world, PostgreSQL has its own uniqueness in RDBMS Open
source for High availability. Latest PostgreSQL 9.1 has in-built support
of Synchronous and Asynchronous replication. In-built Asynchronous
replications are Warm Standby, Hot Standby and Streaming Replication and
with third party tools Slony,Londiste,Mammoth etc. Below chart will help
you to understand about available Synchronous and Asynchronouse
WAL Shipping (Hot Standby and Warm
PostgreSQL has the ability to ship WAL’s to another Server i.e, Standby.
The Standby server will be running in recovery mode with the pg_standby
utility applying the WAL’s. Primary Server generates archives (a copy of
WAL, usually 16 MB file) and sends them to multiple slaves, later it
will be applied by pg_standby utility.
- Warm Standby: Primary generates archives and feed them to Slave.
Its a WAL Shipping to slave. Slave will be in continous recovery and
not accessible for reads.
- Hot Standby: Hot Standby is the name for the capability to run
queries on a database that is currently performing archive recovery.
In Hot Standby slaves can be used for read-only access.
- Slave applie’s WAL’s periodically not continously, means only
completed XLOG’s will be available to slave as WAL archives and
those will be applied. So, lag will be the unfilled or uncompleted
WAL which has not generated archive. Data loss will be minimum of
one WAL(16 MB).
Trigger Based Replication :
In trigger based replication, tools like Slony,Londiste, Mammoth uses ON
INSERT, ON UPDATE, ON DELETE triggers on tables to maintain replication
between Master and Slave. Slave will hold consistent Snapshots.
Streaming Replication :
Its also called as Binary replication. PostgreSQL, XLOG’s records
generated at primary will be shipped to Standby via network. Lag in
streaming replication is very minimum like single transaction depending
on Network Speed and Hot Standby Settings. Multiple Slave can
be configured. Streaming replication comes with additional process
‘WAL SENDER’ at Primary and ‘WAL RECEIVER’ at Standby.
- On Primary Crash, standby can be recovered in very less time.
- Standby can be opened and it will be in READ ONLY mode.
- It can be used for Reporting Server.
- Load balancing can be configured using pgpool-II between Primary and
- Standby Server should hold same amount of Memory/Disk/CPU etc.,
because, in case of Primary crashes the Slave acts as Primary.
- Minimal Lag i.e. (one transaction behind Primary)
Slony is a asynchronous trigger-based replication. Its a single master
to multiple slave replication system for PostgreSQL. Every table or
sequence on Master will be replicated via remote triggers to Slave.
Updates are committed to one database and are applied to Slave later as
EVENTs. Using Slony Switchover and switchback is possible.
Limitations of Slony-I
- Tables must have a primary key or a unique.
- Only Tables and sequeces are allowed for replication.
- Slave databases cannot be modified.
- Slony-I supports switchback.
- Using Slony-I, we can upgrade PG from one version to another version
without any downtime.
- Slony cannot detect the network failuer, hence causing all the
EVENT’s created at primary will be queued and are released once
Network catch ups.
- NO DDL changes allowed on the replication Tables while Slony Daemons
Do post your comments, they will be highly appreciated.