Published on Wed, Oct 19, 2011
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 accessibility.
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 replication.
WAL Shipping (Hot Standby and Warm Standby):
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.
Disadvantages:
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.
Advantages:
Disadvantages:
Slony Replication:
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
Advantages:
Disadvantages:
Do post your comments, they will be highly appreciated.
–Raghav