Thread: Database replication... - Mission Critical DBMS's -- Taking postgreSQL to the Enterprise Computing level...

I have a client that I am developing a web-based business application for
using Perl / PHP, DHTML, and postgreSQL on a FreeBSD server with Apache...

The client realized (or hopes) that this application may become mission
critical (to his clients / end-users) and was asking about the possibility
of having a replicated database using postgreSQL in case the database server
takes a dive (paranoia). He is somewhat familiar with MS Access (I gathered
he was thinking of the MS Access replication / front-end / back-end /
synchronized database concept).

I have read through all 7 chapters of the PG documentation and didn't see
anything about replicating a postgreSQL database on a secondary database
server and having the primary server push all data manipulations on through
to the secondary server. The secondary server would be available (through
the connect script) if the primary server died.

Does anyone know of a reasonable means of synchronizing two (or more)
postgreSQL database servers in order to maintain two COMPLETE concurrent
copies of the same database ?

Since I can already see REFINT problems in maintaining the integrity of
serial values, trigger problems, and stored procedure problems (UDF's),
perhaps I should just suggest a robust database backup and restore scheme
with a standby postgreSQL server ready to accept a pgdumpall.

Although the documentation claims that CREATE DATABASE is not intended as a
"copy database" utility, maybe a CRON event (say every 4 hours) on the
secondary server to:

DROP database 'backupdatabase';
CREATE DATABASE 'backupdatabase' WITH TEMPLATE = 'livedatabase';

would do the trick....

I am completely open to suggestions here...

TIA.

GP

On Tue, 2002-10-29 at 17:56, Greg Patnude wrote:
> Does anyone know of a reasonable means of synchronizing two (or more)
> postgreSQL database servers in order to maintain two COMPLETE concurrent
> copies of the same database ?

I'm not sure how complete it is (just starting to look at this myself)
but 'dbbalancer' at least makes some claims to this.  It acts as
(basically) a postgresql proxy server, and can send queries to multiple
servers.  My presumption would be that if you initialize 2 databases to
a known identical start, have all the same triggers and rules on both,
then send all queries to both databases, you will have 2 identical
databases at the end.

Don't know how well that will work in practice tho.  I should know more
in the next couple of weeks.

b.g.

You might want to check out
http://gborg.postgresql.org/genpage?replication_research which has
information and links to several replication solutions for postgresql.

the techdocs.postgresql.org website also has a good number of papers
regarding replication, as well as a "high availability how-to" that
would probably be worth reading.

Robert Treat

On Wed, 2002-10-30 at 09:24, Bill Gribble wrote:
> On Tue, 2002-10-29 at 17:56, Greg Patnude wrote:
> > Does anyone know of a reasonable means of synchronizing two (or more)
> > postgreSQL database servers in order to maintain two COMPLETE concurrent
> > copies of the same database ?
>
> I'm not sure how complete it is (just starting to look at this myself)
> but 'dbbalancer' at least makes some claims to this.  It acts as
> (basically) a postgresql proxy server, and can send queries to multiple
> servers.  My presumption would be that if you initialize 2 databases to
> a known identical start, have all the same triggers and rules on both,
> then send all queries to both databases, you will have 2 identical
> databases at the end.
>
> Don't know how well that will work in practice tho.  I should know more
> in the next couple of weeks.
>
> b.g.
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On Tue, Oct 29, 2002 at 03:56:46PM -0800, Greg Patnude wrote:

> I have read through all 7 chapters of the PG documentation and didn't see
> anything about replicating a postgreSQL database on a secondary database
> server and having the primary server push all data manipulations on through
> to the secondary server. The secondary server would be available (through
> the connect script) if the primary server died.
>
> Does anyone know of a reasonable means of synchronizing two (or more)
> postgreSQL database servers in order to maintain two COMPLETE concurrent
> copies of the same database ?

I think what you want is that any change, including schema changes,
&c., on the master database are echoed to the slave.  I know rserv
can't do that, and I don't _think_ dbmirror can, either.  But both of
those are possibilities.  AFAIK, the contrib/rserv code is completely
broken in the 7.3 series, so dbmirror might be the free answer to
pick; rserv has a commercial cousin which we use.

The problem with these is that they send _data_ to the slave, and use
standard SQL statements.  So sequences &c. are not carried over.  You
need a script to do that.  It's not an instantaneous failover.

On the other hand, if your client is comparing with Access, the
reliability will be so much better that perhaps some extra work in
the rare case of failure will be acceptable.  It is for us, and we
have extremely stringent SLAs which constrain how long we could be
down if (heaven forfend!) our master database ever died.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

On Wed, Oct 30, 2002 at 10:18:47AM -0500, Robert Treat wrote:
> regarding replication, as well as a "high availability how-to" that
> would probably be worth reading.

The high availability howto suggests using rsync to synchronise the
data areas of two data servers.  That is an _extremely bad_ idea.
I've suggested before that the link be removed, because it recommends
something almost guaranteed to introduce massive database corruption
at some point.  If there's no load and you have a fast network, you
might get lucky.  But it is an extremely dangerous plan.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

Andrew Sullivan wrote:
>
> On Wed, Oct 30, 2002 at 10:18:47AM -0500, Robert Treat wrote:
> > regarding replication, as well as a "high availability how-to" that
> > would probably be worth reading.
>
> The high availability howto suggests using rsync to synchronise the
> data areas of two data servers.  That is an _extremely bad_ idea.
> I've suggested before that the link be removed, because it recommends
> something almost guaranteed to introduce massive database corruption
> at some point.  If there's no load and you have a fast network, you
> might get lucky.  But it is an extremely dangerous plan.

Ok, have just removed the link.  Sorry for not getting around to it
before Andrew.

(Bruce pointed out your email, otherwise I would have missed it again
too).

:-/

Regards and best wishes,

Justin Clift


> A
>
> --
> ----
> Andrew Sullivan                         204-4141 Yonge Street
> Liberty RMS                           Toronto, Ontario Canada
> <andrew@libertyrms.info>                              M2P 2A8
>                                          +1 416 646 3304 x110
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi