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

[Nikhil S. <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