Skip site navigation (1) Skip section navigation (2)

dbmirror - migration to 8.3 from 7.4

From: Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: dbmirror - migration to 8.3 from 7.4
Date: 2008-05-07 11:42:25
Message-ID: 200805071442.25378.achill@matrix.gatewaynet.com (view raw or flat)
Thread:
Lists: pgsql-sql
Hi,
we have been running our own heavily modified/enhanced version of dbmirror, running on 7.4 for some years, 
and now it is the time to upgrade to 8.3.
We have called our approach "Conditional row grained + FK dependency oriented lazy replication", that is,
any FK dependencies of a row are tranfered only when needed, and each remote slave has a subset of the
master DB. This is applied to a uucp network of postgresql installations that communicate over satelite
dialup connections. That is why we cannot follow any officially supported replication platform.
Now back to my issue,
In the code, i do some SELECTs from the pg_catalog.pg_index, pg_catalog.pg_constraint c,pg_catalog.pg_class,
pg_catalog.pg_attribute and i would like to have your opinion on wether some semantics have changed or
added to the new pg_catalog tables.

The way i find the primary key of a table is:
	SELECT indkey FROM pg_index WHERE indisprimary='t' AND indrelid=TABLEOID;
i noticed that some columns have been added to pg_index : indisvalid, indcheckxmin, indisready,indoption
Should i include any of them (e.g. indisvalid) in the where clause above?

The way i find the FK of a table is:
	SELECT c.confrelid,c.conkey,c.confkey,f.relname FROM pg_catalog.pg_constraint c,pg_catalog.pg_class f 
		WHERE c.contype = 'f' AND c.confrelid = f.oid AND c.conrelid= TABLEOID;
I noticed that some columns have been added to pg_constraint: conpfeqop,conppeqop,conffeqop
Should i change something to the above query?

Finally, the way i find the name of a column is:
	SELECT attname FROM pg_attribute WHERE attrelid=TABLEOID and attnum=ATTNUM;

Also, i had to change any int2vector code, since now int2vector are implemented like varlenas,
and also i had to add PG_DETOAST_DATUM on any array Datum.

The code seems to work however i'd like your comments if i miss something.
Thanks a lot.
-- 
Achilleas Mantzios

Responses

pgsql-sql by date

Next:From: Claus GuttesenDate: 2008-05-07 11:49:31
Subject: Re: dbmirror - migration to 8.3 from 7.4
Previous:From: Sid 'Neko Tamashii'Date: 2008-05-06 12:33:48
Subject: Re: Database normalization

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group