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

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

My current setup:

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

   - 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
3. Referene

More: http://postgres-xc.sourceforge.net/docs/1_1_beta/upgrading.html

2013/7/16 Nikhil Sontakke <ni...@st...>

>
> OK so all i need is to use pg_dumpall in psql in any of the coordinators
>> (this will store my DDL in the file).
>> When I want to restore it is just like to connect to anyy of the
>> coordinator, execute psql and load the DDL file using -f option or \i in
>> interactive mode?
>>
>> Yeah. That should work.
>
> Regards,
> Nikhils
>
>
>>
>> 2013/7/16 Ashutosh Bapat <ash...@en...>
>>
>>> Hi Adam,
>>> You do not need to follow these steps, if you are configuring your
>>> cluster the first time. You may follow installation steps there.
>>>
>>>
>>> On Mon, Jul 15, 2013 at 5:44 PM, Adam Dec <ada...@gm...> wrote:
>>>
>>>>  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
>>>>
>>>>
>>>
>>>
>>> --
>>> Best Wishes,
>>> Ashutosh Bapat
>>> EntepriseDB Corporation
>>> The Postgres Database Company
>>>
>>
>>
>>
>> ------------------------------------------------------------------------------
>> 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
>>
>>
>
>
> --
> StormDB - http://www.stormdb.com
> The Database Cloud