Thread: postgresql replication

Hello,

in need to increase reliability of the service (and perhaps eventually
offload main DB server) we are looking to setup replication for the
database server. I found two solutions:

Slony ( http://gborg.postgresql.org/project/slony1/projdisplay.php )
PGCluster ( http://pgfoundry.org/projects/pgcluster )

I found that PgCluster supports multi-muster mode, which we can
benefit from, but it's not required for "backup" which is #1 goal at
the moment.

If anyone used those solutions, compared performance, reliability, etc
- please share your experience / thoughts.

--
Vlad

On Wed, 4 May 2005 20:49:59 -0400 Vlad <marchenko@gmail.com> wrote:

> Hello,

> in need to increase reliability of the service (and perhaps eventually
> offload main DB server) we are looking to setup replication for the
> database server. I found two solutions:

> Slony ( http://gborg.postgresql.org/project/slony1/projdisplay.php )
> PGCluster ( http://pgfoundry.org/projects/pgcluster )

> I found that PgCluster supports multi-muster mode, which we can
> benefit from, but it's not required for "backup" which is #1 goal at
> the moment.

> If anyone used those solutions, compared performance, reliability, etc
> - please share your experience / thoughts.

i think you need to be more specific about your replication requirements.

async multi master is problematic in any case. it can be useful in certain
circumstances, but for generically duplicating a large database, it's generally
the wrong answer.

you should probably focus on single master/multi slave setups, there are
useful solutions in that space.

richard
--
Richard Welty                                         rwelty@averillpark.net
Averill Park Networking
    Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security
             "F=ma : it's not just a good idea, it's the law"

the number one aim at the moment is to have "always-up-to-date" copy
of our main DB with minial performance impact on replication (as I
guess, single master - slave setup will work the best in this case).
Eventually I it's likely that we'll want to unload the database server
by splitting requests between two+ servers, and in this case having
multi-master setup will be more convenient to have, I think.

so at the point any of slony and pgcluster works for me, but before I
start messing with any, I wanted to hear real users opninon about
those (or different) packages :)


> i think you need to be more specific about your replication requirements.
>
> async multi master is problematic in any case. it can be useful in certain
> circumstances, but for generically duplicating a large database, it's generally
> the wrong answer.
>
> you should probably focus on single master/multi slave setups, there are
> useful solutions in that space.
>
> richard
> --
> Richard Welty                                         rwelty@averillpark.net
> Averill Park Networking
>     Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security
>              "F=ma : it's not just a good idea, it's the law"
>

--
Vlad

Vlad wrote:
> Hello,
>
> in need to increase reliability of the service (and perhaps eventually
> offload main DB server) we are looking to setup replication for the
> database server. I found two solutions:
>
> Slony ( http://gborg.postgresql.org/project/slony1/projdisplay.php )
> PGCluster ( http://pgfoundry.org/projects/pgcluster )
>
> I found that PgCluster supports multi-muster mode, which we can
> benefit from, but it's not required for "backup" which is #1 goal at
> the moment.
>
> If anyone used those solutions, compared performance, reliability, etc
> - please share your experience / thoughts.
>
There is a 3rd option - DBMirror, which comes as part of the Postgres
distribution (look in the contrib/dbmirror directory).

I looked at Slony, which seems to be a current favourite -but I couldn't
get it working on my database (claimed my tables didn't have relevant
keys - which they do). Slony-I had almost non-existent documentation
which I always find prety unacceptable unless everything goes very
smoothly..

I then found DBMirror - which I'm now using to replicate replicate from
master to slave database, and then cascade replicating my slave to a 3rd
off-site server.

Performance of DBMirror.pl (a Perl script that does the actual
replication) was very poor - so I re-wrote it in C++. Some details at:
     http://www.whitebeam.org/library/guide/TechNotes/replicate.rhtm

DBmirror doesn't replicate schema changes, which wasn't a problem for me
since our schema changes very infrequently.

Hope that helps
Pete
--
http://www.yellowhawk.co.uk
http://www.whitebeam.org
-----

On Wed, May 04, 2005 at 10:06:03PM -0400, Vlad wrote:
> so at the point any of slony and pgcluster works for me, but before I
> start messing with any, I wanted to hear real users opninon about
> those (or different) packages :)

Well, Afilias (my employer) sponsored/sponsors Slony development.  We
use it for the .info and .org top level domains, among other systems.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
When my information changes, I alter my conclusions.  What do you do sir?
        --attr. John Maynard Keynes

Hello,

in need to increase reliability of the service (and perhaps eventually
offload main DB server) we are looking to setup replication for the
database server. I found two solutions:

Slony ( http://gborg.postgresql.org/project/slony1/projdisplay.php )
PGCluster ( http://pgfoundry.org/projects/pgcluster )

I found that PgCluster supports multi-muster mode, which we can
benefit from, but it's not required for "backup" which is #1 goal at
the moment.

If anyone used those solutions, compared performance, reliability, etc
- please share your experience / thoughts. 

Martha Stewart called it a Good Thing when Peter Wilson <petew@yellowhawk.co.uk> wrote:
> I looked at Slony, which seems to be a current favourite -but I
> couldn't get it working on my database (claimed my tables didn't have
> relevant keys - which they do). Slony-I had almost non-existent
> documentation which I always find prety unacceptable unless everything
> goes very smoothly..

I updated the copy of the documentation that I keep online (URL below)
to reflect the latest CVS updates just yesterday.  I have to say
"nonsense!"

There are things I would like to be better documented, but the notion
that the documentation is "almost nonexistent" is just nonsense.  And
the problem you describe is indeed discussed in the documentation.

Admittedly, it is not all in the version 1.0.5 tarball, but that's
because a lot of it was written after that release.
--
output = reverse("moc.liamg" "@" "enworbbc")
http://linuxdatabases.info/info/slony.html
Space Corps Directive #997: Work done  by an officer's doppleganger in
a parallel universe cannot be claimed as overtime.  -- Red Dwarf

Christopher Browne wrote:
> Martha Stewart called it a Good Thing when Peter Wilson <petew@yellowhawk.co.uk> wrote:
>
>>I looked at Slony, which seems to be a current favourite -but I
>>couldn't get it working on my database (claimed my tables didn't have
>>relevant keys - which they do). Slony-I had almost non-existent
>>documentation which I always find prety unacceptable unless everything
>>goes very smoothly..
>
>
> I updated the copy of the documentation that I keep online (URL below)
> to reflect the latest CVS updates just yesterday.  I have to say
> "nonsense!"
>
> There are things I would like to be better documented, but the notion
> that the documentation is "almost nonexistent" is just nonsense.  And
> the problem you describe is indeed discussed in the documentation.
>
> Admittedly, it is not all in the version 1.0.5 tarball, but that's
> because a lot of it was written after that release.

Nonsense? hmmmmmmmm

Without wanting to be hyper-critical, documentation that's hidden isn't
a whole lot of use. The link in your email to
    http://linuxdatabases.info/info/slony.html

does indeed seem to have some more documentation, but why isn't it
linked from the main Slony site
(http://gborg.postgresql.org/project/slony1)?

Why is this documentation on 'Christoper B. Browns homepage rather than
the Slony web pages? The 'official' Slony documentation I had available
was at :
    http://gborg.postgresql.org/project/slony1/genpage.php?howto_idx

and it *really* didn't help with the problems I had.

Having now taken a look at the documentation you reference, it's still
not wonderfully comprehensive. The problem I had was that despite the
fact that my tables had primary keys the Slony configuration refused to
recognise them. The documentation says simply that primary or candidate
primary keys are a requirements.

So - as a potential 'slony user' it is *not* nonsense that there was
negligable documentation - from my perspective it was a fact and I found
an alternative solution.

Pete

On Wed, 2005-05-04 at 21:06, Vlad wrote:
> the number one aim at the moment is to have "always-up-to-date" copy
> of our main DB with minial performance impact on replication

Have you considered point in time recovery as implemented in 8.0?

While it does require some small amount of work to bring the secondary
server up to date, it's not that much, and it gives you very up to date
recoverability.

Centuries ago, Nostradamus foresaw when smarlowe@g2switchworks.com (Scott Marlowe) would write:
> On Wed, 2005-05-04 at 21:06, Vlad wrote:
>> the number one aim at the moment is to have "always-up-to-date" copy
>> of our main DB with minial performance impact on replication
>
> Have you considered point in time recovery as implemented in 8.0?
>
> While it does require some small amount of work to bring the secondary
> server up to date, it's not that much, and it gives you very up to date
> recoverability.

If they want to be able to use the replica, perhaps to run reports on
it, that turns out to be problematic.
--
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://linuxfinances.info/info/spreadsheets.html
Long computations which yield zero are probably all for naught.

On Thu, May 05, 2005 at 03:21:40PM +0100, Peter Wilson wrote:
> Why is this documentation on 'Christoper B. Browns homepage rather than
> the Slony web pages? The 'official' Slony documentation I had available
> was at :
>     http://gborg.postgresql.org/project/slony1/genpage.php?howto_idx
>
> and it *really* didn't help with the problems I had.

I think it's already been acknowledged that the 1.0.5 release's
documentation wasn't as comprehensive as people liked; but that is,
as has also been noted, because nobody had run into the problems you
had yet (i.e. the documentation was as complete at release time as
the users had been able to make it).  Nevertheless, it is not true
that the documentation isn't on the slony pages.  By my count,
including the header bar, the "admin guide" is the 10th link on the
page.  It's near the top.  These updated docs are also in the
currently-beta version of the software.  (I don't want to get into a
flamewar over this; I just think it's unfair to claim the docs aren't
there.)

> Having now taken a look at the documentation you reference, it's still
> not wonderfully comprehensive. The problem I had was that despite the
> fact that my tables had primary keys the Slony configuration refused to
> recognise them. The documentation says simply that primary or candidate
> primary keys are a requirements.

This is also not true.  The documentation for SET ADD TABLE tells you
that there is a KEY option to tell slonik what the key field is.
Automatic detection won't always work, because not all "primary keys"
are so noted in a PostgreSQL database.

What is worth mentioning, though, is that Slony is not as simple to
set up as some of the alternatives, like dbmirror.  This is because
items like controlled switchover, failover, cascading, target
promotion, and log shipping are all designed-in features of Slony.
The same is not true of dbmirror, or erserver.  My experience tells
me that such features are very hard to add in retrospect.  Indeed,
the very reason Afilias sponsored development of Slony was that we
concluded the features we wanted could be more easily built from the
ground up than added to any of the alternatives we then had
available.  (This isn't to say everybody should use Slony; just that
it's important to realise that additional features come at the cost
of additional complexity.  My bet is that anyone really needing high
availability will end up needing some of those additional features.)

A


--
Andrew Sullivan  | ajs@crankycanuck.ca
When my information changes, I alter my conclusions.  What do you do sir?
        --attr. John Maynard Keynes