Replication & Upgrades - PostgreSQL 9 Administration - page 321

1. Ensure that all tables that will be replicated have a Primary Key or Unique
index defined.
2. Install Slony on each node in the configuration.
3. Create a PostgreSQL user to perform replication. You need to create a PostgreSQL
user in each PostgreSQL cluster. This user is dedicated to the Slony replication
system, and must have a robust md5 password for login. Log in each server involved
in the replication, and execute the following command as postgresql Unix user:
createuser -s -W -E my_slony_user

4. Update your PostgreSQL pg_hba.conf . On each server involved in the replication
system, adjust authorization to login so that my_slony_user is able to connect from
each servers to each others to the databases you want to replicate.
host my_db my_slony_user 192.168.1.111/32 md5

host my_db my_slony_user 192.168.1.112/32 md5

5. Provide password in .pgpass . Edit the the ~/.pgpass file, and add lines for each
host, like the following:
192.168.0.1:5432:my_db:my_slony_user:my_slony_pass

6. Edit etc/slony_include.h in the marked places for the following entries:
Edit the file, and adjust the path.

Edit the Slony user

7. Edit etc/bases.h . This file holds the connection information for each database
involved in the replication. There can be a different database name for the same
replication or the same database name for different replication.
# CLUSTER NODE BASE HOST PORT

my_replica 1 my_db 192.168.1.12 5432

my_replica 2 my_db 192.168.1.13 5432

8. Edit etc/relations.h . Edit the tables that you wish to replicate as follows:
# CLUSTER

SET MASTER SLAVE

my_replica 1 1 2

9. Optionally, edit etc/slon.cfg . Edit the file if you want to change the defaults. Slony
will be launch with this configuration file.
10. Use slony-ctl:
./01_create_init.sh -c my_replica
./create_struct.sh -c my_replica
./02_exec_init.sh -c my_replica