Install and configure postges-xl

Other than official documentation i did not find much help . It took one hour for me to setup . As i thought its straight forward setup . Download source , compile it and install it . But there is more to it 

Below are steps i followed :

Download a stable branch 

git clone --depth=1  -b XL9_5_STABLE git://git.postgresql.org/git/postgres-xl.git

cd postgres-xl/

./configure

make

cd contrib/pgxc_ctl

make

cd postgres-xl/ ; make install

cd contrib/pgxc_ctl ; make install 


Now installation is done 

Add below in /etc/profile

export LD_LIBRARY_PATH=/usr/local/pgsql/lib
export PGDATA=/usr/local/pgsql/data
export PATH=$PATH:/usr/local/pgsql/bin

Then add postgres user 
and set /user/local/pgsql as its home directory

chown postgres:postgres -R /user/local/pgsql 

Now we need to setup postgres-XL . Since it is distributed database we need atleast two nodes . In general every component sits on its own node and also have stand by nodes .

For this tutorial purpose i am using two nodes node1 and node2


Major components of Postgres-XL

Global Transaction Monitor (GTM) : The Global Transaction Monitor ensures cluster-wide transaction consistency. GTM is responsible for issuing transaction ids and snapshots as part of its Multi-version Concurrency Control.

Coordinator : The Coordinator manages the user sessions and interacts with GTM and the data nodes.

Data Node : The Data Node is where the actual data is stored.
I have tried installing everything on  node1 for demo purpose but default port of coordinator and data node are same also the share same /tmp directory structure.

So we might  need to change port number then temp directories etc

      Below is the error for reference purpose

      postgres@Node1:~$ postgres --datanode -D       /usr/local/pgsql/dbdata -h 0.0.0.0 -p 5432 -c gtm_host=node1 -c gtm_port=6666 &
[3] 35190
postgres@Node1:~$ LOG:  could not bind IPv4 socket: Address already in use
HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
WARNING:  could not create listen socket for "0.0.0.0"
FATAL:  could not create any TCP/IP sockets
^C
[3]+  Exit 1                  postgres --datanode -D /usr/local/pgsql/dbdata -h 0.0.0.0 -p 5432 -c gtm_host=node1 -c gtm_port=6666
postgres@Node1:~$ postgres --datanode -D /usr/local/pgsql/dbdata -h 0.0.0.0 -p 5433 -c gtm_host=node1 -c gtm_port=6666 &
[3] 35196
postgres@Node1:~$ LOG:  database system was shut down at 2017-08-01 16:29:12 UTC
FATAL:  lock file "/tmp/.s.PGPOOL.6667.lock" already exists
HINT:  Is another postmaster (PID 35113) using socket file "/tmp/.s.PGPOOL.6667"?
LOG:  pool manager process (PID 35199) exited with exit code 1
LOG:  terminating any other active server processes
LOG:  startup process (PID 35198) exited with exit code 2
LOG:  aborting startup due to startup process failure

[3]+  Exit 1                  postgres --datanode -D /usr/local/pgsql/dbdata -h 0.0.0.0 -p 5433 -c gtm_host=node1 -c gtm_port=6666


Now this two node setup 
Node1 will have  Global Transaction monitor + Coordinator
Node2 will be the Data node .
We need to download and install from source on both the servers then 
One Node1 
Init GTM
Create a directory in /usr/local/pgsql called gtm

postgres@Node1:~/data$ initgtm -D  /usr/local/pgsql/gtm -Z gtm
The files belonging to this GTM system will be owned by user "postgres".
This user must also own the server process.


fixing permissions on existing directory /usr/local/pgsql/gtm ... ok
creating configuration files ... ok
creating control file ... ok

Success.
You can now start the GTM server using:

    gtm -D /usr/local/pgsql/gtm
or
    gtm_ctl -Z gtm -D /usr/local/pgsql/gtm -l logfile start
Then start GTM service 
gtm -D /usr/local/pgsql/gtm -h 0.0.0.0 -n node1 -p 6666 &

Now coordinator service 
Create a directory pgdata or coordinator and run below 

postgres@Node1:~$ initdb -D /usr/local/pgsql/pgdata --nodename=node1
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /usr/local/pgsql/pgdata ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /usr/local/pgsql/pgdata/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
creating cluster information ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok
freezing database template0 ... ok
freezing database template1 ... ok
freezing database postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success.
You can now start the database server of the Postgres-XL coordinator using:

    postgres --coordinator -D /usr/local/pgsql/pgdata
or
    pg_ctl start -D /usr/local/pgsql/pgdata -Z coordinator -l logfile

 You can now start the database server of the Postgres-XL datanode using:

    postgres --datanode -D /usr/local/pgsql/pgdata
or 
    pg_ctl start -D /usr/local/pgsql/pgdata -Z datanode -l logfile

Now start the service 

 postgres --coordinator -D /usr/local/pgsql/pgdata -h 0.0.0.0 -p 5432 -c gtm_host=node1 -c gtm_port=6666 &
Now on node2  after you compile from source and followed steps till adding content in /etc/profile and adding of postgres user is done
Create a data folder and initialize as above
initdb -D /usr/local/pgsql/data --nodename=node2
Then start service 
postgres --datanode -D /usr/local/pgsql/data -h 0.0.0.0 -p 5432 -c gtm_host=node1 -c gtm_port=6666 &


Now you can run psql and connect

postgres@Node2:~$ psql
psql (PGXL 9.5r1.5, based on PG 9.5.6 (Postgres-XL 9.5r1.5))
Type "help" for help.

postgres=# 

No comments: