Thread: Replication of databases (esp. postgres)

Hello altogether,


i'm breeding on a sensible concept to replicate a database
in a heterogenous environment while keeping the relations intact.

Given situation:

Database 1


Database 2

                                        MASTER
Database 3


...


Database n


Every database replicates itself to MASTER which renumbers the
relations and which replicates the data to the rest of the databases.

All databases must have identical data. All databases have
tables with relations on unique indices. All databases are
granted i/u/d permissions on itself.

So any database may fire a trigger to insert, update or to delete
a record on any other database.

The shown scheme has the disadvantage to have two passes. One Transfer
of data from a slave to a master and one acknowledgement with corrected
relations from the master to the sending slave and replication traffic
to the other, receiving slaves.

Does anybody know how to implement another replication scheme?

Are there any info pointers on readings about replication of databases?

Are there any "ready to go out of the box" tools for postgres?


Thank you in advance.

t++

On Mon, 15 Feb 1999, Thomas Antepoth wrote:

// Every database replicates itself to MASTER which renumbers the
// relations and which replicates the data to the rest of the
// databases.

    Just one note.  Renumbering isn't all that important.  I did one
that had a non-numeric unique ID for identity type columns.  I got the
idea from one of our DBAs who's doing the same kinda thing with Sybase.
The main difference between Postgres and Sybase in this context is that
Sybase has a replication agent already.

    Anyway, what I did was create a table of hostnames with a flag
that said, ``me.''  Then a numeric sequence, and combined the two to make
an ID unique to the host.  You just have to not replicate the sequence.
I'm not really sure what to do about the db_host table.  It needs to be
replicated, but you don't want them all to say true.  :)  Here's the
schema I did:

create sequence db_host_seq;

create table db_host(
        host_key integer default nextval('db_host_seq'),
        host_name text,
        isme bool
);

create function hostname() returns text as
        'select host_name from db_host where isme=\'t\''
        language 'sql';

create function unique_id() returns text as
        'select ((hostname()) || (''.''::text))
                  || (nextval(''unique_id'')::text)'
        language 'sql';


--
Principal Member Technical Staff, beyond.com    The world is watching America,
pub  1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
|    Key fingerprint =  87 02 57 08 02 D0 DA D6  C8 0F 3E 65 51 98 D8 BE
L______________________________________________ and America is watching TV. __

Dustin,


thank you for your answer.

On Mon, 15 Feb 1999, dustin sallings wrote:

> // {Replication of r/w Databases in a two pass scheme w/ renumbering}
>     Just one note.  Renumbering isn't all that important.  I did one
> that had a non-numeric unique ID for identity type columns.

I thought of a field named db_id. This field combined with
a unique (at least to the local database) counter should give a
unique index to each relation.


The rule to follow is: Every record in a relation with the
same db_id and the same rec_id belongs together.


But think of this situation:

DB 1 gets a relation.

table1        table2
record1       record1
              record2

Now this relation gets replicated to DB 2.

After this is done, DB 2 adds a record3 to
table2 this relation.

Question: Which db_id should DB 2 use?

His own? In this way record3 wouldn't belong
to this relation.

The id of DB 1? In this way the data may
not be unique to DB 1 as DB 1 might enter
the same values for record3 for a completely
other relation and a collision is created.

So i dropped the idea of having a db_id field
to identify the record of a relation w/o
renumbering the relations by a master.


have a nice day! t++