Postgres-XL集群部署与管理

最新推荐文章于 2024-09-03 17:58:59 发布

linuxchen

最新推荐文章于 2024-09-03 17:58:59 发布

阅读量1k

点赞数

1. https://www.jianshu.com/p/82aaf352b772

from http://blog.51cto.com/hsbxxl/1980423

Postgres-XL 集群详细创建步骤

主机列表和集群安装的角色分配

10.21.13.109 GTM

10.21.13.67 coordinator&datanode

10.21.13.60 datanode

2.创建postgres用户，这部分我使用ansible完成的用户创建，以及相关软件包的应用，节省劳动力（yum其实可以使用ansible自带的方式，所以我ansible写的比较业余）

ansible all -m shell -a "useradd postgres"

ansible all -m shell -a 'echo "postgres:postgres" | chpasswd'

ansible all -m shell -a "yum install -y flex bison readline-devel zlib-devel openjade docbook-style-dsssl gcc"

3. 在每个节点上下载软件，并解压缩

ansible all -m shell -a "wget https://www.postgres-xl.org/downloads/postgres-xl-9.5r1.6.tar.bz2"

ansible all -m shell -a "mv /root/postgres-xl-9.5r1.6.tar.bz2 /tmp; chown postgres:postgres /tmp/postgres-xl-9.5r1.6.tar.bz2"

ansible all -m shell -a "tar -jxvf /tmp/postgres-xl-9.5r1.6.tar.bz2 -C /home/postgres"

4. 全部节点都创建pgxc路径，用来存放postgres文件

1	`ansible` `all` `-m shell -a` `"su - postgres -c 'mkdir -p /home/postgres/pgxc/nodes/'"`

5. 修改每个节点的.bashrc文件，注意：修改/etc/profile是不生效的。

1 2	`su - postgres` `vi .bashrc`

添加如下内容：

export PGHOME=/home/postgres/pgxl

export PGUSER=postgres

export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH

export PATH=$PGHOME/bin:$PATH

使之生效：

1	`source ~/.bashrc`

6. 关闭防火墙或者放开相应的端口，这里我直接关闭了每个虚拟机的防火墙，并且重启它们：

1 2	`service iptables stop` `chkconfig iptables` `off` `--重启后生效`

7. 关闭SELinux：

查看SELinux状态：

1 2	`/usr/sbin/sestatus -v ##如果SELinux status参数为enabled即为开启状态` `SELinux status: enabled`

临时关闭（不用重启机器）：

1 2	`setenforce 0 ##设置SELinux 成为permissive模式` `##setenforce 1 设置SELinux 成为enforcing模式`

永久关闭：

1 2	`修改/etc/selinux/config 文件` `将SELINUX=enforcing改为SELINUX=disabled`

8. 到每一个节点执行下面的软件安装步骤，这个过程注意，如果中间有报错，需要解决到。例如确实gcc包之类的，不然后面的编译也不会成功的。

cd postgres-xl

./configure --prefix=/home/postgres/pgxl/

make

make install

cd contrib/

make

make instal

9. 执行pgxc_ctl命令来生成配置集群的模板文件：

./pgxc_ctl ---会提示Error说没有配置文件，忽略即可

PGXC prepare ---执行该命令将会生成一份配置文件模板

10. 根据模板修改对应的主机名，端口等信息，内容如下：

vi ~/pgxc_ctl/pgxc_ctl.conf

pgxcInstallDir=$HOME/pgxc

pgxcOwner=$USER

pgxcUser=$pgxcOwner

tmpDir=/tmp

localTmpDir=$tmpDir

configBackup=n

configBackupHost=pgxc-linker

configBackupDir=$HOME/pgxc

configBackupFile=pgxc_ctl.bak

gtmName=gtm

gtmMasterServer=10.21.13.109

gtmMasterPort=20001

gtmMasterDir=$HOME/pgxc/nodes/gtm

gtmExtraConfig=none

gtmMasterSpecificExtraConfig=none

gtmSlave=n

gtmProxyDir=$HOME/pgxc/nodes/gtm_pxy

gtmProxy=y

gtmProxyNames=(gtm_pxy1 gtm_pxy2)

gtmProxyServers=(10.21.13.67 10.21.13.60)

gtmProxyPorts=(20001 20001)

gtmProxyDirs=($gtmProxyDir $gtmProxyDir)

gtmPxyExtraConfig=none

gtmPxySpecificExtraConfig=(none none)

coordMasterDir=$HOME/pgxc/nodes/coord

coordSlaveDir=$HOME/pgxc/nodes/coord_slave

coordArchLogDir=$HOME/pgxc/nodes/coord_archlog

coordNames=(coord1)

coordPorts=(20004)

poolerPorts=(20010)

coordPgHbaEntries=(10.21.13.0/25) <<<<<<<<<<<<<<<<<<<<<<<<<非常重要

coordMasterServers=(10.21.13.67)

coordMasterDirs=($coordMasterDir)

coordMaxWALsernder=5

coordMaxWALSenders=($coordMaxWALsernder)

coordSlave=n <<<<<<<<<<<<<<<<没使用slave

coordSlaveSync=y

coordSlaveServers=(10.21.13.60 10.21.13.67)

coordSlavePorts=(20004 20005 20004 20005)

coordSlavePoolerPorts=(20010 20011 20010 20011)

coordSlaveDirs=($coordSlaveDir $coordSlaveDir)

coordArchLogDirs=($coordArchLogDir $coordArchLogDir)

coordExtraConfig=coordExtraConfig

cat > $coordExtraConfig <<EOF

log_destination = 'stderr'

logging_collector = on

log_directory = 'pg_log'

listen_addresses = '*'

max_connections = 100

EOF

coordSpecificExtraConfig=(none none)

coordExtraPgHba=none

coordSpecificExtraPgHba=(none none)

coordAdditionalSlaves=n

coordAdditionalSlaveSet=(cad1)

cad1_Sync=n

cad1_Servers=(10.21.13.67 10.21.13.60)

cad1_dir=$HOME/pgxc/nodes/coord_slave_cad1

cad1_Dirs=($cad1_dir $cad1_dir)

cad1_ArchLogDir=$HOME/pgxc/nodes/coord_archlog_cad1

cad1_ArchLogDirs=($cad1_ArchLogDir $cad1_ArchLogDir)

datanodeMasterDir=$HOME/pgxc/nodes/dn_master

datanodeSlaveDir=$HOME/pgxc/nodes/dn_slave

datanodeArchLogDir=$HOME/pgxc/nodes/datanode_archlog

primaryDatanode=datanode1

datanodeNames=(datanode1 datanode2)

datanodePorts=(20008 20008) # Master ports

datanodePoolerPorts=(20012 20012)

datanodePgHbaEntries=(10.21.13.0/25) <<<<<<<<<<<<<<<<<<<<<<<<<<<<<非常重要

datanodeMasterServers=(10.21.13.67 10.21.13.60)

datanodeMasterDirs=($datanodeMasterDir $datanodeMasterDir)

datanodeMaxWalSender=5

datanodeMaxWALSenders=($datanodeMaxWalSender $datanodeMaxWalSender )

datanodeSlave=n <<<<<<<<<<<<<<<<没使用slave

datanodeSlaveServers=(10.21.13.60 10.21.13.67)

datanodeSlavePorts=(20008 20008)

datanodeSlavePoolerPorts=(20012 20012)

datanodeSlaveSync=y

datanodeSlaveDirs=($datanodeSlaveDir $datanodeSlaveDir)

datanodeArchLogDirs=( $datanodeArchLogDir $datanodeArchLogDir )

datanodeExtraConfig=none

datanodeSpecificExtraConfig=(none none )

datanodeExtraPgHba=none

datanodeSpecificExtraPgHba=(none none)

datanodeAdditionalSlaves=n

walArchive=n <<<<<<<<<<<<<<<<不开wal归档

walArchiveSet=(war1 war2)

war1_source=(master)

wal1_source=(slave)

wal1_source=(additiona_coordinator_slave_set additional_datanode_slave_set)

war1_host=node10

war1_backupdir=$HOME/pgxc/backup_war1

wal2_source=(master)

war2_host=node11

war2_backupdir=$HOME/pgxc/backup_war2

11. 初始话命令，如果前面没有任何错误，这里就是最后一步了，PGXL会把全部的部署工作完成的。

pgxc_ctl -c pgxc_ctl.conf init all

完整输出如下：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

[postgres@ip-10-21-13-109 pgxc_ctl]$ pgxc_ctl -c pgxc_ctl.conf init all

/bin/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 /home/postgres/pgxc_ctl --configuration /home/postgres/pgxc_ctl/pgxc_ctl.conf

Finished reading configuration.

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

Current directory: /home/postgres/pgxc_ctl

Initialize GTM master

ERROR: target directory (/home/postgres/pgxc/nodes/gtm) exists and not empty. Skip GTM initilialization

1:3768231680:2017-11-09 19:35:02.983 CST -FATAL: lock file "gtm.pid" already exists

2:3768231680:2017-11-09 19:35:02.983 CST -HINT: Is another GTM (PID 14635) running in data directory "/home/postgres/pgxc/nodes/gtm"?

LOCATION: CreateLockFile, main.c:2099

waiting for server to shut down... done

server stopped

Done.

Start GTM master

server starting

Initialize all the gtm proxies.

Initializing gtm proxy gtm_pxy1.

Initializing gtm proxy gtm_pxy2.

waiting for server to shut down... done

server stopped

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 /home/postgres/pgxc/nodes/gtm_pxy ... ok

creating configuration files ... ok

Success.

waiting for server to shut down... done

server stopped

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 /home/postgres/pgxc/nodes/gtm_pxy ... ok

creating configuration files ... ok

Success.

Done.

Starting all the gtm proxies.

Starting gtm proxy gtm_pxy1.

Starting gtm proxy gtm_pxy2.

server starting

Done.

Initialize all the coordinator masters.

Initialize coordinator master coord1.

ERROR: target coordinator master coord1 is running now. Skip initilialization.

Done.

Starting coordinator master.

Starting coordinator master coord1

ERROR: target coordinator master coord1 is already running now. Skip initialization.

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 "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 /home/postgres/pgxc/nodes/dn_master ... 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 /home/postgres/pgxc/nodes/dn_master/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.

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 /home/postgres/pgxc/nodes/dn_master ... 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 /home/postgres/pgxc/nodes/dn_master/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.

Done.

Starting all the datanode masters.

Starting datanode master datanode1.

Starting datanode master datanode2.

LOG: redirecting log output to logging collector process

HINT: Future log output will appear in directory "pg_log".

LOG: redirecting log output to logging collector process

HINT: Future log output will appear in directory "pg_log".

Done.

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

ALTER NODE

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

ERROR: PGXC Node datanode1: object already defined

CREATE NODE datanode2 WITH (TYPE='datanode', HOST='10.21.13.60', PORT=20008);

ERROR: PGXC Node datanode2: object already defined

SELECT pgxc_pool_reload();

pgxc_pool_reload

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

t

(1 row)

Done.

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

EXECUTE DIRECT

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

EXECUTE DIRECT

EXECUTE DIRECT ON (datanode1) 'CREATE NODE datanode2 WITH (TYPE=''datanode'', HOST=''10.21.13.60'', PORT=20008)';

EXECUTE DIRECT

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

pgxc_pool_reload

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

t

(1 row)

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

EXECUTE DIRECT

EXECUTE DIRECT ON (datanode2) 'CREATE NODE datanode1 WITH (TYPE=''datanode'', HOST=''10.21.13.67'', PORT=20008, PRIMARY, PREFERRED)';

EXECUTE DIRECT

EXECUTE DIRECT ON (datanode2) 'ALTER NODE datanode2 WITH (TYPE=''datanode'', HOST=''10.21.13.60'', PORT=20008)';

EXECUTE DIRECT

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

pgxc_pool_reload

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

t

(1 row)

Done.

12. 验证可用性

在coordinator节点上，本地连接方式

1	`psql -p 20004`

远程用户连接方式

1	`psql -h 10.21.13.67 -p 20004 -U postgres`

postgres=# select * from pgxc_node;

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

datanode1 | D | 20008 | 10.21.13.67 | t | t | 888802358

datanode2 | D | 20008 | 10.21.13.60 | f | f | -905831925

coord1 | C | 20004 | 10.21.13.67 | f | f | 1885696643

13. 建表测试

postgres=# create table test(id integer,name varchar(10));

postgres=# insert into test(id,name) values(1,'test');

postgres=# insert into test(id,name) values(2,'test');

查询结果

postgres=# select * from test;

id | name

----+------

1 | test

2 | test

(2 rows)

问题汇总：

1. 如果登陆到数据库中，执行下面语句，发现只有coord的信息，说明并没有创建成功，

postgres=# select * from pgxc_node;

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

coord3 | C | 20004 | localhost | f | f | 1638403545

(1 row)

而问题是出在coordPgHbaEntries=(10.21.13.0/25)和datanodePgHbaEntries=(10.21.13.0/25)的访问权限控制上，一定要和实际的子网掩码匹配,不然各个datanode节点和coord节点间是无法正常通讯的。

由于没有注意各个细节，让这个问题困扰我一周时间。一直在找为什么datanode之间不能相互识别的原因，其实就是很小的一个点。

[postgres@ip-10-21-13-109 pgxc_ctl]$ ifconfig

eth0 Link encap:Ethernet HWaddr 02:57:E7:56:4C:70

inet addr:10.21.13.109 Bcast:10.21.13.127 Mask:255.255.255.128<<<<<<10.21.13.0/25

inet6 addr: fe80::57:e7ff:fe56:4c70/64 Scope:Link

UP BROADCAST RUNNING MULTICAST MTU:9001 Metric:1

RX packets:29500 errors:0 dropped:0 overruns:0 frame:0

TX packets:28538 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:1000

RX bytes:3113614 (2.9 MiB) TX bytes:4653853 (4.4 MiB)

Interrupt:160

2. 如果遇到建表，建库失败，也是遇到上面的访问控制权限问题。在部署完成之后，这个参数是在/home/postgres/pgxc/nodes/dn_master/pg_hba.conf和/home/postgres/pgxc/nodes/coord/pg_hba.conf中设置的。但是重新init，会覆盖掉这些文件的。

postgres=# create database test;

ERROR: Failed to get pooled connections

HINT: This may happen because one or more nodes are currently unreachable, either because of node or network failure.

Its also possible that the target node may have hit the connection limit or the pooler is configured with low connections.

Please check if all nodes are running fine and also review max_connections and max_pool_size configuration parameters

3. 有很多人的安装方式，是最后通过手动执行下面命令，添加coord和datanode，其实前面的pgxc_ctl.conf配置正确的话，脚本都是自动帮你完成的，没有必要人工干预

create node coord1 with (type=coordinator,host='c6702', port=20004);

create node coord2 with (type=coordinator,host='c6703', port=20005);

alter node coord3 with (type=coordinator,host='c6704', port=20004);

create node datanode1 with (type=datanode, host='c6702',port=20008);

create node datanode2 with (type=datanode, host='c6703',port=20008);

alter node datanode3 with (type=datanode, host='c6704',port=20008);

4. 第8步，make PGXL软件过程中，一定要仔细查看输出，有错误一定要纠正。不然后续会发现，有很多命令软件，都没有安装成功。

5.如果你在远程登陆postgres sql遇到如下问题

1 2	`[root@c6703 ~]# psql -h 10.21.13.67 -p 20004 -U postgres` `psql: FATAL:` `no` `pg_hba.conf entry` `for` `host` `"172.16.32.116",` `user` `"postgres",` `database` `"postgres"`

修改/home/postgres/pgxc/nodes/dn_master/pg_hba.conf和/home/postgres/pgxc/nodes/coord/pg_hba.conf中的访问控制权允许对应的IP有访问权限，就可以登陆了。注意，要修改全部的coordinator和datanode的pg_hba.conf配置文件才行。然后通过postgres用户执行reload命令，使配置文件生效。或者重启使配置生效。