Menu
▾
▴
postgres-xc-developers
|
From: Adam D. <ada...@gm...> - 2013-07-15 12:14:41
|
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 & |
|
From: Koichi S. <koi...@gm...> - 2013-07-16 04:52:38
|
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 > > |
|
From: Ashutosh B. <ash...@en...> - 2013-07-16 05:20:09
|
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 |
|
From: Adam D. <ada...@gm...> - 2013-07-16 05:44:23
|
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? 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 > |
|
From: Nikhil S. <ni...@st...> - 2013-07-16 15:37:16
|
> 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 |
|
From: Adam D. <ada...@gm...> - 2013-07-16 15:08:57
|
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 |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X