Hi,
I have a classical (but beautiful) school case to submit to the community.
We have a slony1 (1.2.13) replication between 10 PGnodes (8.3).
For performance reasons, we have to add columns in our biggest tables
(approximatively 10 millions of rows).
On the finest hardware of these nodes, it takes a few hours.
Because of production constraints, the challenge is to minimize the time of
this operation, not to use slony's execute_script command, which first alter
the master and then replicates the schema change to the others nodes.
So we planned to alter the table manually on each nodes in parallel by :

   1. down write access
   2. down slony
   3. then in a same transaction :
   1. save the sequence of a column (select nextval)
      2. altertablerestore(tab_id) to deactivate slony triggers, restore
      tables in initial state and permit the ddl change onto the replicated
      databases
      3. create the new table table_new by select into (+ alter table alter
      column set not null)
      4. drop original table
      5. alter table table_new rename to table
      6. alter table table inherit
      7. create column's sequence
      8. alter table add constraint & index
      9. create table's trigger
      10. reinject sequence (select setval)
      11. update manually sl_table and sl_sequence with the new oid of the
      recreated objects
      12. altertableforreplication(tab_id) to replicate the table
   4. VACUUM ANALYZE table

In a dev environnement, datas after schema change are replicated with no
error, so it appears to run successfully.
But I ask for your knowledge : if anyone experienced altertablerestore /
altertableforreplication manually in similar conditions, does this plan
appears to be correct ?

Many thanx for your interest

J=E9r=F4me Jouanin
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-hackers/attachments/20080716/=
a04e1800/attachment-0001.htm