Re: SPAR Simple PostgreSQL AddOn Replication System

Hi Johnny,

    I must say, I was really fascinated by the idea. This is almost a multi-master replication, although with a lot of grey areas. I had read the docs a few days ago but did not test it. Yesterday, gave it a try; but I am sorry to say it is not working. Here's what I did to test it:

I am on Windows, using MinGW to compile and install postgres.
I compiled the sources for Postgres REL8_2_STABLE.
Installed Tomcat (apache-tomcat-6.0.7.exe) (and it is using java from the following location on my machine: C:\Program Files\Java\jre1.5.0_10\bin\client\jvm.dll).
I placed the spar.war file in the tomcat's webapp directory.

Now, from the command line I fired the following to set up (identical) databases:

# create DATA directories
initdb -D /d/data1
initdb -D /d/data2
initdb -D /d/data3

# startup these database clusters on different ports
pg_ctl start -D /d/data1/ -o "-p 6543" -l /d/data1/server.log
pg_ctl start -D /d/data2/ -o "-p 5432" -l /d/data2/server.log
pg_ctl start -D /d/data3/ -o "-p 4321" -l /d/data3/server.log

# create 'test' database on each of these clusters
createdb -p 6543 test
createdb -p 5432 test
createdb -p 4321 test

# create 'test' tables in each of the cluster's 'test' database
psql.exe -p 6543 test -c "create table test( a int primary key, b int )"
psql.exe -p 5432 test -c "create table test( a int primary key, b int )"
psql.exe -p 4321 test -c "create table test( a int primary key, b int )"

# insert a sample row in each of them
psql.exe -p 6543 test -c "insert into test values( 1, 1 )"
psql.exe -p 5432 test -c "insert into test values( 1, 1 )"
psql.exe -p 4321 test -c "insert into test values( 1, 1 )"

# verify that we have identical data on each database
psql.exe -p 6543 test -c "select * from test"
psql.exe -p 5432 test -c "select * from test"
psql.exe -p 4321 test -c "select * from test"

Now, I configured the SPAR as follows:
Pointed the browser (firefox) to http://localhost:8080/spar/start
On the 'Configuration Set' page I made following 3 entries:

jdbc:postgresql://localhost:6543/test
jdbc:postgresql://localhost:5432/test
jdbc:postgresql://localhost:4321/test

Against each of these entries, I entered my OS user name, left the passwd field blank, and checked the 'M' check-box (monitored).

I clicked on the 'Configure dBs' button.
I clicked on the 'Test dB connections' button. (the three entries were highlighted in green and the rest of the rows in red).
I clicked on the 'Add scripts to DB' button. (8 table (starting with _) and some procedures were created on each of the databases).
Then, on 'Server Control' page, I clicked on 'Start' button.

I guess this is all I need to do, to set up the replication. This basically is a setup similar to the one mentioned under your 'single replication server' Archetype. Please point out if I missed something.

Now that the replication was setup, I wanted to test by inserting some data in one DB and monitor others to see if it shows up there. So I executed the following:

psql.exe -p 6543 test -c "insert into test values( 2, 2 )"

And even after waiting for some time, nothing happened on the other databases! I checked for errors on the 'Server Control' page (I did press Refresh button). I also ran the following to see if any errors occurred:

psql.exe -p 6543 test -c "select * from _errors"
psql.exe -p 5432 test -c "select * from _errors"
psql.exe -p 4321 test -c "select * from _errors"

but nothing!!! I tried with tables with just one integer columns without the primary key, even that didn't work. Can you please see if I made some mistake?

Also, the sources are not available. Although you mention that the software is free, you should consider associating some license with it; just stick a license file in you package. I am no expert here, but I guess BSD would be appropriate for the users to be able to use it without any complications, since Postgres is also under BSD.

Regards,

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com