[Postgres-xc-developers] Create a cluster wide backup and restore it...

[Adam D. <ada...@gm...>]

Hi!
My topology is like this:
I have to machines. On each machine I have 1 master coordinator and 1
master datanode,

I have created a backup (only DDL as I read from here:
http://postgres-xc.sourceforge.net/docs/1_1_beta/add-node-datanode.html)
process, please advice  me if this does make any sense to you:

1. Creating backup

   - Connect to the any of the coordinators (lets say the first one):

./bin/psql -p 20011 -h 192.168.123.195 -d sts -U sts

   - Lock the cluster for backup, do not close this session

select pgxc_lock_for_backup();

More:
http://postgres-xc.sourceforge.net/docs/1_1_beta/functions-admin.html#FUNCTIONS-PGXC-ADD-NEW-NODE

   - Connect to the any of the coordinators and backup the data:

./pg_dumpall -p 20011 -s --include-nodes --dump-nodes --file=
/opt/backup/sts_ddl_backup.sql

Only schema (i.e. no data) is to be dumped. Note the use of
--include-nodes, so that the CREATE TABLE contains TO NODE clause.

Similarly --dump-nodes ensures that the dump does contain existing nodes
and node groups.
2. Loading backup

   - Stop all the coordinators:

./bin/pg_ctl stop -D /opt/postgres-xc-1.1/coord1/ -Z coordinator -l
/opt/postgres-xc-1.1/logs/coord1.log -mf &
./bin/pg_ctl stop -D /opt/postgres-xc-1.1/coord2/ -Z coordinator -l
/opt/postgres-xc-1.1/logs/coord2.log -mf &

   - Stop all the datanodes:

./bin/pg_ctl stop -D /opt/postgres-xc-1.1/datanode1/ -Z datanode -l
/opt/postgres-xc-1.1/logs/datanode1.log -mf &
./bin/pg_ctl stop -D /opt/postgres-xc-1.1/datanode2/ -Z datanode -l
/opt/postgres-xc-1.1/logs/datanode2.log -mf &

   - Start the coordinators in restore mode:

./bin/pg_ctl start -Z restoremode -D /opt/postgres-xc-1.1/coord1/ -l
/opt/postgres-xc-1.1/logs/coord1.log &
./bin/pg_ctl start -Z restoremode -D /opt/postgres-xc-1.1/coord2/ -l
/opt/postgres-xc-1.1/logs/coord2.log &

   - Start the datanodes in restore mode:

./bin/pg_ctl start -Z restoremode -D /opt/postgres-xc-1.1/datanode1/ -l
/opt/postgres-xc-1.1/logs/datanode1.log &
./bin/pg_ctl start -Z restoremode -D /opt/postgres-xc-1.1/datanode2/ -l
/opt/postgres-xc-1.1/logs/datanode2.log &

   - Connect to the any of the coordinators (lets say the first one):

./bin/psql -p 20011 -h 192.168.123.195 -d sts -U sts

   - Restore DDL data:

./bin/psql -p 20011 -h 192.168.123.195 -d sts -U sts -f
/opt/backup/sts_ddl_backup.sql

   - Stop the coordinators ( this will unlock the cluster -> select
   pgxc_lock_for_backup(); ):

./bin/pg_ctl stop -D /opt/postgres-xc-1.1/coord1/ -Z coordinator -l
/opt/postgres-xc-1.1/logs/coord1.log -mf &
./bin/pg_ctl stop -D /opt/postgres-xc-1.1/coord2/ -Z coordinator -l
/opt/postgres-xc-1.1/logs/coord2.log -mf &

   - Stop the datanodes

./bin/pg_ctl stop -D /opt/postgres-xc-1.1/datanode1/ -Z datanode -l
/opt/postgres-xc-1.1/logs/datanode1.log -mf &
./bin/pg_ctl stop -D /opt/postgres-xc-1.1/datanode2/ -Z datanode -l
/opt/postgres-xc-1.1/logs/datanode2.log -mf &

   - Start the coordinators:

./bin/pg_ctl start -D /opt/postgres-xc-1.1/coord1/ -Z coordinator -l
/opt/postgres-xc-1.1/logs/coord1.log &
./bin/pg_ctl start -D /opt/postgres-xc-1.1/coord2/ -Z coordinator -l
/opt/postgres-xc-1.1/logs/coord2.log &

   - Start the datanodes:

./bin/pg_ctl start -D /opt/postgres-xc-1.1/datanode1/ -Z datanode -l
/opt/postgres-xc-1.1/logs/datanode1.log &
./bin/pg_ctl start -D /opt/postgres-xc-1.1/datanode2/ -Z datanode -l
/opt/postgres-xc-1.1/logs/datanode2.log &