If on all nodes (both master and slaves), you did a fresh backup of the node using
pg_dump, here is the script that you need to apply on each node, just after
pg_restore. I use this script due to a bug in "repair config()" in Slony 1.2.X. So I use this script instead of using instruction "repair config()".
1. To do
Backup all nodes
- On each node, stop the Slony processes. This step is mandatory else you could have bad lock on sl_event while pg_dump is running and Slony too.
- On each node, make a dump
pg_dump -Fc -f your_dump_of_node.dmp -hlocalhost -v -p???? -U???? yourDB
Restore all nodes
- On each node, restore from the dump using pg_restore
dropdb yourDB
-- createdb -p??? -U??? -hlocalhost yourDB
pg_restore -d yourDB -p??? -U??? -v your_dump_of_node.dmp
- Copy the content of the script on each node. Apply the script
chmod +x script.sh
./script.sh | psql -hlocalhost -U??? -p??? yourDB
- On each node, restart Slony processes.
2. Content of the script
# This script is a updated version done by Darcy Buskermolen
# Original script see http://lists.slony.info/pipermail/slony1-general/2005-February/001631.html
#
# The name of your Slony cluster
CLUSTERNAME="replication";
# Using 'cat | sed ', the SQL queries will be printed out on stdout. You can redirect it to psql ( | psql ....) or to a SQL file ' > your_file.sq
#
cat << _EOF_ | sed -e "s:CLUSTERNAME:${CLUSTERNAME}:g"
BEGIN TRANSACTION;
DROP TABLE IF EXISTS "_CLUSTERNAME".repair_config_tab;
DROP TABLE IF EXISTS "_CLUSTERNAME".repair_config_seq;
CREATE TABLE
_CLUSTERNAME.repair_config_tab
AS SELECT
rct.tab_reloid as reloid,
rct.tab_relname,
rct.tab_nspname
FROM
_CLUSTERNAME.sl_table rct,
pg_catalog.pg_class pgc,
pg_catalog.pg_namespace pgn
WHERE
pg_catalog.quote_ident(rct.tab_relname)=pg_catalog.quote_ident(pgc.relname)
AND
pg_catalog.quote_ident(rct.tab_nspname)=pg_catalog.quote_ident(pgn.nspname)
AND pgc.relnamespace=pgn.oid;
CREATE TABLE
_CLUSTERNAME.repair_config_seq
AS SELECT
rcs.seq_reloid as reloid,
rcs.seq_relname,
rcs.seq_nspname
FROM
"_CLUSTERNAME".sl_sequence rcs,
pg_catalog.pg_class pgc,
pg_catalog.pg_namespace pgn
WHERE
pg_catalog.quote_ident(rcs.seq_relname)=pg_catalog.quote_ident(pgc.relname)
AND
pg_catalog.quote_ident(rcs.seq_nspname)=pg_catalog.quote_ident(pgn.nspname)
AND pgc.relnamespace = pgn.oid
;
UPDATE "_CLUSTERNAME".sl_table
SET
tab_reloid = pgc.oid
FROM
"_CLUSTERNAME".repair_config_tab rct,
pg_catalog.pg_class pgc,
pg_catalog.pg_namespace pgn
WHERE
pg_catalog.quote_ident(rct.tab_relname)=pg_catalog.quote_ident(pgc.relname)
AND
pg_catalog.quote_ident(rct.tab_nspname)=pg_catalog.quote_ident(pgn.nspname)
AND pgc.relnamespace = pgn.oid
AND rct.reloid = tab_reloid;
UPDATE "_CLUSTERNAME".sl_sequence
SET
seq_reloid = pgc.oid
FROM
"_CLUSTERNAME".repair_config_seq rcs,
pg_catalog.pg_class pgc,
pg_catalog.pg_namespace pgn
WHERE
pg_catalog.quote_ident(rcs.seq_relname)=pg_catalog.quote_ident(pgc.relname)
AND
pg_catalog.quote_ident(rcs.seq_nspname)=pg_catalog.quote_ident(pgn.nspname)
AND pgc.relnamespace = pgn.oid
AND rcs.reloid = seq_reloid
;
Tonight it's Christmas! As far as I remembered while I was listening Relax - done by Mika -, my mind went out(...) To my family, to my friends, I was thinking a couple hours ago! At 06:00 PM, I went to the church - not away from where I live -. I dedicated a prayer to my famlily and my friends. I called my family (Reunion Island) in Indian Ocean. I was very glad to listen my mother and my sister on phone. Dad was a little tired. Happy I was to call with him 
even if I was not with them.
