In recent versions of PostgreSQL there are replication capabilities built-in, but for older versions I’ve been using Slony-I. I’m going to describe how I’ve replicated a database running on PostgreSQL 8.4 with Slony 1.2. For more info, you can read the official documentation.
- Create a superuser role in both servers for replication:
CREATE ROLE slony WITH SUPERUSER LOGIN PASSWORD 'mipassword';
- Example values:
CLUSTERNAME=db_cluster MASTERDBNAME=mydb SLAVEDBNAME=mydb MASTERHOST=psql01.example.com SLAVEHOST=psql02.example.com REPLICATIONUSER=slony DBUSER=user export CLUSTERNAME MASTERDBNAME SLAVEDBNAME MASTERHOST SLAVEHOST REPLICATIONUSER DBUSER