Database replication is included in the base distribution since PostgreSQL 7.3.1
[ up ]
How does PostgreSQL replication work?
by reto -
Let's imagine you had two identical databases on two hosts. We call one the Master and Slave the other. The Slave copies periodically (a to be defined value of seconds) the pending transactions from the Master to the Slave database. If there are any transactions to process, the same statements will be executed immediately on the slave. If everything went well, the Slave host initiates the deletion of the pending transactions on the Master database as well and then commits the whole meta transaction. So the consistency of the Master and Slave database is assured all the time by the built-in transaction capability of Postgres.
Installation & Configuration
Consider using PG Version >= 7.3.1 where replication is included in the base distribution (postgresql-7.3.x/contrib/dbmirror)
make
make install
You should now have a file named pending.so that contains the trigger.
Install this file in /usr/local/pgsql/lib (or another suitable location).
If you choose a different location the MirrorSetup.sql script will need
to be modified to reflect your new location. The CREATE FUNCTION command
in the MirrorSetup.sql script associates the trigger function with the
pending.so shared library. Modify the arguments to this command if you
choose to install the trigger elsewhere.
2) Run MirrorSetup.sql
This file contains SQL commands to setup the Mirroring environment.
This includes
- Telling Postgres about the "recordchange" trigger function.
- Creating the Pending,PendingData, MirrorHost, MirroredTransaction tables
To execute the script use psql as follows
psql -f MirrorSetup.sql MyDatabaseName
where MyDatabaseName is the name of the database you wish to install mirroring
on (Your master).
Each slave database needs its own configuration file for the
DBMirror.pl script. See slaveDatabase.conf for a sample.
DON'T FORGET TO CREATE THE TRIGGER ON THE MASTER'S SIDE ;-)
Master
DEFINE AS MASTER FOR REPLICATION:
psql -f MirrorSetup.sql YOUR_DATABASE
psql -d YOUR_DATABASE < AddTrigger_YOUR_TABLE.sql
Relations Inventory of DB Master:
Below, foo is the relation that is being mirrored to the host in HostName.
retoh=# \d
List of relations
Name | Type | Owner
-----------------------------+----------+----------
MirrorHost | table | root
MirrorHost_MirrorHostId_seq | sequence | root
MirroredTransaction | table | root
Pending | table | root
PendingData | table | root
Pending_SeqId_seq | sequence | root
foo | table | postgres
(7 rows)
retoh=# select * from "MirrorHost";
MirrorHostId | HostName
--------------+--------------------
1 | hostname_of_slave
(1 row)
Slave
START SLAVE (as root):
./DBMirror.pl slaveDatabase.conf >/dev/null 2>/dev/null &
FAQ's, Hints & Traps