文档章节

Postgres-X2部署步骤

PGSmith
 PGSmith
发布于 2015/11/27 16:30
字数 1817
阅读 3049
收藏 85
点赞 6
评论 4

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部署效率以及正确率是非常高的,欢迎大家尝试。

© 著作权归作者所有

共有 人打赏支持
PGSmith

PGSmith

粉丝 91
博文 54
码字总数 59729
作品 0
济南
后端工程师
加载中

评论(4)

javasql
javasql
支持!
廖君
廖君
[79]
李嘉图
李嘉图
要学习
YuanyuanL
YuanyuanL
79
容器开启数据服务之旅系列(一):Kubernetes如何解自建PostgreSQL运维之痛

摘要: 通过阿里云Kubernetes容器服务,开启你的数据服务之旅 (一)云上运维自建数据库之痛,使用容器服务自动恢复数据库postgresql实例 概述 本文为大家介绍一种容器化的数据服务 posgresq...

阿里云云栖社区
04/17
0
0
从oracle迁移带clob字段的表数据至postgresql

在oarcle的sql脚本中字段长度超过4000执行会有异常,而在postgresql中超过4000仍可以正常执行,产品同时支持多个数据库,如oracle和postgresql,在基础数据较多时,只能通过导出基础数据相关...

无知有趣
2014/09/26
0
0
PostgreSQL 安装步骤

前言 最近需要将数据库从MySql迁移到PostgreSQL上来,故学习了下postgres的安装,写本文记录下. @Author duangr @Website http://my.oschina.net/duangr/blog/181914 1.相关环境 Host Name IP...

一只小逛
2013/12/05
0
2
PostgreSQL在CentOS下的源码安装

下面主要是PostgreSQL在CentOS下的安装步骤,和Linux步骤基本类似。 1.环境: 操作系统:CentOS-6.2-x8664 虚拟机:Vmware-workstation 6.5 数据库:postgresql-9.1.3 工具: SecureCRT 5.1.2 2...

kenyon_君羊
2012/04/15
0
2
从代码层判断 pg_basebackup 是否已正常结束 - 暨改进建议

标签 PostgreSQL , pg_basebackup 背景 用户通常会使用pg_basebackup来对PostgreSQL实施在线备份,对于超级大的数据库实例,可能需要备份很久。 如果将任务分解来看,假设备份是任务流中的一...

德哥
06/21
0
0
Oracle DBA 增值 PostgreSQL,Greenplum 学习计划

标签 PostgreSQL , Oracle , Greenplum 背景 去O很大程度上是国家层面的战略考虑,比如斯诺登事件,最近贸易战的“中兴”事件,使得去O成为一个不可不做的事情。 但是去O喊了若干年,并没有真...

德哥
05/06
0
0
1 Ambari从Postgresql8.4升级到9.2

1.1 安装Postgresql9.2 1) 安装依赖 安装readline-devel.x8664 0:6.0-4.el6和ncurses-devel.x8664 0:5.7-3.20090208.el6 rpm -ivh ncurses-devel-5.7-3.20090208.el6.x86_64.rpm rpm -ivh re......

yntmdr
07/06
0
0
centos7部署posgresql和kong总结

  之前在macos系统测试安装psql和kong,但是实际环境中,大部分是部署在linux服务器上。下面记录了在centos7上部署postgresql和kong的总结以及遇到的一些问题的解决。 查看centos版本: $ ...

zhoujie0111
06/08
0
0
azure linux虚拟机openlogic_centos7.0搭建postgresql数据库

近日,需要用到postgresql数据库。 我搭建的环境为: azure平台,操作系统为azure平台自带的openlogic centos7.0。 搭建过程: 1,使用系统自带postgresql包。如果仅仅搭建postgresql数据库,...

longfirst
2014/11/02
0
0
CentOS 7.x里yum安装Odoo 8.0&9.0(OpenERP)

(总结)CentOS 7.x里yum安装Odoo 8.0&9.0(OpenERP) 最近想了解下合适中小企业使用的ERP系统,研究了几款主流的开源ERP系统以及了解下SAP、Oracle EBS等大型ERP系统,开源ERP里Odoo(以前叫...

李伟铭k
07/09
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

JAVA知识点随心记

1.Switch case具体的支持类型? Q:支持byte、short、char、int基本类型,枚举类型和String类型(JDK7以上支持),四种基本类型的包装类型也支持,但是原因在于触发了自动拆箱,将包装类型拆成了基本...

勤奋的蚂蚁
13分钟前
0
0
NoSQL

一、NoSQL介绍 NoSQL属于非关系型数据,mysql属于关系型数据库。 对于关系型数据库来说,是需要把数据存储到库、表、行、字段里,查询的时候根据条件一行一行地去匹配,当数据量非常大的时候...

人在艹木中
18分钟前
0
0
第17章MySQL主从配置

mysql安装总结 mysql主从准备工作: 准备两台机器,每台机器安装msyql服务,并启动mysql服务 mysql详细安装 1.首先下载二进制免编译的包,下载到/usr/local/src/目录下 2.解压压缩包 3.解压完...

Linux学习笔记
21分钟前
0
0
Redis高可用及分片集群

一、主从复制 使用异步复制 一个服务器可以有多个从服务器 从服务器也可以有自己的从服务器 复制功能不会阻塞主服务器 可以通过服务功能来上主服务器免于持久化操作,由从服务器去执行持久化...

Java大蜗牛
25分钟前
0
0
前端面试题汇总

最近在复习,准备找工作了,特此总结一下前端的相关知识。 1.获取浏览器URL中查询字符的参数: function getQuery(name){    var reg = new RegExp("(^|&)"+name+"=([^&]*)"(&|$));...

凛冬来袭
59分钟前
0
0
可持续发展的学习道路

与其要求别人,不如提升自己 内心渴望进步 经常做出改变现有模式,不断学习 寻找资源,整合资源,不断熟练这种模式 渠道很重要 先打开新世界的航路

狮子狗
今天
0
0
apollox-lua开源项目 示例codepen2

今天在示例上增加了几个功能, 首先添加js array的标准库。 所有js array的方法目前都支持了。 添加查看code模式。 点击查看code可以看到生成的lua代码。默认web模式需要把标准库连接进来, ...

钟元OSS
今天
0
0
javascript性能优化之避免重复工作

javascript最重要也最根本的性能优化标准之一是避免工作,避免工作又包括两点,第一,不做不必要的工作,第二,不做重复的已经完成的工作。第一部分可以通过代码重构完成,第二部分不做重复的...

老韭菜
今天
0
0
缓存穿透、并发和雪崩那些事

0 题记 缓存穿透、缓存并发和缓存雪崩是常见的由于并发量大而导致的缓存问题,本文讲解其产生原因和解决方案。 缓存穿透通常是由恶意攻击或者无意造成的;缓存并发是由设计不足造成的;缓存雪...

Java填坑之路
今天
1
0
项目jar包管理构建工具---Maven

一、what is Maven? 我们来寻找一下官网,里面介绍了maven到底是什么?下面一句话就有讲解到:Apache Maven is a software project management and comprehension tool. Based on the conc...

一看就喷亏的小猿
今天
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部