Here is is some stuff that can help you to manage your (eventually replicated :-) databases.

Table restructure utility.

Permits to quick and easily drop and move table fields, optionally changing its data types.

Sometimes is useful drop some field from a table or change a field data type. Since not all ALTER TABLE variants are supported, you will find useful editschema utility.

Using editschema you can edit the schema of a table obtained by pg_dump, automatically importing old data into the new table.

1. Change directory to pgreplica/bin/utilities

2. Launch ./editchema <dbname> <tablename>

3. Edit your original table structure using vi (you can use any other editor instead). Here you can:
A) insert a new field: the new field will be create in the restructured table in the same position you put it.
B) delete an old field: the file will be dropped from tha table.
C) change a field type: the data type will be changed and data will be re-loaded using the new data type. Please note the new data type sould be able to cast from the old data type.
D) move fields: you can freely change the sequence of the fields whose are composing your table. The restructured table will have the the fields order. (Please have care of SQL syntax when moving fields: all the field lines should have a comma character at the end of line but the last one.)
E) change a field name. The field with old name will be dropped and will be created a new field with new name. If you want to simply rename a columns please use ALTER TABLE command. (Use carefully this option!)

4. Exiting from vi all your changes will be committed. If you want to skip your changes refresh the original file from disk (using :e!)

editschema need getdata.tcl script to work, so you have to copy both files if you want to use this utility elsewhere.

Sequences for distributed tables

How to store data coming from sequences in distributed / replicated tables without overlaps

Developing applications on distributed tables requires sequences that don't overlap among sites.
We are using a function which works on standard sequences and gives back data that should not overlap, and fits in a int8 data type. The MSB int4 store the site ID number (if siteid is not set up it is set to 0) and the LSB int4 store the real sequence.

Using it is simple, as you can see:
Form host id 1:
select * from pgr_lsite;
pgr_sid | 1
create sequence ('serials');
select nextval('serials');
nextval | 1
select nextval8('serials');
nextval8 | 4292967298

Form host id 2:
select * from pgr_lsite;
pgr_sid | 2
create sequence ('serials');
select nextval('serials');
nextval | 1
select nextval8('serials');
nextval8 | 8589934594

All you need is create a nextval8 function like this:

CREATE FUNCTION "nextval8" (varchar ) RETURNS int8 AS '
spi_exec "select count(relname) as table_exists from pg_class where relname = ''pgr_lsite''"
if { $table_exists == 0 } {
set lsid 0
} else {
set lsid 0
spi_exec "SELECT pgr_sid as lsid from pgr_lsite"
spi_exec "select int8(int8($lsid) * 4294967296::int8) + int8(nextval(''$1'')) as retvalue"
return $retvalue
' LANGUAGE 'pltcl';

Since nextval8 is written in Pl/Tcl you have to correctly install pgreplicator prior to use it.
It is possible to use nextval8 without installing pgreplicator if you manually install the pl/Tcl language with these commands:
CREATE FUNCTION "pltcl_call_handler" ( ) RETURNS opaque AS '/usr/lib/pgsql/' LANGUAGE 'C';
(the path of could change)