PostgreSQL, Slony-I, pgadmin, win32, First time config/installation

In one occasion, I have to use Slony-I for Database replication. As a starter, I look for my friend (Gooooogle) for help. She showed me some tutorial: some rather outdated with version or some having broken pic link. After quite a lot of struggle, I have managed to get it working, and would like to provide a better tutorial/notes for the installation.

Here is my software config:
PostgreSQL : 8.3.5-2
Slony-I : 1.2.15
OS: Windows XP, Windows Server 2003

Notice that my Postgres version of 8.3.5-2 is not available to be downloaded in official site, which only contain 8.3.5-1 (and higher version) in the mirror I have tried. (Minor version is not showing anywhere, AND 8.3.5-1 is NOT working for me somehow) I am not sure if the procedure will work on future versions but the general wisdom should able to apply to most situation.

My tutorial will be based on Dave's PostgreSQL Blog with extra information that help any first time comer to setup everything gracefully.

The target of the procedure is to setup a localhost replication between 2 database of your choice.

Initial Installation:

Install PostgreSQL server and run Application Stack Builder to add Replication : Slony-I support.
Start pgAdmin III, goto File -> Options, setup the Slony-I Path. On default case it should be C:\Program Files\PostgreSQL\8.3\share
Use pgAdmin, connect to the localhost server, and remember to save password.

Database Preparation:
If it is your first time playing around Slony-I, do not apply anything on the working database yet, which would probably screw things up.
With pgAdmin, backup with default setting a working database into an *.backup file.
Create 2 new database, which will be acting as Master and Slave, with names of your choice. I will use db_1 and db_2 which represent the master and slave respectively.
Restore db_1 with the backup file obtained above, using default option. (Full restore)
Restore db_2 with the backup file, using "Schema only" option.

Setting up Replication at pgAdmin

All the setting is located in Databases : db_1 / db_2 : Replication

Right click on db_1 : Replication and choose "New Slony-I Cluster"
Join existing cluster : UN-checked
Cluster name : sample
Local Node Left : 1
Local Node Right : Master Node
Admin Node Left : 99
Admin Node Right : Admin Node
Press OK.

Right click on db_2 : Replication and choose "New Slony-I Cluster"
Join existing cluster : Checked
Server : localhost
Database : db_1
Cluster name : sample
Local Node Left : 10
Local Node Right : Slave Node
Admin Node : 99 - Admin Node
Press OK.

Upcoming is path setup, which is not well documented in any reference.

Right click on db_1 : Replication : sample : Nodes : Master Node : Path and choose New path
Server : 10 - Slave Node
Connect Info : host=localhost port=5432 user=postgres dbname=db_2
Conn retry : 10
Press OK.

Right click on db_2 : Replication : sample : Nodes : Slave Node : Path and choose New path
Server : 1 - Master Node
Connect Info : host=localhost port=5432 user=postgres dbname=db_1
Conn retry : 10
Press OK.

Now we define Replication set.

Right click on db_1 : Replication : sample : Replication Sets and choose new Replication Set.
ID : 1
Comment : sample set

Now we choose the table to be replicated.
Right click on Replication Sets : sample set : Tables and choose New tables
Table : Any table of your choice
ID : 1
Index : Auto filled when choosing table.
Press OK.

Repeat the above for all the tables that you want to replicate with increasing IDs. If your Database contain hundreds of tables that need to be replicated, this manual action can takes hours. The following SQL can help this progress.

SELECT COL.table_name as table_name , CON.constraint_name as constraint_name FROM information_schema.columns as COL INNER JOIN information_schema.constraint_table_usage as CON ON CON.table_name = COL.table_name WHERE COL.table_schema='public' GROUP BY COL.table_name , CON.constraint_name ORDER BY COL.table_name;

and use the result to fill the following SQL.

SELECT _<ClusterName>.setaddtable(<MasterID>, <ID>, 'public.<table_name>', '<constraint_name>', '');

In our example,
<ClusterName> : sample
<MasterID> : 1
<ID> : increasing counter from 1
<table_name> : from above query
<constraint_name> : from above query

Notice that the query assume that there is one on one mapping between table_name and constraint_name. Otherwise, multiple matches will exist in result set. In my occasion, Hibernate is used and extra constraint is added to some of the tables, and I have added "AND CON.constraint_name not LIKE 'fk%'" in my WHERE clause to filter those constraints.

Now we create a new Subscription
Right click on db_1 : Replication : sample : Replication Sets : sample set : Subscription and choose New subscription.
Origin : 1
Provider : 1 - Master Node
Receiver : 10 - Slave Node
Can forward : UN-checked
Press OK.

Here ends the Database preparation.

Slon daemon setup

Slon daemon is needed to be running in all server involving the replication. The setting is quite tricky and differ from each other for different version of Windows being used.

To ease the installation, add slon.exe to the PATH environment variable.
Control Panel : System : Advanced : Environment Variables
Search for PATH in system environment and append ";C:\Program Files\PostgreSQL\8.3\bin" or other path if your installation is not on default.
Start command prompt and try "slon" to see if the path is correct. If successful, an help screen will be shown.

Register new service:
slon -regservice Slony-I

Create Engine Configure files:
Create folder C:\Slony
Create C:\Slony\db_1.conf with the following contents (2 lines)
cluster_name='sample'
conn_info='host=localhost port=5432 user=postgres dbname=db_1'

Create C:\Slony\db_2.conf with the following contents (2 lines)
cluster_name='sample'
conn_info='host=localhost port=5432 user=postgres dbname=db_2'

Add engine to the daemon:
Do the following in command prompt :
slon -addengine C:\Slony\db_1.conf
slon -addengine C:\Slony\db_2.conf

Double check the above with :
slon -listengines
You should see the path of the two conf files being listed.

Before starting the daemon, there are extra setting to be done for running the service successfully.
In folder C:\Documents and Settings\<Current User>\Application Data\postgresql,
there should be a postgres password file pgpass.conf. Slon need to access this file to connect to the database for replication. However as Slon is a daemon that do not run as current user, so the owner of Slon service should contain such a file.

Copy the file to C:\Documents and Settings\postgres\Application Data\postgresql
On default, this folder do not exist and have to be created manually (unless you login as postgres and saved password before). Make sure user "postgres" have read/write access to the password file.

Go to Control Panel : Administrative Tools: Services and look for "Slony-I"
Enter Slony-I's settings and access "Log On" tab.
Log on as "This account" and enter "postgres".

Now we are ready to start up the service. Either right click on Slony-I in services and choose Enable, or do "net start Slony-I" in command prompt.

Replication should takes place immediately if every setting above is correct.

Diagnose

In most cases if you are working on it for the first time, there would be some errors in the setting and the replication is not working. You can do one of the following to check up errors.

Using pgAdmin, access db_1 : Replication : sample : Nodes : Master Node, on the right panel, access Statistics tab. If replication is working fine, the numbers keep changing. Otherwise multiple "0"s will be shown. Statistics is also available for Slave nodes.

Using Event viewer (Control Panel : Administrative Tools : Event viewer, Slon's activities will be shown in Application session.

If Slon running as Service didn't show its log nicely to read, we can run Slon in command prompt in Debug mode, as follows:
slon -d 4 sample "host=localhost port=5432 user=postgres dbname=db_1"
slon -d 4 sample "host=localhost port=5432 user=postgres dbname=db_2"

Common Mistake

FATAL main: Cannot connect to local database - fe_sendauth: no password

This indicated that pgpass.conf is not found or not accessible.
Make sure pgpass.conf file exist for the user running the Slony-I service but not the current user. And make sure the user running the service should have full access the the password file. Also make sure that Service owner is correctly set up.

Conclusion

Setting up Slony-I for the first time really takes time and effort. I hope this guild can help whoever that want to start using Slony-I for replication on Windows.

Clearly, more in-depth knowledge in replication is needed for creating a real working Replication solution. One can find more information in the reference session below.

Good luck for your Slony-I setup.

References
http://www.slony.info/documentation/
http://www.pgadmin.org/docs/1.8/slony.html
http://pgsnake.blogspot.com/2007/09/setting-up-slony-i-with-pgadmin.html
http://www.nabble.com/No-replication-on-Windows-Vista-td19488860.html