Postgres-X2 deployment steps

Postgres-X2 deployment steps

Sort 2015 elephant will, everyone is very concerned about PostgreSQL cluster, at present, developers have turned to Postgres-X2, recently according to their own and colleagues deploy xl process to deploy a Postgres-X2. This deployment experiment uses pgxc_ctl to deploy clusters more flexibly.

1. Overall summary:

There are four nodes in total, one gtm, one coordinator, and two datanode.

A. GTM Node
IP: 192.168.238.129
Nodename: gtm
Port: 6666
B. coordinator
IP: 192.168.238.130
Nodename: coord1
Port: 5432
Pooler_port: 6668
C. datanode1
IP: 192.168.238.131
Nodename: datanode1
Port: 15432
Pooler_port: 6669
D. datanode2
IP: 192.168.238.132
Nodename: datanode2
Port: 15432
Pooler_port: 6669

2. Preparation (unless specified, the four nodes perform the same operation ):

A. Compile and install pgx2 and compile contrib.

./Configure -- prefix =/opt/pgx2
Make; make install
Cd contrib
Make; make install

B. Create postgres and assign the owner of the installation directory to postgres.

Chown-R postgres: postgres pgx2

C. Configure an ssh connection

[S @ localhost ~] # Ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/. ssh/id_rsa ):
Enter passphrase (empty for no passphrase ):
Enter same passphrase again:
Your identification has been saved in/root/. ssh/id_rsa.
Your public key has been saved in/root/. ssh/id_rsa.pub.
The key fingerprint is:
Ea: c9: 48: 2d: dc: 0d: AB: 9b: 3d: 99: cb: bd: db: 3b: ba: fa root@localhost.localdomain
The key's randomart image is:
+ -- [RSA 2048] ---- +
|
|
|
|
|. S |
|. O = |
| + = O. |
|. X + o... |
| =. O = E = oo |
+ ----------------- +
[S @ localhost ~] #

Cat ~ /. Ssh/id_rsa.pub> ~ /. Ssh/authorized_keys
 
Vi/etc/hosts
192.168.238.129 localhost. localdomain
 
-- Distribution key, which is distributed by gtm nodes to other nodes
Scp ~ //. Ssh/authorized_keys postgres@192.168.238.130
Scp ~ //. Ssh/authorized_keys postgres@192.168.238.131
Scp ~ //. Ssh/authorized_keys postgres@192.168.238.132

D. Configure Environment Variables

[S @ localhost ~] $ Cat. bashrc
#. Bashrc
 
# Source global definitions
If [-f/etc/bashrc]; then
./Etc/bashrc
Fi
 
Export PGHOME =/opt/pgx2/
Export PGUSER = postgres
Export LD_LIBRARY_PATH = $ PGHOME/lib
Export PATH = $ PGHOME/bin: $ PATH
 
# User specific aliases and functions
[S @ localhost ~] $

Suggestion:

The validity rate of ssh connections is slow. You can use ssh-v for detection. This is not described here.
 
Modify GSSAPIAuthentication and UseDNS to no in/etc/ssh/sshd_config to speed up ssh connection.

For convenience, I disable iptables for all nodes. You can configure it on your own.

3. Deploy nodes

A. Configure pgxc_ctl.conf

-- Under/home/postgres/pgxc_ctl
 
-- Conf content
Cat pgxc_ctl.conf
# User and path
PgxcOwner = postgres
PgxcUser = $ pgxcOwner
PgxcInstallDir =/opt/pgx2
 
# Gtm and gtmproxy
GtmMasterDir = $ HOME/pgxc/nodes/gtm
GtmMasterPort = 6666
GtmMasterServer = 192.168.238.129
GtmSlave = n
 
# Gtmproxy
GtmProxy = n
GtmProxyDir = $ HOME/pgxc/nodes/coord
GtmProxyNames = (gtm_pxy1)
GtmProxyServers = (192.168.238.130)
GtmProxyPorts = (20001)
GtmProxyDirs = ($ gtmProxyDir/gtm_pxy1)
GtmPxyExtraConfig = (none)
GtmPxySpecificExtraConfig = (none)
 
# Coordinator
CoordMasterDir = $ HOME/pgxc/nodes/coord
CoordNames = (coord1)
CoordPorts = (5432)
PoolerPorts = (6668)
CoordPgHbaEntries = (192.168.238.0/24)
CoordMasterServers = (192.168.238.130)
CoordMasterDirs = ($ coordMasterDir/coord1)
CoordMaxWALsernder = 0
CoordMaxWALSenders = ($ coordMaxWALsernder)
CoordSlave = n
CoordSpecificExtraConfig = (none)
CoordSpecificExtraPgHba = (none)
 
# Datanode
DatanodeNames = (datanode1 datanode2)
DatanodePorts = (15432 15432)
DatanodePoolerPorts = (6669 6669)
DatanodePgHbaEntries = (192.168.238.0/24)
DatanodeMasterServers = (192.168.238.131 192.168.238.132)
DatanodeMasterDir = $ HOME/pgxc/nodes/dn_master
DatanodeMasterDirs = ($ datanodeMasterDir/datanode1 $ datanodeMasterDir/datanode2)
DatanodeMaxWALsernder = 0
DatanodeMaxWALSenders = ($ datanodeMaxWALsernder)
DatanodeSlave = n
PrimaryDatanode = datanode1
DatanodeSpecificExtraConfig = (none)
DatanodeSpecificExtraPgHba = (none)

B. Use pgxc_ctl to deploy nodes

Pgxc_ctl init all
 
[S @ localhost ~] $ Pgxc_ctl init all
Installing pgxc_ctl_bash script as/home/postgres/pgxc_ctl/pgxc_ctl_bash.
Installing pgxc_ctl_bash script as/home/postgres/pgxc_ctl/pgxc_ctl_bash.
Reading configuration using/home/postgres/pgxc_ctl/pgxc_ctl_bash -- home/postgres/pgxc_ctl -- configuration/home/S/pgxc_ctl/pgxc_ctl.conf
Finished to read configuration.
* ******* PGXC_CTL START ***************
 
Current directory:/home/postgres/pgxc_ctl
Initialize GTM master
The files belonging to this GTM system will be owned by user "s ".
This user must also own the server process.
 
 
Fixing permissions on existing directory/home/postgres/pgxc/nodes/gtm... OK
Creating configuration files... OK
 
Success. You can now start the GTM server using:
 
Gtm-D/home/postgres/pgxc/nodes/gtm
Or
Gtm_ctl-Z gtm-D/home/postgres/pgxc/nodes/gtm-l logfile start
 
Waiting for server to shut down... done
Server stopped
Done.
Start GTM master
Gtm_ctl: PID file "/home/postgres/pgxc/nodes/gtm. pid" does not exist
Is server running?
Server starting
Initialize all the coordinator masters.
Initialize coordinator master coord1.
The files belonging to this database system will be owned by user "stored s ".
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/home/postgres/pgxc/nodes/coord/coord1... OK
Creating subdirectories... OK
Selecting default max_connections... 100
Selecting default shared_buffers... 128 MB
Creating configuration files... OK
Creating template1 database in/home/postgres/pgxc/nodes/coord/coord1/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 copying s... 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_0000.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-XC coordinator using:
 
Postgres -- coordinator-D/home/postgres/pgxc/nodes/coord/coord1
Or
Pg_ctl start-D/home/postgres/pgxc/nodes/coord/coord1-Z coordinator-l logfile
 
You can now start the database server of the Postgres-XC datanode using:
 
Postgres -- datanode-D/home/postgres/pgxc/nodes/coord/coord1
Or
Pg_ctl start-D/home/postgres/pgxc/nodes/coord/coord1-Z datanode-l logfile
 
Done.
Starting coordinator master.
Starting coordinator master coord1
Done.
Initialize all the datanode masters.
Initialize the datanode master datanode1.
Initialize the datanode master datanode2.
The files belonging to this database system will be owned by user "stored s ".
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/home/postgres/pgxc/nodes/dn_master/datanode1... OK
Creating subdirectories... OK
Selecting default max_connections... 100
Selecting default shared_buffers... 128 MB
Creating configuration files... OK
Creating template1 database in/home/postgres/pgxc/nodes/dn_master/datanode1/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 copying s... 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_0000.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-XC coordinator using:
 
Postgres -- coordinator-D/home/postgres/pgxc/nodes/dn_master/datanode1
Or
Pg_ctl start-D/home/postgres/pgxc/nodes/dn_master/datanode1-Z coordinator-l logfile
 
You can now start the database server of the Postgres-XC datanode using:
 
Postgres -- datanode-D/home/postgres/pgxc/nodes/dn_master/datanode1
Or
Pg_ctl start-D/home/postgres/pgxc/nodes/dn_master/datanode1-Z datanode-l logfile
 
The files belonging to this database system will be owned by user "stored s ".
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/home/postgres/pgxc/nodes/dn_master/datanode2... OK
Creating subdirectories... OK
Selecting default max_connections... 100
Selecting default shared_buffers... 128 MB
Creating configuration files... OK
Creating template1 database in/home/postgres/pgxc/nodes/dn_master/datanode2/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 copying s... 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_0000.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-XC coordinator using:
 
Postgres -- coordinator-D/home/postgres/pgxc/nodes/dn_master/datanode2
Or
Pg_ctl start-D/home/postgres/pgxc/nodes/dn_master/datanode2-Z coordinator-l logfile
 
You can now start the database server of the Postgres-XC datanode using:
 
Postgres -- datanode-D/home/postgres/pgxc/nodes/dn_master/datanode2
Or
Pg_ctl start-D/home/postgres/pgxc/nodes/dn_master/datanode2-Z datanode-l logfile
 
Done.
Starting all the datanode masters.
Starting datanode master datanode1.
Starting datanode master datanode2.
Done.
Alter node coord1 WITH (HOST = '192. 168.238.130 ', PORT = 192 );
ALTER NODE
Create node datanode1 WITH (TYPE = 'datanode', HOST = '2017. 168.238.131 ', PORT = 192, PRIMARY );
CREATE NODE
Create node datanode2 WITH (TYPE = 'datanode', HOST = '192. 168.238.132 ', PORT = 192 );
CREATE NODE
Done.

4. Run the demo

[S @ localhost ~] $ Psql-h 192.168.238.130-p 5432-d postgres-U postgres
Psql (PGXC 1.3 devel, based on PG 9.4beta1)
Type "help" for help.
 
Postgres = # create table test (id int, name text) distribute by replication;
CREATE TABLE
S = # insert into test values (1, 'wang '), (2, 'shuo ');
INSERT 0 2
S = # select * from test;
Id | name
---- + ------
1 | wang
2 | shuo
(2 rows)
 
Postgres = # \ q
[S @ localhost ~] $ Psql-h 192.168.238.131-p 15432-d postgres-U postgres
Psql (PGXC 1.3 devel, based on PG 9.4beta1)
Type "help" for help.
 
S = # select * from test;
Id | name
---- + ------
1 | wang
2 | shuo
(2 rows)
 
Postgres = # \ q
[S @ localhost ~] $ Psql-h 192.168.238.132-p 15432-d postgres-U postgres
Psql (PGXC 1.3 devel, based on PG 9.4beta1)
Type "help" for help.
 
S = # select * from test;
Id | name
---- + ------
1 | wang
2 | shuo
(2 rows)
 
Postgres = #

Summary:

Compared with manual deployment, the efficiency and accuracy of pgxc_ctl deployment are very high. You are welcome to try it.

------------------------------------ Lili split line ------------------------------------

Install PostgreSQL 6.3 on yum in CentOS 9.3

PostgreSQL cache details

Compiling PostgreSQL on Windows

Configuration and installation of LAPP (Linux + Apache + PostgreSQL + PHP) Environment in Ubuntu

Install and configure phppgAdmin on Ubuntu

Install PostgreSQL9.3 on CentOS

Configure a Streaming Replication cluster in PostgreSQL

How to install PostgreSQL 7/6 and phpPgAdmin in CentOS 5/6. 4

------------------------------------ Lili split line ------------------------------------

PostgreSQL details: click here
PostgreSQL: click here

This article permanently updates the link address: