原 Postgres-X2部署步骤

Postgre2015大象会,大家都很关注PostgreSQL的集群,目前,开发人员已经转向Postgres-X2,近期根据自己和同事部署xl的过程部署了一下Postgres-X2。本次部署试验是利用pgxc_ctl部署的,更加灵活的部署集群。

1、整体概括:

    一共四个节点,一个gtm,一个coordinator,两个datanode。

a. GTM节点
        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、准备工作(不特别指明,四个节点做相同的操作):

    a.编译安装pgx2,同时编译contrib。

./configure --prefix=/opt/pgx2
make; make install
cd contrib
make; make install

    b.建立用户postgres,将安装目录属主赋给postgres。

chown -R postgres:postgres pgx2

    c.配置ssh连接

[postgres@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     |
+-----------------+
[postgres@localhost~]#
cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys

vi /etc/hosts
192.168.238.129 localhost.localdomain

--分发密钥,gtm节点向其他节点分发
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.配置环境变量

[postgres@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
[postgres@localhost ~]$

建议:

在ssh连接时效率很慢,可以用ssh -v进行检测,这里就不做说明了。

修改/etc/ssh/sshd_config中的GSSAPIAuthentication和UseDNS为no,然后/etc/init.d/sshd restart就可提高ssh连接速度。

为了方便起见我将所有节点的iptables关闭,大家可自行配置。

3、部署节点

    a.配置pgxc_ctl.conf

--在/home/postgres/pgxc_ctl下

--conf内容
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 $datanodeMaxWALsernder)
datanodeSlave=n
primaryDatanode=datanode1
datanodeSpecificExtraConfig=(none none)
datanodeSpecificExtraPgHba=(none none)

    b.利用pgxc_ctl部署节点

pgxc_ctl init all

[postgres@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 /home/postgres/pgxc_ctl --configuration /home/postgres/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 "postgres".
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/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 "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/coord/coord1 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
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 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-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 "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/datanode1 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
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 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-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 "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/datanode2 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
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 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-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=5432);
ALTER NODE
CREATE NODE datanode1 WITH (TYPE='datanode', HOST='192.168.238.131', PORT=15432, PRIMARY);
CREATE NODE
CREATE NODE datanode2 WITH (TYPE='datanode', HOST='192.168.238.132', PORT=15432);
CREATE NODE
Done.

4、运行演示

[postgres@localhost ~]$ psql -h 192.168.238.130 -p 5432 -d postgres -U postgres
psql (PGXC 1.3devel, based on PG 9.4beta1)
Type "help" for help.

postgres=# create table test(id int, name text) distribute by replication;
CREATE TABLE
postgres=# insert into test values (1,'wang'),(2,'shuo');
INSERT 0 2
postgres=# select * from test;
 id | name 
----+------
  1 | wang
  2 | shuo
(2 rows)

postgres=# \q
[postgres@localhost ~]$ psql -h 192.168.238.131 -p 15432 -d postgres -U postgres 
psql (PGXC 1.3devel, based on PG 9.4beta1)
Type "help" for help.

postgres=# select * from test;
 id | name 
----+------
  1 | wang
  2 | shuo
(2 rows)

postgres=# \q
[postgres@localhost ~]$ psql -h 192.168.238.132 -p 15432 -d postgres -U postgres 
psql (PGXC 1.3devel, based on PG 9.4beta1)
Type "help" for help.

postgres=# select * from test;
 id | name 
----+------
  1 | wang
  2 | shuo
(2 rows)

postgres=#

总结:

相较于手动部署,利用pgxc_ctl部署效率以及正确率是非常高的,欢迎大家尝试。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏猿天地

ElasticSearch搜索引擎在SpringBoot中的实践

实验环境 ES版本:5.3.0 spring bt版本:1.5.9 首先当然需要安装好elastic search环境,最好再安装上可视化插件 elastics...

49050
来自专栏Java后端技术

Spring+SpringMvc+Mybatis框架集成搭建教程三(框架整合测试程序开发)

(2).在main文件夹下的java源文件夹下创建com.hafiz.www包,并在该包下依次创建:

9230
来自专栏我的博客

MySQL数据表生成Wiki格式数据字典

16630
来自专栏java初学

关于mybatis的思考(1)——mybatis的使用实例

15650
来自专栏Java开发

Spring MyBatis多数据源(同包)

创建基本的包 entity service dao 为了区分多数据源 一个用的是Mysql 一个是Oracle 方便测试,

37820
来自专栏Java学习123

查看Linux系统信息

296100
来自专栏公众号_薛勤的博客

SpringBoot集成Freemarker与Thymeleaf

3.模板使用 在templates文件夹下新建freemarker文件夹,然后在该文件夹下新建index.html

12430
来自专栏王亚昌的专栏

UNIX共享内存总结

    共享内存可以说是最有用的进程间通信方式,也是最快的IPC形式。两个不同进程A、B共享内存的意思是,同一块物理内存被映射到进程A、B各自的进程地址空间。进...

15030
来自专栏Pulsar-V

CTF随笔(一)

WEB01 XSS水题 直接提交poc吧 http://xxx.com/xss1.php?bug="></h2><h1+onclick="alert()">s...

61770
来自专栏Java架构师进阶

nginx性能优化及测试

处理器: Intel Xeon E5-2609 v2 @ 2.50GHz        *2 

17940

扫码关注云+社区

领取腾讯云代金券