Re: [GENERAL] Replication of databases (esp. postgres)

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(at)spy(dot)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. __