Thread: Re: pgsql-general-digest V1 #529

Re: pgsql-general-digest V1 #529

From
Manuel Cabido
Date:
Sir:

   I would like to inquire if with PostgreSQL 6.5.3.x I can put the same
database on two or more different Linux server such that each update will
be replicated on all database backends...

                              Manny C. Cabido
                              ====================================
                              e-mail:manny@tinago.msuiit.edu.ph
                                     manny@sun.msuiit.edu.ph
                              =====================================



Re: replication

From
"Aaron J. Seigo"
Date:
hi...

>    I would like to inquire if with PostgreSQL 6.5.3.x I can put the same
> database on two or more different Linux server such that each update will
> be replicated on all database backends...
>

this is on the to-do list under "Exotic Features". it seems to be one of the
more asked about features that doesn't exist at the moment on the General list.
unfortunately, it isn't a trivial project so it won't happen over night...

right now, you can jury rig your own system up using network file systems,
periodic updates of local filesystems, etc... this is limited in
usefulness...

true replication does not exist (ala Oracle or MSJet)... yet.

--
Aaron J. Seigo
Sys Admin

Re: [GENERAL] Re: replication

From
Jeff Hoffmann
Date:
"Aaron J. Seigo" wrote:

> >    I would like to inquire if with PostgreSQL 6.5.3.x I can put the same
> > database on two or more different Linux server such that each update will
> > be replicated on all database backends...
>
> right now, you can jury rig your own system up using network file systems,
> periodic updates of local filesystems, etc... this is limited in
> usefulness...

my favorite hack is to wrap the c/c++ api so you can set up a group of
mirror database connections and then calling the wrapped functions will
call the original function on each of the mirror database connections.
i've really never thought of it as something that should be part of the
library, though, since that's pretty easy to implement in the
application.

Re: [GENERAL] Re: replication

From
"Aaron J. Seigo"
Date:
hi...

> my favorite hack is to wrap the c/c++ api so you can set up a group of
> mirror database connections and then calling the wrapped functions will
> call the original function on each of the mirror database connections.
> i've really never thought of it as something that should be part of the
> library, though, since that's pretty easy to implement in the
> application.

very cool... but i too agree that this shouldn't necessarily be in the
libraries...

this needs to be in the back end... otherwise, if you have multiple people
performing updates on different replicated servers, how can you guarentee
concurancy? how do you manage the differences between read-only and updateable
replicants? (this can be done using your wrapper method, but quickly gets
clumsy)

also, it would make maintaining replication simpler if it were part of the
back end process... and not prone to some app that goes around your wrapped lib
and defeats the replication scheme altogether... imo, it should be part of the
transaction system.... =)

--
Aaron J. Seigo
Sys Admin

Re: [GENERAL] Re: replication

From
Jeff Hoffmann
Date:
"Aaron J. Seigo" wrote:

> this needs to be in the back end... otherwise, if you have multiple people
> performing updates on different replicated servers, how can you guarentee
> concurancy? how do you manage the differences between read-only and updateable
> replicants? (this can be done using your wrapper method, but quickly gets
> clumsy)

yeah it can get clumsy, but fortunately updates are pretty rare in my
case (and usually only performed by one person) so i don't really worry
about those issues, but i should.  the only safety issue i use is
starting a transaction for each connection, perform the action on all of
the databases, and then commit the transaction on each.  it's not bad
unless one of the commits fails and the others don't, then i have to
retry the failed ones & hope it works.  i don't know how to fix it if i
had to give up on one of the transactions for a mirror.  again, not
perfect, but it works pretty well for me & my simpler cases.