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

[Koichi S. <koi...@gm...>]

You don't need to issue "select pgxc_lock_for_backup();".  This function
blocks DDL and is used when you add new coordinator/datanode dynamically.
You don't have to restart coordinator/datanode.   When you issue
pg_dump/pg_dumpall, you just call it from any coordinator.  You don't have
to stop coordinator/datanode.

This is a logical database backup.

Regards;

----------
Koichi Suzuki


2013/7/15 Adam Dec <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 &
>
>
>
>
> ------------------------------------------------------------------------------
> See everything from the browser to the database with AppDynamics
> Get end-to-end visibility with application monitoring from AppDynamics
> Isolate bottlenecks and diagnose root cause in seconds.
> Start your free trial of AppDynamics Pro today!
> http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>
>