centos 7.4 使用 pgxc_ctl 安裝與使用

os: centos 7.4

pgxl:pg.version '10.3 (Postgres-XL 10alpha2)

pgxl 是一款非常實用的橫向擴充的開源軟體，繼承了很多pgxc的功能，在replication 和sharding 方面有着非常棒的用處。

pgxl 不嚴格的說是 pgxc的更新加強版。是對官方 postgresql 的版本的修改提升，為大牛點贊。

Global Transaction Monitor (GTM)

全局事務管理器，確定群集範圍内的事務一緻性。 GTM負責發放事務ID和快照作為其多版本并發控制的一部分。

叢集可選地配置一個備用GTM，以改進可用性。此外，可以在協調器間配置代理GTM，可用于改善可擴充性，減少GTM的通信量。

GTM Standby

GTM的備節點，在pgxc,pgxl中，GTM控制所有的全局事務配置設定，如果出現問題，就會導緻整個叢集不可用，為了增加可用性，增加該備用節點。當GTM出現問題時，GTM Standby可以更新為GTM，保證叢集正常工作。

GTM-Proxy

GTM需要與所有的Coordinators通信，為了降低壓力，可以在每個Coordinator機器上部署一個GTM-Proxy。

Coordinator

協調員管理使用者會話，并與GTM和資料節點進行互動。協調員解析，并計劃查詢，并給語句中的每一個元件發送下一個序列化的全局性計劃。

為節省機器，通常此服務和資料節點部署在一起。

Data Node

資料節點是資料實際存儲的地方。資料的分布可以由DBA來配置。為了提高可用性，可以配置資料節點的熱備以便進行故障轉移準備。

總結：

gtm是負責ACID的，保證分布式資料庫全局事務一緻性。得益于此，就算資料節點是分布的，但是你在主節點操作增删改查事務時，就如同隻操作一個資料庫一樣簡單。

Coordinator是排程的，将操作指令發送到各個資料節點。

datanodes是資料節點，分布式存儲資料。

規劃如下：

node1 192.168.56.101 gtm

node2 192.168.56.102 gtm-proxy,coordinator,datanode

node3 192.168.56.103 gtm-proxy,coordinator,datanode

下載下傳

https://www.postgres-xl.org/download/

https://git.postgresql.org/gitweb/?p=postgres-xl.git;a=summary

git://git.postgresql.org/git/postgres-xl.git

安裝

node1 需要安裝依賴包

# yum install -y bison flex perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc gcc-c++ openssl-devel cmake openjade docbook-style-dsssl uuid uuid-devel

node1 節點上關閉防火牆，selinux

# systemctl stop firewalld.service

# systemctl disable firewalld.service

# vim /etc/selinux/config

disabled

node1 節點上建立使用者

# groupadd postgres

# useradd postgres -g postgres

# passwd postgres

# mkdir -p /usr/pgxl-10

# chown -R postgres:postgres /usr/pgxl-10

# mkdir -p /var/lib/pgxl

# cd /var/lib/pgxl

# mkdir {gtm,gtm_slave,pgxc_ctl}

# chown -R postgres:postgres /var/lib/pgxl

node1 節點 postgres 使用者的環境變量

# su - postgres

$ vi ~/.bash_profile

export PGUSER=postgres

export PGHOME=/usr/pgxl-10

export PGXC_CTL_HOME=/var/lib/pgxl/pgxc_ctl

export LD_LIBRARY_PATH=$PGHOME/lib

export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib

export PATH=$PGHOME/bin:$PATH

export TEMP=/tmp

export TMPDIR=/tmp

export PS1="\[\e[32;1m\][\u@\h \W]$>\[\e[0m\]"

node1 上編譯安裝

$ cd /tmp

$ git clone git://git.postgresql.org/git/postgres-xl.git

$ cd postgres-xl

$ git branch -r

origin/HEAD -> origin/master

origin/XL9_5_STABLE

origin/XL_10_STABLE

origin/master

origin/xl_dbt3_expt

origin/xl_doc_update

origin/xl_test

$ git checkout XL_10_STABLE

Branch XL_10_STABLE set up to track remote branch XL_10_STABLE from origin.

Switched to a new branch 'XL_10_STABLE'

$ git status

# On branch XL_10_STABLE

nothing to commit, working directory clean

$ ./configure --prefix=/usr/pgxl-10 --with-perl --with-python --with-openssl --with-pam --with-ldap --with-libxml --with-libxslt

$ make

$ make install

$ cd contrib

以上操作除了 mkdir {gtm,gtm_slave,pgxc_ctl} 指令外，都需要在 node2、node3 上執行。

另外在 node2、node3節點上還需要運作如下指令

$ cd /var/lib/pgxl

$ mkdir {gtm_proxy}

$ mkdir {coord,coord_slave,coord_archlog}

$ mkdir {dn_master,dn_slave,dn_archlog}

node1、node2、node3配置ssh互相免密登入

過程略

node1、node2、node3同步下時間

# ntpdate asia.pool.ntp.org

node1,node2,node3 節點修改環境變量

~/.bashrc

~/.bash_profile 這兩個檔案都需要修改

$ vi ~/.bashrc

不修改環境會出現 command not found 的錯誤提示

這裡是依賴 ~/.bashrc

Current directory: /var/lib/pgxl/pgxc_ctl

Initialize GTM master

ERROR: target directory (/var/lib/pgxl/gtm) exists and not empty. Skip GTM initilialization

bash: gtm: command not found

bash: gtm_ctl: command not found

Done.

Start GTM master

Initialize GTM slave

bash: initgtm: command not found

pgxc_ctl 生成配置檔案

$ which pgxc_ctl

/usr/pgxl-10/bin/pgxc_ctl

$ pgxc_ctl prepare

/bin/bash

Installing pgxc_ctl_bash script as /var/lib/pgxl/pgxc_ctl/pgxc_ctl_bash.

ERROR: File "/var/lib/pgxl/pgxc_ctl/pgxc_ctl.conf" not found or not a regular file. No such file or directory

Reading configuration using /var/lib/pgxl/pgxc_ctl/pgxc_ctl_bash --home /var/lib/pgxl/pgxc_ctl --configuration /var/lib/pgxl/pgxc_ctl/pgxc_ctl.conf

Finished reading configuration.

******** PGXC_CTL START ***************

$ ls -l /var/lib/pgxl/pgxc_ctl

total 24

-rw-r--r-- 1 postgres postgres 246 Jul 18 16:42 coordExtraConfig

-rw-r--r-- 1 postgres postgres 17815 Jul 18 16:42 pgxc_ctl.conf

pgxc_ctl 修改配置檔案

$ vi /var/lib/pgxl/pgxc_ctl/pgxc_ctl.conf

pgxcInstallDir=/usr/pgxl-10

#---- OVERALL

pgxcOwner=postgres

pgxcUser=$pgxcOwner

tmpDir=/tmp

localTmpDir=$tmpDir

#---- GTM

#---- GTM Master

#---- Overall

gtmName=node1_gtm

gtmMasterServer=node1

gtmMasterPort=6666

gtmMasterDir=/var/lib/pgxl/gtm

#---- Configuration

gtmExtraConfig=none

gtmMasterSpecificExtraConfig=none

#---- GTM Slave

gtmSlave=y

gtmSlaveName=node1_gtm_slave

gtmSlaveServer=node1

gtmSlavePort=6667

gtmSlaveDir=/var/lib/pgxl/gtm_slave

gtmSlaveSpecificExtraConfig=none

#---- GTM Proxy

#---- Shortcuts

gtmProxyDir=/var/lib/pgxl/gtm_proxy

gtmProxy=y

gtmProxyNames=(gtm_proxy1 gtm_proxy2)

gtmProxyServers=(node2 node3)

gtmProxyPorts=(6668 6668)

gtmProxyDirs=($gtmProxyDir $gtmProxyDir)

gtmPxyExtraConfig=none

gtmPxySpecificExtraConfig=(none none)

#---- Coordinators

#---- shortcuts

coordMasterDir=/var/lib/pgxl/coord

coordSlaveDir=/var/lib/pgxl/coord_slave

coordArchLogDir=/var/lib/pgxl/coord_archlog

coordNames=(coord1 coord2)

coordPorts=(20004 20005)

poolerPorts=(20010 20011)

coordPgHbaEntries=(192.168.56.0/24)

#---- Master -------------

coordMasterServers=(node2 node3)

coordMasterDirs=($coordMasterDir $coordMasterDir)

coordMaxWALsernder=10

coordMaxWALSenders=($coordMaxWALsernder $coordMaxWALsernder)

#---- Slave -------------

coordSlave=y

coordSlaveSync=y

coordSlaveServers=(node3 node2)

coordSlavePorts=(20004 20005) # Master ports

coordSlavePoolerPorts=(20010 20011) # Master pooler ports

coordSlaveDirs=($coordSlaveDir $coordSlaveDir)

coordArchLogDirs=($coordArchLogDir $coordArchLogDir)

#---- Configuration files

coordExtraConfig=coordExtraConfig

cat > $coordExtraConfig <<EOF

#================================================

# Added to all the coordinator postgresql.conf

# Original: $coordExtraConfig

log_destination = 'csvlog'

logging_collector = on

log_directory = 'pg_log'

listen_addresses = '*'

max_connections = 100

EOF

coordSpecificExtraConfig=(none none)

coordExtraPgHba=none

coordSpecificExtraPgHba=(none none)

#---- Datanodes

datanodeMasterDir=/var/lib/pgxl/dn_master

datanodeSlaveDir=/var/lib/pgxl/dn_slave

datanodeArchLogDir=/var/lib/pgxl/dn_archlog

primaryDatanode=node2

datanodeNames=(datanode1 datanode2)

datanodePorts=(20008 20009)

datanodePoolerPorts=(20012 20013)

datanodePgHbaEntries=(192.168.56.0/24)

#---- Master

datanodeMasterServers=(node2 node3)

datanodeMasterDirs=($datanodeMasterDir $datanodeMasterDir)

datanodeMaxWalSender=10

datanodeMaxWALSenders=($datanodeMaxWalSender $datanodeMaxWalSender)

#---- Slave

datanodeSlave=y

datanodeSlaveServers=(node3 node2)

datanodeSlavePorts=(20008 20009)

datanodeSlavePoolerPorts=(20012 20013)

datanodeSlaveSync=y

datanodeSlaveDirs=($datanodeSlaveDir $datanodeSlaveDir)

datanodeArchLogDirs=( $datanodeArchLogDir $datanodeArchLogDir)

# ---- Configuration files

datanodeExtraConfig=none

datanodeSpecificExtraConfig=(none none)

datanodeExtraPgHba=none

datanodeSpecificExtraPgHba=(none none)

#----- Additional Slaves

datanodeAdditionalSlaves=n

#---- WAL archives

walArchive=y

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

pgxc_ctl 的一些操作

在 node1 節點上操作

初始化叢集

$ pgxc_ctl -c /var/lib/pgxl/pgxc_ctl/pgxc_ctl.conf init all

啟動叢集

$ pgxc_ctl -c /var/lib/pgxl/pgxc_ctl/pgxc_ctl.conf start all

關閉叢集

$ pgxc_ctl -c /var/lib/pgxl/pgxc_ctl/pgxc_ctl.conf stop all

驗證

登入 node2節點的 coordinator，發現都不用再手動 create node

$ psql -p 20004

psql (PGXL 10alpha2, based on PG 10.3 (Postgres-XL 10alpha2))

Type "help" for help.

postgres=#

postgres=# select * from pgxc_node;

-----------+-----------+-----------+-----------+----------------+------------------+-------------

coord1 | C | 20004 | node2 | f | f | 1885696643

coord2 | C | 20005 | node3 | f | f | -1197102633

datanode1 | D | 20008 | node2 | t | t | -927910690

datanode2 | D | 20009 | node3 | f | f | 914546798

(4 rows)

再登入下 node3節點的 coordinator。

$ psql -p 20005

datanode1 | D | 20008 | node2 | t | f | -927910690

datanode2 | D | 20009 | node3 | f | t | 914546798

在任意一個coordinator執行如下操作

postgres=# create database peiybdb;

postgres=# \c peiybdb

peiybdb=# create table tmp_t0(c0 varchar(100),c1 varchar(100));

peiybdb=# insert into tmp_t0(c0,c1) SELECT id::varchar,md5(id::varchar) FROM generate_series(1,10000) as id;

INSERT 0 10000

peiybdb=# select xc_node_id,count(1) from tmp_t0 group by xc_node_id;

xc_node_id | count

------------+-------

-927910690 | 5081

914546798 | 4919

(2 rows)

very very good。

參考：

https://www.postgres-xl.org/documentation/

https://www.postgres-xl.org/documentation/runtime.html

https://www.postgres-xl.org/documentation/runtime-config.html

https://www.postgres-xl.org/documentation/pgxc-ctl.html

https://gist.github.com/ewiger/a71689db37fec5c93a9920621f52b2cf

https://sourceforge.net/p/postgres-xl/tickets/74/

https://sourceforge.net/p/postgres-xl/tickets/77/

https://www.postgres-xl.org/

https://www.postgres-xl.org/overview/

https://www.2ndquadrant.com/en/resources/postgres-xl/

https://www.postgresql.org/download

下面是 pgxc_ctl -c /var/lib/pgxl/pgxc_ctl/pgxc_ctl.conf init all 的輸出日志，記錄一下

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 /var/lib/pgxl/gtm ... ok

creating configuration files ... ok

creating control file ... ok

Success.

waiting for server to shut down.... done

server stopped

server starting

fixing permissions on existing directory /var/lib/pgxl/gtm_slave ... ok

Start GTM slaveserver starting

Initialize all the gtm proxies.

Initializing gtm proxy gtm_proxy1.

Initializing gtm proxy gtm_proxy2.

fixing permissions on existing directory /var/lib/pgxl/gtm_proxy ... ok

Starting all the gtm proxies.

Starting gtm proxy gtm_proxy1.

Starting gtm proxy gtm_proxy2.

Initialize all the coordinator masters.

Initialize coordinator master coord1.

Initialize coordinator master coord2.

The files belonging to this database system will be owned by user "postgres".

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 /var/lib/pgxl/coord ... ok

creating subdirectories ... ok

selecting default max_connections ... 100

selecting default shared_buffers ... 128MB

selecting dynamic shared memory implementation ... posix

running bootstrap script ... ok

performing post-bootstrap initialization ... creating cluster information ... 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.

Starting coordinator master.

Starting coordinator master coord1

Starting coordinator master coord2

2018-07-18 19:40:20.825 CST [17289] LOG: listening on IPv4 address "0.0.0.0", port 20004

2018-07-18 19:40:20.825 CST [17289] LOG: listening on IPv6 address "::", port 20004

2018-07-18 19:40:20.829 CST [17289] LOG: listening on Unix socket "/tmp/.s.PGSQL.20004"

2018-07-18 19:40:20.840 CST [17289] LOG: redirecting log output to logging collector process

2018-07-18 19:40:20.840 CST [17289] HINT: Future log output will appear in directory "pg_log".

2018-07-18 19:40:20.077 CST [16992] LOG: listening on IPv4 address "0.0.0.0", port 20005

2018-07-18 19:40:20.077 CST [16992] LOG: listening on IPv6 address "::", port 20005

2018-07-18 19:40:20.080 CST [16992] LOG: listening on Unix socket "/tmp/.s.PGSQL.20005"

2018-07-18 19:40:20.090 CST [16992] LOG: redirecting log output to logging collector process

2018-07-18 19:40:20.090 CST [16992] HINT: Future log output will appear in directory "pg_log".

Initialize all the coordinator slaves.

Initialize the coordinator slave coord1.

Initialize the coordinator slave coord2.

Starting all the coordinator slaves.

Starting coordinator slave coord1.

Starting coordinator slave coord2.

2018-07-18 19:40:29.552 CST [17176] LOG: listening on IPv4 address "0.0.0.0", port 20004

2018-07-18 19:40:29.552 CST [17176] LOG: listening on IPv6 address "::", port 20004

2018-07-18 19:40:29.556 CST [17176] LOG: listening on Unix socket "/tmp/.s.PGSQL.20004"

2018-07-18 19:40:29.577 CST [17176] LOG: redirecting log output to logging collector process

2018-07-18 19:40:29.577 CST [17176] HINT: Future log output will appear in directory "pg_log".

2018-07-18 19:40:30.353 CST [17450] LOG: listening on IPv4 address "0.0.0.0", port 20005

2018-07-18 19:40:30.353 CST [17450] LOG: listening on IPv6 address "::", port 20005

2018-07-18 19:40:30.369 CST [17450] LOG: listening on Unix socket "/tmp/.s.PGSQL.20005"

2018-07-18 19:40:30.382 CST [17450] LOG: redirecting log output to logging collector process

2018-07-18 19:40:30.382 CST [17450] HINT: Future log output will appear in directory "pg_log".

Done

Initialize all the datanode masters.

Initialize the datanode master node2.

Initialize the datanode master node3.

fixing permissions on existing directory /var/lib/pgxl/dn_master ... ok

Starting all the datanode masters.

Starting datanode master node2.

Starting datanode master node3.

2018-07-18 19:40:41.642 CST [17662] LOG: listening on IPv4 address "0.0.0.0", port 20008

2018-07-18 19:40:41.642 CST [17662] LOG: listening on IPv6 address "::", port 20008

2018-07-18 19:40:41.648 CST [17662] LOG: listening on Unix socket "/tmp/.s.PGSQL.20008"

2018-07-18 19:40:41.656 CST [17662] LOG: redirecting log output to logging collector process

2018-07-18 19:40:41.656 CST [17662] HINT: Future log output will appear in directory "pg_log".

2018-07-18 19:40:40.918 CST [17389] LOG: listening on IPv4 address "0.0.0.0", port 20009

2018-07-18 19:40:40.918 CST [17389] LOG: listening on IPv6 address "::", port 20009

2018-07-18 19:40:40.921 CST [17389] LOG: listening on Unix socket "/tmp/.s.PGSQL.20009"

2018-07-18 19:40:40.929 CST [17389] LOG: redirecting log output to logging collector process

2018-07-18 19:40:40.929 CST [17389] HINT: Future log output will appear in directory "pg_log".

Initialize all the datanode slaves.

Initialize datanode slave node2

Initialize datanode slave node3

Starting all the datanode slaves.

Starting datanode slave node2.

Starting datanode slave node3.

2018-07-18 19:40:49.702 CST [17550] LOG: listening on IPv4 address "0.0.0.0", port 20008

2018-07-18 19:40:49.702 CST [17550] LOG: listening on IPv6 address "::", port 20008

2018-07-18 19:40:49.712 CST [17550] LOG: listening on Unix socket "/tmp/.s.PGSQL.20008"

2018-07-18 19:40:49.725 CST [17550] LOG: redirecting log output to logging collector process

2018-07-18 19:40:49.725 CST [17550] HINT: Future log output will appear in directory "pg_log".

2018-07-18 19:40:50.462 CST [17823] LOG: listening on IPv4 address "0.0.0.0", port 20009

2018-07-18 19:40:50.462 CST [17823] LOG: listening on IPv6 address "::", port 20009

2018-07-18 19:40:50.471 CST [17823] LOG: listening on Unix socket "/tmp/.s.PGSQL.20009"

2018-07-18 19:40:50.483 CST [17823] LOG: redirecting log output to logging collector process

2018-07-18 19:40:50.483 CST [17823] HINT: Future log output will appear in directory "pg_log".

ALTER NODE coord1 WITH (HOST='node2', PORT=20004);

ALTER NODE

CREATE NODE coord2 WITH (TYPE='coordinator', HOST='node3', PORT=20005);

CREATE NODE

CREATE NODE node2 WITH (TYPE='datanode', HOST='node2', PORT=20008, PRIMARY, PREFERRED);

CREATE NODE node3 WITH (TYPE='datanode', HOST='node3', PORT=20009);

SELECT pgxc_pool_reload();

pgxc_pool_reload

------------------

(1 row)

CREATE NODE coord1 WITH (TYPE='coordinator', HOST='node2', PORT=20004);

ALTER NODE coord2 WITH (HOST='node3', PORT=20005);

CREATE NODE node2 WITH (TYPE='datanode', HOST='node2', PORT=20008, PRIMARY);

CREATE NODE node3 WITH (TYPE='datanode', HOST='node3', PORT=20009, PREFERRED);

EXECUTE DIRECT ON (node2) 'CREATE NODE coord1 WITH (TYPE=''coordinator'', HOST=''node2'', PORT=20004)';

EXECUTE DIRECT

EXECUTE DIRECT ON (node2) 'CREATE NODE coord2 WITH (TYPE=''coordinator'', HOST=''node3'', PORT=20005)';

EXECUTE DIRECT ON (node2) 'ALTER NODE node2 WITH (TYPE=''datanode'', HOST=''node2'', PORT=20008, PRIMARY, PREFERRED)';

EXECUTE DIRECT ON (node2) 'CREATE NODE node3 WITH (TYPE=''datanode'', HOST=''node3'', PORT=20009, PREFERRED)';

EXECUTE DIRECT ON (node2) 'SELECT pgxc_pool_reload()';

EXECUTE DIRECT ON (node3) 'CREATE NODE coord1 WITH (TYPE=''coordinator'', HOST=''node2'', PORT=20004)';

EXECUTE DIRECT ON (node3) 'CREATE NODE coord2 WITH (TYPE=''coordinator'', HOST=''node3'', PORT=20005)';

EXECUTE DIRECT ON (node3) 'CREATE NODE node2 WITH (TYPE=''datanode'', HOST=''node2'', PORT=20008, PRIMARY, PREFERRED)';

EXECUTE DIRECT ON (node3) 'ALTER NODE node3 WITH (TYPE=''datanode'', HOST=''node3'', PORT=20009, PREFERRED)';

EXECUTE DIRECT ON (node3) 'SELECT pgxc_pool_reload()';

---------------------

作者：peiybpeiyb

來源：CSDN

原文：

https://blog.csdn.net/ctypyb2002/article/details/81104535

centos 7.4 使用 pgxc_ctl 安裝與使用centos 7.4 使用 pgxc_ctl 安裝與使用

centos 7.4 使用 pgxc_ctl 安裝與使用

繼續閱讀

Linxu常用指令技巧彙總

httpd服務的部署、啟動、配置和簡單優化一、部署二、啟動三、配置檔案

配置網頁内容通路

手動安裝Intel network I217-LM網卡的Linux驅動

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

禁止ubuntu系統彈出報錯界面

Ubuntu Linux下Apache的配置檔案

ACS基本配置-權限等級管理

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

Git學習筆記5 merge沖突時二選一

Bugku-WEB-web33

vue-cli簡介（中文翻譯）

samba伺服器的功能

【Linux】UDP廣播封包接收速率問題

Linux裝置模型（中）之上層容器

PowerPC平台 Linux移植三