Re: Clustering & Load Balancing & Replication

I guess the latest 8.2 Windows PostgreSQL installer does come with a Slony
option and you can set it up easily using pgadmin too.

This link -->
http://people.planetpostgresql.org/xzilla/index.php?/archives/200-Alpha-testing-Slony-on-win32-Crib-Notes.htmlmight
help you as well.

-----------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 12/25/06, org(at)kewlstuff(dot)co(dot)za <org(at)kewlstuff(dot)co(dot)za> wrote:
>
> Thanks Chris,
> I see you a core member of Slony team and a replication guru so I'll look
> into it.
> I'm not slamming Slony I think its probably the right tool for type of
> work
> your company Afilias does. Just wish you would make an official Windows
> version of Slony as well.
> Anyway thanks for the education, and I think it would be a good thing if
> your site on replication, was also listed on Postgresql... good research.
> Merry Xmas
>
> ----- Original Message -----
> From: "Christopher Browne" <cbbrowne(at)acm(dot)org>
> To: <pgsql-general(at)postgresql(dot)org>
> Sent: Sunday, December 24, 2006 4:23 AM
> Subject: Re: [GENERAL] Clustering & Load Balancing & Replication
>
>
> > Centuries ago, Nostradamus foresaw when org(at)kewlstuff(dot)co(dot)za would write:
> >> Suggest you download my little application and read the documentation,
> >> you'll see its very different, maybe even interesting.
> >> Maybe they should change that to.... Postgres DOES HAVE a free
> >> multi-master
> >> replication system :)
> >
> > It isn't systematically usable as such, without a whole lot of
> > end-user assembly.
> >
> >> One comment they make.... "Heavy write activity can cause excessive
> >> locking,
> >> leading to poor performance. In fact, write performance is often worse
> >> than
> >> that of a single server. Read requests can be sent to any server."
> >> I'm not sure I agree with that... or maybe MVCC is just fantastic.... I
> >> tested it.
> >> The 2 phase commit locking is definitely happening at record level, so
> >> only
> >> if the multimasters all hit the same record is there the potential for
> >> lock
> >> conflict.
> >> Why will dB's being randomly used, hit the same records, I think its a
> >> low
> >> probability to begin with?
> >
> > That's only true if you are certain that the update pattern is NOT
> > involving a shared set of records. IN GENERAL, heavy write activity
> > can cause locking to become mighty expensive, which is certainly a
> > true statement.
> >
> >> Not happy with that, I wrote a multithreaded routine and got them to
> all
> >> smack the same record, it NEVER ROLLED BACK, and if there is
> performance
> >> degradation, I didnt notice it... again probably a testament to the
> MVCC
> >> design.
> >
> > It seems likely to me that this requires some careful validation of
> > testing.
> >
> > An effect we see is that if a set of transactions are "fighting" over
> > a single "balance" record, they will essentially serialize over that.
> >
> > On a system with a single CPU, it is not obvious that you'll see a
> > degradation there because, since you only have the single CPU, it
> > would be serializing the activity anyways.
> >
> > Try it out on an 8-way SMP system and you may see things differently.
> >
> >> In any event if you look at the documentation, you'll see SPAR is not
> >> multimaster or nothing. Can use say one server in an office and another
> >> to
> >> pump data to a remote web site... not sure if you would even call that
> >> multimaster, thats the point, I'm not sure SPAR fits any pure theory
> >> category.
> >
> > There are a few tests I could throw at it that tend to challenge
> > replication systems vis-a-vis "fidelity of results." I otta see if I
> > can find them in a readily deployable form.
> >
> > There are two notable anomalies which have been known to break
> > replication systems:
> >
> > 1. Nondeterministic updates:
> >
> > For instance, functions that are nondeterministic:
> >
> > insert into rtable values (random(), now());
> >
> > Or result sets that are nondeterministic:
> >
> > insert into rtable2 (select * from mytable where some_attr='foo'
> > order by random() limit 5); -- Where there are 25 records with
> > some_attr='foo'
> >
> > 2. Value swapping:
> >
> > Consider the table:
> >
> > create table t1 (mk integer primary key, val text unique not null);
> >
> > insert into t1 (mk, val) values (1, 'chris');
> > insert into t1 (mk, val) values (2, 'dave');
> > insert into t1 (mk, val) values (3, 'brad');
> >
> > begin;
> > update t1 set mk = 99 where mk = 1;
> > update t1 set mk = 1 where mk = 3;
> > update t1 set mk = 3 where mk = 99;
> > commit;
> >
> > Is there a condition where a pause somewhere in there will cause
> > replication to break? Note that there have been replication systems
> > (erServer) that this set of updates can, intermittently, cause to fall
> > over.
> > --
> > let name="cbbrowne" and tld="linuxfinances.info" in String.concat "@"
> > [name;tld];;
> > http://cbbrowne.com/info/slony.html
> > "Feel free to contact me (flames about my english and the useless of
> > this driver will be redirected to /dev/null, oh no, it's full...)"
> > -- Michael Beck, describing the PC-speaker sound device
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>