Alpha Testing Slony on Win32 Crib Notes

Recently I got the chance to work on setting up a [http://slony.info Slony] replication on a couple of Windows 2003 servers running PostgreSQL 8.1.1. As it happens, I am pretty familiar with setting up Slony on linux machines, but in the world of win32 things are a little different for me. Overall I was actually impressed how smoothly things went. I had to crib together my install plan from various websites and email postings, and to be honest I don’t know of anyone who has set this up outside of the [http://people.planetpostgresql.org/dpage/index.php?/archives/40-A-jolly-in-Tokyo.html guys who ported it] to win32 in the first place. Well now I’ve done it, it worked, and really it looked pretty good; I think once Slony 1.2 is released with official win32 support, Windows admins will have a pretty nice system for doing PostgreSQL replication on thier chosen OS. The following are my “crib notes” on the process involved in setting up Slony replication on Windows. They should be easy enough to follow, though I better make the disclaimer that this is of course Alpha level software so if it eats your whole system don’t look at me. Many thanks to Dave Page and the folks in #slony for letting me bounce ideas off of them. **Steps to install/configure/setup PostgreSQL replication using Slony on Windows.** 1. Acquire a copy of Slony for Windows. You can build it yourself from the slony 1.2 CVS sources or use a prebuilt version. Since the CVS sources may or may not be stable at any given time (they are under active development) I used a pre-compiled demo version that has been used successfully by others. Currently you can download this binary from http://developer.pgadmin.org/~hiroshi/Slony-I/. (It has a build date of 2005-12-19) 2. Unzip the package to $Slony directory 3. Copy the slon and slonik executable from $Slony/bin directories into the $PostgreSQL/bin directory. 4. Copy the slevent.dll, slony1_funcs.dll, and xxid.dll files from $Slony/lib directory into the $PostgreSQL/lib directory. 5. Copy the slony1_base.sql, slony1_base.v80.sql, xxid.v80.sql, slony1_funcs.sql, and slony1_funcs.v80.sql file from $Slony/share to $PostgreSQL/share. (If you had compiled the code from CVS and were using a different version of PostgreSQL, you would also want to copy over the appropriate version specific files as well) 6. From a command prompt, go to $PostgreSQL/lib and type “regsvr32 slevent.dll”. It should respond with “DllRegisterServer in slevent.dll succeeded”. (Technically you could skip this step, but since it formats messages to the event log correctly, it is generally worth it). 7. Switch to the $postgreSQL/bin directory, then type in “slon -regservice” which will register the slon binary as a service. You should recieve a message like “Service registered. Before you can run Slony, you must also register an engine!” 8. Create a “slony” superuser on the database. Make sure this user can connect from both machines. 8.5 Rinse repeat the above steps if you haven’t been doing them on both machines. 9. Make a new directory $PostgreSQL/Slony and CD into it. This directory will be used to hold the text scripts we feed into the slonik program to create the configuration. 10. Make an initial configuration script for slonik, this should set up the replication cluster name and the nodes that are going to be in the cluster. Run this script through slonik “slonik slonyconfigure.txt” 11. At this point you should have a new schema in the database (_foo) that contains the replication information. You can confirm that the entries exist by looking in _foo.sl_node and _foo.sl_path. Make sure to check both servers! At this point if you want to uninstall the slony system from the database, you would drop the _foo schema. Also I normally recommend to alter the slony users “search_path” to include the _foo schema. 12. Create a slon.conf file in $PostgreSQL/data. Documentation on the slon.conf file canbe found at [http://linuxfinances.info/info/runtime-config.html http://linuxfinances.info/info/runtime-config.html]. Generally I just went with all defaults, however being on Windows we do need to set up the “cluster_name” and “conn_info” settings in the conf file. (In Unix this is usually done on the command line… in fact I had never used the slony.conf till I set this up on windows) 13. Run the command “slon -addengine $PostgreSQL/data/slon.conf” and then start the Slony service. 14. At this point you want to verify that your slon dameons are running properly. Check the database logs of both machines to make sure there weren’t any errors on the connections. 15. Create a script of slonik commands creating your set and designating the tables and sequences to be replicated. The file I used is called “slonysetupset.txt” and is located in the $PostgreSQL/Slony directory. You would then execute this via slonik by running “slonik slonysetupset.txt”. You want to verify the set is created in both machines by looking in sl_set. 16. Create a script with the slonik command to subscribe the slave to the master database. The file I used is called “slonysubscribe.txt” and is located in the $postgreSQL/Slony directory. You would then execute this via slonik by running “slonik slonyubscribe.txt”. Once you execute this command, the data will start replicating over. If you have enabled stat_command_string in your postgresql.conf, you can look in pg_stat_activity to see the COPY statements being processed. Depending on the size of the data, this could take some time. Keep an eye on sl_status to watch for how replication is going. 17. That’s it. If for some reason replication stops (maybe someone reboots a database) shut down the slons on both machines, restart any neccessary databases, and restart the slons. Replication should pick back up from there. Again keep an eye on sl_status.