[Postgres-xc-developers] Add a new node to the cluster

[Abbas B. <abb...@en...>]

Hi All,

Attached please find a patch that provides a new command line argument for
postgres called --restoremode.

While adding a new node to the cluster we need to restore the schema of
existing database to the new node.
If the new node is a datanode and we connect directly to it, it does not
allow DDL, because it is in read only mode &
If the new node is a coordinator, it will send DDLs to all the other
coordinators which we do not want it to do.

To provide ability to restore on the new node a new command line argument
is provided.
It is to be provided in place of --coordinator OR --datanode.
In restore mode both coordinator and datanode are internally treated as a
datanode.
For more details see patch comments.

After this patch one can add a new node to the cluster.

Here are the steps to add a new coordinator


 1) Initdb new coordinator
        /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data_cord3
--nodename coord_3

 2) Make necessary changes in its postgresql.conf, in particular specify
new coordinator name and pooler port

 3) Connect to any of the existing coordinators & lock the cluster for
backup
        ./psql postgres -p 5432
        SET xc_lock_for_backup=yes;
        \q

 4) Connect to any of the existing coordinators and take backup of the
database
        ./pg_dump -p 5432 -C -s
--file=/home/edb/Desktop/NodeAddition/dumps/101_all_objects_coord.sql test

 5) Start the new coordinator specify --restoremode while starting the
coordinator
        ./postgres --restoremode -D ../data_cord3 -p 5455

 6) connect to the new coordinator directly
        ./psql postgres -p 5455

 7) create all the datanodes and the rest of the coordinators on the new
coordiantor & reload configuration
        CREATE NODE DATA_NODE_1 WITH (HOST = 'localhost', type =
'datanode', PORT = 15432, PRIMARY);
        CREATE NODE DATA_NODE_2 WITH (HOST = 'localhost', type =
'datanode', PORT = 25432);

        CREATE NODE COORD_1 WITH (HOST = 'localhost', type = 'coordinator',
PORT = 5432);
        CREATE NODE COORD_2 WITH (HOST = 'localhost', type = 'coordinator',
PORT = 5433);

        SELECT pgxc_pool_reload();

 8) quit psql

 9) Create the new database on the new coordinator
        ./createdb test -p 5455

10) create the roles and table spaces manually, the dump does not contain
roles or table spaces
        ./psql test -p 5455
        CREATE ROLE admin WITH LOGIN CREATEDB CREATEROLE;
        CREATE TABLESPACE my_space LOCATION
'/usr/local/pgsql/my_space_location';
        \q

11) Restore the backup that was taken from an existing coordinator by
connecting to the new coordinator directly
         ./psql -d test -f
/home/edb/Desktop/NodeAddition/dumps/101_all_objects_coord.sql -p 5455

11) Quit the new coordinator

12) Connect to any of the existing coordinators & unlock the cluster
        ./psql postgres -p 5432
        SET xc_lock_for_backup=no;
        \q

13) Start the new coordinator as a by specifying --coordinator
        ./postgres --coordinator -D ../data_cord3 -p 5455

14) Create the new coordinator on rest of the coordinators and reload
configuration
        CREATE NODE COORD_3 WITH (HOST = 'localhost', type = 'coordinator',
PORT = 5455);
        SELECT pgxc_pool_reload();

15) The new coordinator is now ready
        ./psql test -p 5455
        create table test_new_coord(a int, b int);
        \q
        ./psql test -p 5432
        select * from test_new_coord;


Here are the steps to add a new datanode


 1) Initdb new datanode
        /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data3 --nodename
data_node_3

 2) Make necessary changes in its postgresql.conf, in particular specify
new datanode name

 3) Connect to any of the existing coordinators & lock the cluster for
backup
        ./psql postgres -p 5432
        SET xc_lock_for_backup=yes;
        \q

 4) Connect to any of the existing datanodes and take backup of the database
        ./pg_dump -p 15432 -C -s
--file=/home/edb/Desktop/NodeAddition/dumps/102_all_objects_dn1.sql test

 5) Start the new datanode specify --restoremode while starting the it
        ./postgres --restoremode -D ../data3 -p 35432

 6) Create the new database on the new datanode
        ./createdb test -p 35432

 7) create the roles and table spaces manually, the dump does not contain
roles or table spaces
        ./psql test -p 35432
        CREATE ROLE admin WITH LOGIN CREATEDB CREATEROLE;
        CREATE TABLESPACE my_space LOCATION
'/usr/local/pgsql/my_space_location';
        \q

 8) Restore the backup that was taken from an existing datanode by
connecting to the new datanode directly
         ./psql -d test -f
/home/edb/Desktop/NodeAddition/dumps/102_all_objects_dn1.sql -p 35432

 9) Quit the new datanode

10) Connect to any of the existing coordinators & unlock the cluster
        ./psql postgres -p 5432
        SET xc_lock_for_backup=no;
        \q

11) Start the new datanode as a datanode by specifying --datanode
        ./postgres --datanode -D ../data3 -p 35432

12) Create the new datanode on all the coordinators and reload configuration
        CREATE NODE DATA_NODE_3 WITH (HOST = 'localhost', type =
'datanode', PORT = 35432);
        SELECT pgxc_pool_reload();

13) Redistribute data by using ALTER TABLE REDISTRIBUTE

14) The new daatnode is now ready
        ./psql test
        create table test_new_dn(a int, b int) distribute by replication;
        insert into test_new_dn values(1,2);
        EXECUTE DIRECT ON (data_node_1) 'SELECT * from test_new_dn';
        EXECUTE DIRECT ON (data_node_2) 'SELECT * from test_new_dn';
        EXECUTE DIRECT ON (data_node_3) 'SELECT * from test_new_dn';

Please note that the steps assume that the patch sent earlier
*1_lock_cluster.patch
*in mail subject [Patch to lock cluster] is applied.

I have also attached test database scripts, that would help in patch review.

Comments are welcome.

--
Abbas
Architect
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

Phone: 92-334-5100153

Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of
the individual or entity to whom it is addressed. This message
contains information from EnterpriseDB Corporation that may be
privileged, confidential, or exempt from disclosure under applicable
law. If you are not the intended recipient or authorized to receive
this for the intended recipient, any use, dissemination, distribution,
retention, archiving, or copying of this communication is strictly
prohibited. If you have received this e-mail in error, please notify
the sender immediately by reply e-mail and delete this message.