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

Rui Hai Jiang ruihaijiang at msn.com
Sun Aug 23 23:40:10 PDT 2015


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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/private.cgi/postgres-xl-developers-postgres-xl.org/attachments/20150824/66ab7e20/attachment.htm>


More information about the Postgres-xl-developers mailing list