[Postgres-xl-developers] Review Postges-XL Configuration Example

Koichi Suzuki koichi.dbms at gmail.com
Sun Aug 23 23:43:31 PDT 2015


I don't think it's a good idea to configure manually from the scratch.
 Your cluster seems to be supported by pgxc_ctl.   I'd advice to use this
to avoid many pitfalls.

Thank you;

---
Koichi Suzuki
https://www.linkedin.com/in/koichidbms


2015-08-24 15:40 GMT+09:00 Rui Hai Jiang <ruihaijiang at msn.com>:

>
>
> Hello,
>
>
>
> I have tried many times to install the Postgres-XL. And finally I made it.
>
> Since I counldn’t find a complete guide from the internet, I came up with
> an example and hope to share with other people.
>
>
>
> Could you please help to review my example? See following. BTW, I also
> tried the pgxc_ctl tool, but it doesn’t work for me and I’ll keep trying.
>
>
>
> Thank you!
>
>
>
>
> ===========================================================================================
>
>
>
>             Postges-XL Configuration Example
>
>
>
> 1. Overview
>
>
>
>     In this example, we configure a Postgres-XL cluster for test only.
>
>     The cluster includes 4 nodes. They are, 1 GTM, 1 Coordinator and 2
> Datanodes.
>
>     Each node is deployed on a separate host so there are totally 4 hosts
> used.
>
>
>
>     It's also possible to deploy all the nodes on one host.
>
>     To do that, you just need to change all the IPs to the same one.
>
>
>
>     GTM:
>
>     hostname=host1
>
>     nodename=gtm
>
>     IP=192.168.187.130
>
>     port=6666
>
>
>
>     Coordinator:
>
>     hostname=host2
>
>     nodename=coord1
>
>     IP=192.168.187.131
>
>     pooler_port=6668,port=5432
>
>
>
>     Datanode1:
>
>     hostname=host3
>
>     nodename=datanode1
>
>     IP=192.168.187.132
>
>     pooler_port=6669, port=15432
>
>
>
>     Datanode2:
>
>     hostname=host4
>
>     nodename=datanode2
>
>     IP=192.168.187.133
>
>     pooler_port=6670, port=15433
>
>
>
>
>
> 2.  Install from source code
>
>
>
>     On every host, create an account named 'postgres'.
>
>
>
>     Do following as user postgres,
>
>     Download source code and run following,
>
>        ./configure
>
>         make
>
>
>
>     Install the binary as user root,
>
>         make install
>
>
>
>     By default, Postgres-XL is installed in directory /usr/local/pgsql/
>
>
>
>
>
> 3. Configuration before starting nodes
>
>
>
>     Use postgres account to do all following.
>
>     If needed, you can add /usr/local/pgsql/bin to the PATH.
>
>
>
> 3.1 Create directores
>
>
>
>     on host1:
>
>     mkdir -p /home/postgres/db/data_gtm
>
>
>
>     on host2:
>
>     mkdir -p /home/postgres/db/data_coord1
>
>
>
>     on host3:
>
>     mkdir -p /home/postgres/db/data_datanode1
>
>
>
>     on host4:
>
>     mkdir -p /home/postgres/db/data_datanode2
>
>
>
> 3.2  Initialize the work directory for each node
>
>
>
>     host1:
>
>     /usr/local/pgsql/bin/initgtm -D /home/postgres/db/data_gtm -Z gtm
>
>
>
>     host2:
>
>     /usr/local/pgsql/bin/initdb -D /home/postgres/db/data_coord1
> --nodename coord1
>
>
>
>     host3:
>
>     /usr/local/pgsql/bin/initdb -D /home/postgres/db/data_datanode1
> --nodename datanode1
>
>
>
>     host4:
>
>     /usr/local/pgsql/bin/initdb -D /home/postgres/db/data_datanode2
> --nodename datanode2
>
>
>
> 3.3  On each host, edit postgresql.conf
>
>
>
>     In this section, we configure postgresql.conf for each node except gtm
> because gtm uses the default parameter values.
>
>
>
>     On host2,edit /home/postgres/db/data_coord1/postgresql.conf
>
>     pooler_port = 6668
>
>     listen_addresses = '192.168.187.131'
>
>     gtm_host = '192.168.187.130'
>
>
>
>
>
>     On host3,edit /home/postgres/db/data_datanode1/postgresql.conf
>
>
>
>     port = 15432
>
>     listen_addresses = '192.168.187.132'
>
>     pooler_port = 6669
>
>     gtm_host = '192.168.187.130'
>
>
>
>
>
>     On host4,edit /home/postgres/db/data_datanode2/postgresql.conf
>
>
>
>     pooler_port = 6670
>
>     listen_addresses = '192.168.187.133'
>
>     port = 15433
>
>     gtm_host = '192.168.187.130'
>
>
>
> 3.4  Configure datanode1 and datanode2 to trust each other, edit
> pg_hba.conf
>
>     Note, if datanode1 and datanode2 are installed on the same host, this
> section can be skipped.
>
>
>
>     On host3, edit /home/postgres/db/data_datanode1/pg_hba.conf
>
>     append one line:
>
>     host    all        all             192.168.187.0/24         trust
>
>
>
>     On host4,edit /home/postgres/db/data_datanode2/pg_hba.conf
>
>     append one line:
>
>     host    all        all             192.168.187.0/24         trust
>
>
>
> 3.5  Configure the firewall
>
>     Note, if all nodes are installed on the same host, this section can be
> skipped.
>
>
>
>     On each host, configure the firewall to accept incomming connections
> for the TCP port numbers.
>
>     If the cluster is only for testing, you can just turn off the firewall.
>
>
>
>
>
> 4  Start each node
>
>     Use the postgres account, run each of the following command in a
> terminal window.
>
>     On host1,start gtm
>
>     /usr/local/pgsql/bin/gtm -D /home/postgres/db/data_gtm
>
>     On host2, start data_coord1,
>
>     /usr/local/pgsql/bin/postgres --coordinator -D
> /home/postgres/db/data_coord1
>
>
>
>     On host3,start datanode1
>
>     /usr/local/pgsql/bin/postgres --datanode -D
> /home/postgres/db/data_datanode1
>
>     On host4,start datanode2
>
>     /usr/local/pgsql/bin/postgres --datanode -D
> /home/postgres/db/data_datanode2
>
>
>
> 5  Configure each node of the cluster
>
>     Since the gtm uses default configuration, there is no configuration
> change for the gtm node.
>
> 5.1  On host2,configure coord1
>
>
>
>     Use psql to connect to coord1. Cord1 uses the default port 5432.
>
>
>
>     /usr/local/pgsql/bin/psql postgres
>
>
>
>     Show existing nodes, use following command,
>
>
>
>     postgres=# select * from pgxc_node;
>
>      node_name | node_type | node_port |    node_host    | nodeis_primary
> | nodeis_preferred |  node_id
>
>
>     -----------+-----------+-----------+-----------------+----------------+------------------+------------
>
>      coord1    | C         |      5432 | localhost       | f
> | f                | 1885696643
>
>
>
>     Change the node_host from localhost to 192.168.187.131, use following
> command,
>
>
>
>     alter node coord1 with(HOST='192.168.187.131', PORT=15432);
>
>
>
>     Define 2 datanodes and apply change using following commands,
>
>     CREATE NODE datanode1 WITH (HOST = '192.168.187.132',TYPE =
> 'datanode', PORT = 15432);
>
>     CREATE NODE datanode2 WITH (HOST = '192.168.187.133', TYPE =
> 'datanode', PORT = 15433);
>
>     SELECT pgxc_pool_reload();
>
>
>
> 5.2  On host3,configure datanode1
>
>
>
>     Use psql to connect to datanode1. Note datanode1 uses port 15432.
>
>
>
>     /usr/local/pgsql/bin/psql postgres -p 15432
>
>
>
>     Check node information:
>
>
>
>     postgres=# select * from pgxc_node;
>
>      node_name | node_type | node_port | node_host | nodeis_primary |
> nodeis_preferred |  node_id
>
>
>     -----------+-----------+-----------+-----------+----------------+------------------+-----------
>
>      datanode1 | C         |      5432 | localhost | f              |
> f                | 888802358
>
>     (1 row)
>
>
>
>
>
>     We need to change datanode1 as following,
>
>
>
>     alter node datanode1 with(TYPE='datanode', HOST='192.168.187.132',
> PORT=15432);
>
>
>
>     Next, define datanode2 and coord1, as following,
>
>
>
>     CREATE NODE coord1 WITH (HOST = '192.168.187.130', type =
> 'coordinator', PORT = 5432);
>
>     CREATE NODE datanode2 WITH (HOST = '192.168.187.133', TYPE =
> 'datanode', PORT = 15433);
>
>
>
>     At last, apply the change,
>
>
>
>     SELECT pgxc_pool_reload();
>
>
>
>
>
> 5.3  On host4,configure datanode2
>
>
>
>     Use psql,connect to datanode2. The port number is 15433.
>
>
>
>
>
>     /usr/local/pgsql/bin/psql postgres -p 15433
>
>
>
>     Check the existing information,
>
>
>
>     postgres=# select * from pgxc_node;
>
>      node_name | node_type | node_port | node_host | nodeis_primary |
> nodeis_preferred |  node_id
>
>
>     -----------+-----------+-----------+-----------+----------------+------------------+------------
>
>      datanode2 | C         |      5432 | localhost | f              |
> f                | -905831925
>
>     (1 row)
>
>
>
>     We should change datanode2, as following,
>
>
>
>     alter node datanode2 with(TYPE='datanode', HOST='192.168.187.133',
> PORT=15433);
>
>
>
>     Next, define coord1 and datanode1, and apply the change.
>
>
>
>     CREATE NODE coord1 WITH (HOST = '192.168.187.130', type =
> 'coordinator', PORT = 5432);
>
>     CREATE NODE datanode1 WITH (HOST = '192.168.187.132',TYPE =
> 'datanode', PORT = 15432);
>
>     SELECT pgxc_pool_reload();
>
>
>
>
>
> 6 Test the cluster
>
> 6.1  Create the database
>
>
>
>     On host2, use following command to create database test,
>
>     /usr/local/pgsql/bin/createdb test
>
>
>
>     After the command, a database named 'test' should exist on both
> datanode1 and datanode2.
>
>
>
> 6.2  Connect to coord1, create a table, insert data and read data
>
>
>
>     On host2, connect to coord1,
>
>
>
>     /usr/local/pgsql/bin/psql test
>
>     psql (PGXL 9.2.0, based on PG 9.2.4 (Postgres-XL 9.2.0))
>
>     Type "help" for help.
>
>
>
>     test=# create table hg1( id int, name text) DISTRIBUTE BY REPLICATION;
>
>     CREATE TABLE
>
>     test=# insert into hg1 values ( 1,'tom');
>
>     INSERT 0 1
>
>     test=# select * from hg1;
>
>      id | name
>
>     ----+------
>
>       1 | tom
>
>     (1 row)
>
>
>
> 6.3  Read data on datanode1
>
>
>
>     On host3, connect to datanode1 and read data,
>
>     /usr/local/pgsql/bin/psql test -p 15432
>
>     select * from hg1;
>
>
>
> 6.4  Read data on datanode2
>
>
>
>     On host4, connect to datanode2 and read data,
>
>     /usr/local/pgsql/bin/psql test -p 15433
>
>     select * from hg1;
>
> 7 Stop the cluster
>
>     One easy way to stop each node is to type CTRL+C in each terminal
> window.
>
>     First, stop all datanodes, then stop the Coordinator, at last, stop
> gtm.
>
>
> ------------------------------------------------------------------------------
>
> _______________________________________________
> Postgres-xl-developers mailing list
> Postgres-xl-developers at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/postgres-xl-developers
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/private.cgi/postgres-xl-developers-postgres-xl.org/attachments/20150824/15964684/attachment.htm>


More information about the Postgres-xl-developers mailing list