SPAR Simple PostgreSQL AddOn Replication System

Hi Johnny,

I must say, I was really fascinated by this. This is almost a
multi-master replication, although with a lot of grey areas. I had re

On 12/21/06, org@kewlstuff.co.za <org@kewlstuff.co.za> wrote:

Couldnt find a replication system that worked and did what I wanted, so I
made one.

If you would like to give my humble creation a try...

http://spar.orgfree.com/index.html
Regards
Johnny

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

On 12/21/06, org@kewlstuff.co.za <org@kewlstuff.co.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[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com

On 1/11/07, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:

Also, the sources are not available.

Do you plan to make the sources available?

--
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 3rd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/

On 1/12/07, org@kewlstuff.co.za <org@kewlstuff.co.za> wrote:

Hi Gurjeet.... glad you copied me, else would have missed it.... no longer
subscribed.

I also almost missed your mail. I was monitoring just the conversation
(gmail) that I sent you mail in, but since you changed the subject, it
almost was lost in the other traffic!!!

Maybe you want to post the this reply to the group. I'm now messing around

with search engines... moved on.

CC'ing the list

Looks like you almost got it right, just missing one step.

1) You use CONFIGURATION to set up the dB connections and yes if they
green... good.
then you clicked on ADD SCRIPTS and yes, this puts all the *** supporting
*** scripts on ALL the dB's for you.

ALSO just make sure the MONITOR (M) selection is checked.... leave all the
others UNCHECKED.

BUT... SPAR *** doesnt *** assume you want to replicate all your tables...
so
Have a look at the TABLE SETUP on the the configuration screen.

You mean the the link (Table Setup) on the homepage? Yeah, I did not notice
it, and expected it to work automatically!!!

2) Setup the tables you want to replicate.

When you click this is will show you all the tables in your dB.... if they
RED... they cant be replicatied... structure or data wrong... BUT if they
GREEN then click the INSTALL BOTH option and submit..... that table will
start replicating.
What this actually does is activate the trigger on the that table across
ALL db's for you.

For the tables created by the script I sent you earlier, (one int
primary-key, one normal int, columns), the entries are red; I did try the
databases individually, still didn't turn green. One thing though, when I
selected the databases individually, the background turned white, which,
according to comments on that page, means the rep-server was not able to
determine the status of this table.

I stopped the replication server from the control-panel and then fired the
following to create no-primary-key (identical) tables:

psql.exe -p 6543 test -c "create table test1( a int )"
psql.exe -p 5432 test -c "create table test1( a int )"
psql.exe -p 4321 test -c "create table test1( a int )"

And then again went to the 'Table Setup' page and now the new table (test1)
does show-up there, but it is also painted red!!!

Can you tell what is the problem here? Or can you give us a setup, tables
and some data, which can prove that it actually works!

3) START server.... you got that one.

You'll see, the dB is still lightning fast.
Then just for fun take one server down for a while... make changes to the
others.... then bring it back... and see what happens.

Thanks for trying Spar... and email me anytime... remember I'm not
subscribed anymore.

Regards Johnny

One more thing Johnny, I asked for sources, others (Jonah) are also asking
for the same. I am sure the community won't show any interest in it if they
don't have the sources. So please consider sending the sources too; and as I
mentioned earlier, you can stick some liberal (preferably BSD) license in
the sources.

Best regards,

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

Ok-- again subscribed to hackers till your questions answered.

I think you missed some of the documentation. On the first page, click on documentation, on that (intro) page, click on 'how it works'... it will tell you about primary keys.

Use this script to make a test table....

CREATE TABLE test
(
id serial NOT NULL,
"int" int4 DEFAULT 0,
string varchar(100),
text text,
logic bool,
blob bytea,
"time" timestamp DEFAULT now(),
CONSTRAINT test_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;

Remember that in replication, when the tables come together, primary keys have to be unique... what SPAR does is intercept the serial key generation to make sure this happens correctly.... docs will explain how I did it.

On your other question licensing... I havnt thought about it too much.

I'm just a hobbiest and I need a web site for my estate agent business, but a) the computer business's that came to see me are not very good, most are just punting someone elses expensive product b) I live in a third world country where a leased line and an ip address costs more than a months salary and c) I love geeking.

So all thats happening is that I'm building up my own technology so I can host offshore, at reasonable costs.
In the next version of both Spar and Ese, (the search engine) I'll will put a license in... it will always be free for use and distribution.
I being thinking about open source, and I like the business model behind it.
When I have my site going I'll do that, will give my site admin a derivative business, that way I get the benefits of core ownership and code improvements... otherwise I cant see the benefits of a business model that just gives code away.

I make my software free, because the feedback is tremendous, makes for a better product, and someone will always say, have you tried this or that, good guage to see if the software deserves to live.

Maybe I should put this on my products, "Made by an estate agent just passing through" ha ha.

In the case of Spar, the core code or "tricks", to make Postgres do it, are actually in the script.... thats on your dB anyway.

regards,
Johnny
----- Original Message -----

For the tables created by the script I sent you earlier, (one int primary-key, one normal int, columns), the entries are red; I did try the databases individually, still didn't turn green. One thing though, when I selected the databases individually, the background turned white, which, according to comments on that page, means the rep-server was not able to determine the status of this table.

psql.exe -p 6543 test -c "create table test1( a int )"