Thread: Replication Solutions for PostgreSQL Master to Slave

Replication Solutions for PostgreSQL Master to Slave

From
Kirby Ubben
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


We have installed a product called Quasar for accounting needs at our
company, which is back ended by PostgreSQL. It is a requirement that
the database is replicated, real time, to a secondary machine and
PostgreSQL database, in case of failure of the first.

Currently i have Slony working, but am not satisfied with how it
accomplishes replication, or it's interface, and am curious what
others are using to accomplish replication?

Thanks in advance,

k.

- --
Kirby Ubben
Systems Engineer
McMurray Hatchery
kirby@mcmurrayhatchery.com

1 515 832 1235 ext 201
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFDWRc0thkzRAQDSgERAhSrAJ9Ax5nwDMlOUiRw8jcczmU7PJo8LgCeMf3a
gIFKb7oH7+4zuAEZyQYowh0=
=CIAC
-----END PGP SIGNATURE-----


Re: Replication Solutions for PostgreSQL Master to Slave

From
"Jim C. Nasby"
Date:
Unfortunately, the only syncronous replication I know of is
http://pgfoundry.org/projects/pgcluster/, which is statement-based. That
means it's got some substantial drawbacks...

On Fri, Oct 21, 2005 at 11:28:36AM -0500, Kirby Ubben wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> We have installed a product called Quasar for accounting needs at our
> company, which is back ended by PostgreSQL. It is a requirement that
> the database is replicated, real time, to a secondary machine and
> PostgreSQL database, in case of failure of the first.
>
> Currently i have Slony working, but am not satisfied with how it
> accomplishes replication, or it's interface, and am curious what
> others are using to accomplish replication?
>
> Thanks in advance,
>
> k.
>
> - --
> Kirby Ubben
> Systems Engineer
> McMurray Hatchery
> kirby@mcmurrayhatchery.com
>
> 1 515 832 1235 ext 201
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (MingW32)
> Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
>
> iD8DBQFDWRc0thkzRAQDSgERAhSrAJ9Ax5nwDMlOUiRw8jcczmU7PJo8LgCeMf3a
> gIFKb7oH7+4zuAEZyQYowh0=
> =CIAC
> -----END PGP SIGNATURE-----
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: Replication Solutions for PostgreSQL Master to Slave

From
Andrew Sullivan
Date:
On Fri, Oct 21, 2005 at 11:28:36AM -0500, Kirby Ubben wrote:
> Currently i have Slony working, but am not satisfied with how it
> accomplishes replication, or it's interface, and am curious what
> others are using to accomplish replication?

What do you find unsatisfactory (or, for that matter,
dissatisfactory) about Slony?  I expect the developers would like to
know.

A


--
Andrew Sullivan  | ajs@crankycanuck.ca
A certain description of men are for getting out of debt, yet are
against all taxes for raising money to pay it off.
        --Alexander Hamilton

Re: Replication Solutions for PostgreSQL Master to Slave

From
Chris Travers
Date:
Kirby Ubben wrote:

>-----BEGIN PGP SIGNED MESSAGE-----
>Hash: SHA1
>
>
>We have installed a product called Quasar for accounting needs at our
>company, which is back ended by PostgreSQL. It is a requirement that
>the database is replicated, real time, to a secondary machine and
>PostgreSQL database, in case of failure of the first.
>
>
You are looking for synchronous replication as opposed to async?

>Currently i have Slony working, but am not satisfied with how it
>accomplishes replication, or it's interface, and am curious what
>others are using to accomplish replication?
>
>
Well, the two main projects that people are using are:
Slony-I (Async, master/slave)
PgPool (sync, load balancing possibilities)

Best Wishes,
Chris Travers
Metatron Technology COnsulting

Attachment

Re: Replication Solutions for PostgreSQL Master to Slave

From
"Joshua D. Drake"
Date:
> You are looking for synchronous replication as opposed to async?
>
> >Currently i have Slony working, but am not satisfied with how it
> >accomplishes replication, or it's interface, and am curious what
> >others are using to accomplish replication?

There is also Mammoth Replicator:

http://www.commandprompt.com/products/mammothreplicator

It is a commercial product (I work for CMD) but it is widely used
in the corporate environment and you may find it a little easier
to manage.

Sincerely,

Joshua D. Drake


--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/


Re: Replication Solutions for PostgreSQL Master to Slave

From
Chris Browne
Date:
jd@commandprompt.com ("Joshua D. Drake") writes:
>> You are looking for synchronous replication as opposed to async?
>>
>> >Currently i have Slony working, but am not satisfied with how it
>> >accomplishes replication, or it's interface, and am curious what
>> >others are using to accomplish replication?
>
> There is also Mammoth Replicator:
>
> http://www.commandprompt.com/products/mammothreplicator
>
> It is a commercial product (I work for CMD) but it is widely used
> in the corporate environment and you may find it a little easier
> to manage.

A vital difference is that Slony-I extracts replication information
(e.g. - determines what it needs to replicate) via triggers on the
tables, whereas Mammoth Replicator takes the (quite common in the
industry) approach of reading update information out of the
transaction logs.

There are a number of differences between the approaches, but in the
conversations with people at OSCON, it seemed surprisingly common for
the similarities to make it seem that when Slony-I was inadequate,
Mammoth Replicator would be too.

Of course, this particular discussion hasn't extracted enough
information about the dissatisfaction to evaluate much of anything...
--
output = ("cbbrowne" "@" "cbbrowne.com")
http://cbbrowne.com/info/spiritual.html
There was a young lady of Crewe
Whose limericks stopped at line two.

Re: Replication Solutions for PostgreSQL Master to Slave

From
Kirby Ubben
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Chris Browne wrote:

>jd@commandprompt.com ("Joshua D. Drake") writes:
>
>>>You are looking for synchronous replication as opposed to async?
>>>
>>>>Currently i have Slony working, but am not satisfied with how it
>>>>accomplishes replication, or it's interface, and am curious what
>>>>others are using to accomplish replication?
>>
>>There is also Mammoth Replicator:
>>
>>http://www.commandprompt.com/products/mammothreplicator
>>
>>It is a commercial product (I work for CMD) but it is widely used
>>in the corporate environment and you may find it a little easier
>>to manage.
>
>
>A vital difference is that Slony-I extracts replication information
>(e.g. - determines what it needs to replicate) via triggers on the
>tables, whereas Mammoth Replicator takes the (quite common in the
>industry) approach of reading update information out of the
>transaction logs.
>
>There are a number of differences between the approaches, but in the
>conversations with people at OSCON, it seemed surprisingly common for
>the similarities to make it seem that when Slony-I was inadequate,
>Mammoth Replicator would be too.
>
>Of course, this particular discussion hasn't extracted enough
>information about the dissatisfaction to evaluate much of anything...

This is all true. The issues with slony are, as you guessed correctly,
are based around the triggers, and relatively cumbersome
administration overhead for a high number of tables, and dynamic table
creation.

The application will indeed come to a more stable database scheme in
the future, but as it is right now, if each table added needs to have
triggers in place to include it in replication, i have alot of work to
deal with.

It is very possible pgcluster may be a better solution, and i am
currently setting it up on a test machine.

I am also limited in what solutions i can use based on platform. for
whatever reasons, i need to be able to use the replication solution on
two intel SuSE 9.0 Professional machines.  I was told Mammoth wasn't
available for this version of SuSE. (by the install script. ;p)

Any and all advice is appreciated.


- --
Kirby Ubben
Systems Engineer
McMurray Hatchery
kirby@mcmurrayhatchery.com

1 515 832 1235 ext 201
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFDWWl3thkzRAQDSgERAi+8AJ4hcoI1ytDzzFx5YmcQylbDZsLBqwCeKxr1
43fQvYdxXMyh7MT6nWp+pxg=
=JQad
-----END PGP SIGNATURE-----