Re: SPAR Simple PostgreSQL AddOn Replication System

On 12/21/06, org(at)kewlstuff(dot)co(dot)za <org(at)kewlstuff(dot)co(dot)za> wrote:
>
> If you would like to give my humble creation a try...
>
> http://spar.orgfree.com/index.html
>

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[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | yahoo }.com