PostgreSQL集群方案探讨.docx

PostgreSQL集群方案探讨.docx

《PostgreSQL集群方案探讨.docx》由会员分享，可在线阅读，更多相关《PostgreSQL集群方案探讨.docx（20页珍藏版）》请在冰豆网上搜索。

PostgreSQL集群方案探讨

PostgreSQ集群方案探讨

1.集群方案探讨目的

现在的业务，虽然从实体上来看，存在多套数据库，但是，这多套数据库使用的主库+分库的模式，也就是说，所有的主体业务，都集中使用主库，分库主要用于存取低级别的原始数据。

对于主库来言,只有一套，存在单点隐患。

目前更紧迫的是，随着业务的扩充，单套主库已经无法承受住正常的业务使用，而这些数据库基本上都是运行在云平台上，扩充机器资源效果不明显，因此，极其需要探讨主库的集群方案，实现主数据库的负载均衡及热备方案，从而满足现有业务的正常使用。

基于上述目的，本文将探讨PostgreSQL数据库多种集群模式，并对这些模式的优缺点进行比较，结合当前的业务特点，选定一种方案,并具体对这种方案进行分析，最后，给出安装部署步骤以及后期故障的维护步骤。

2.PostgreSQL多种集群方案介绍

2.1.单数据库模式

APP应用

写读

单数据库模式，应用程序的读写都与这一个数据库进行操作。

22流复制模式

流复制模式，不需要额外增加软件，只需要在单数据库模式的基础上，再复制一份PostgreSQL数据库到另外的一台机器上，然后，对两台数据库进行参数配置，即可实现。

这两套数据库之间的数据，通过archive日志，后台自动同步。

对外部的应用程序而言，可以看作是两套数据库，需要根据业务需要，显式分别连接不同的数据库。

2.3.

流复制+PGPOOL模式

流复制+PGPOOL模式，需要首先已经具备流复制模式，在此基础上，再增加一个PGPOO组件。

应用程序都与PGPOO进行交互，由PGPOOL自动决定某次会话是连接到写库上还是读库上。

对外部的应用程序而言，可以看作是一套数据库。

24PGPOOL复制模式

APP应用

PGPOOL组件

PGPOO复制模式，需要具有完全相同的DB1和DB2,DB1与DB2是完全相互独立的两套数据库，他们之间没有数据流，只需要在PGPOOILS件上进行配置，应用程序与PGPOOI进行交互，写操作由PGPOOI分别调用DB1和DB2，读操作由PGPOOL根据策略，依次或并行调用DB1和DB2。

对外部的应用程序而言，可以看作是一套数据库。

2.5.灾难恢复

无论采用何种多数据库模式，一旦发生灾难后，要么写库不能操

作，要么写库正常工作造成两套数据库的数据不同步，在灾难解决后，都需要重新配置，将主库的数据重新同步到其他的机器上，否则会出现两边数据不一致，集群方案无法工作。

3.写数据库性能比较

性能压力测试环境描述：

创建一个6个普通字段的数据表，使用一个批量insert的程序，每次1万条，总共往数据库里写入500万条记录。

以下测试环境在相同的测试工具，相同的机器配置下进行的测

试。

单数据库是一套，多数据库是两套。

模式

写库效率

优点

缺点

单数据库

14245条/秒

写库效率最高

所有压力都集中在一个数据库上，存在单点隐患

流复制模式

13550条/秒

读写库分离，解决了单点隐患，同时，写库效率只是降低了5%,基本可以忽略。

应用程序需要改造，使读写数据连接不同的数据源，一旦写库出现故障，需要手工干预重新配置数据库，否则只有读库可以正常操作，写数据都会出错。

流复制+PGPOOL莫式

4332条/秒

应用程序无需改造，解决了单点隐患，自动实现读写分离

冋样存在流复制模式”的缺点，写数据的效率只有单库的30%

PGPOOL复制模式

1111条/秒

应用程序无需改造，彻底解决单点隐患问题

写数据的效率低下，只有单库的10%性能。

4.建议使用方案

从上面的性能压力测试数据可以看出，如果单纯的从效率影响来看，使用“流复制模式”是最好的方式，既解决了单点隐患问题，又没有影响效率。

但是，使用这个方案最大的问题就是需要修改应用程序，要增加一个数据源连接，然后分别根据不同的业务选择使用某一个数据源，增加了业务复杂度和开发工作量。

既然“流复制模式”有这么大的弊端，退而求其次，重新审视“流复制+PGPOOL模式”，这个方案最大的弊端就是写库效率只有原来的30%，结合目前的业务发现的性能压力瓶颈，主要集中在大量的数据读造成的压力（90%），而并不是由于写数据库造成的压力。

因此，可以使用“流复制+PGPOO摸式”这个方案。

以上测试的都是一主一从的多库，后来，再次测试了一下一主二从的3个数据库的效率，发现在写库方面：

1+2基本与1+1的效率相同。

因此，如果实施1+1方案后，还没有达到预期效果的话，可以实施1+2方案。

在进行多库部署时，强烈建议使用相同的操作系统版本，相同的软件目录和数据目录，否则，会需要走很多弯路。

5.流复制+PGPOOL模式特殊场景分析

5.1.特殊场景分析目的

由于“流复制+PGPOOL模式”使用的是多套数据库，因此，需要

分析单套与多套数据库的差异性进行分析，确保不要引入了多套数据

库影响业务的正常使用。

52写库与读库的分发机制分析

当使用流复制和热备的时候，确定哪个操作可以被发送到

主节点或备节点或者不能被发送到备节点，非常重要。

pgpool-ll

的流复制模式可以很好的处理这种情况。

根据PGPO0的官方文档，我们可以知道：

|这些操作只允许被发送到主节点

INSERT,UPDATE,DELETE,COPYFROM,TRUNCATE,CREATE,DROP,ALTER,

COMMENT

SELECT...FORSHARE|UPDATE

在事务隔离级另U为SERIALIZABLE的SELECT

比ROWEXCLUSIVEMODE更严厉的LOCK命令

一些事务相关命令：

BEGINREADWRITE,STARTTRANSACTIONREADWRITE

SETTRANSACTIONREADWRITE,SETSESSIONCHARACTERISTICS

ASTRANSACTIONREADWRITE

SETtransaction_read_only=off

两步提交命令：

PREPARETRANSACTION,COMMITPREPARED,ROLLBACK

PREPARED

LISTEN,UNLISTEN,NOTIFY

VACUUM

一些序列生成器操作函数（nextval和setval）

大对象建立命令

这些操作可以被发送到主节点和备节点。

如果启用了负载均衡，这些查询可以被发送到备节点。

但是，如果设置了delay_threshold且复制延迟大于这个值，则查询被发送到主节点。

SELECTnotlistedabove

COPYTO

DECLARE,FETCH,CLOSE

SHOW

|以下操作被同时发送到主节点和备节点

SET

DISCARD

DEALLOCATEALL

在一个显式的事务中：

启动事务的命令例如BEGIN只被发送到主节点。

接下来的SELECT和一些可以被发送到主节点或备节点的其他查询会在事务中执行或者在备节点中执行。

无法在备节点中执行的命令例如INSERT被发送到主节点。

在这些命令之后的命令，即使是SELECT也被发送到主节点。

这是因为这些SELECT语句可能需要立即查看INSERT的结果

这种行为一直持续到事务关闭或者终止。

在扩展协议中，在负载均衡模式中在分析查询时，有可能探测是否查询可以被发送到备节点。

规则和非扩展协议下相同。

例如，INSERT被发送到主节点。

接下来的bind，describe和execute也将被发送到主节点。

［注：

如果对SELECT语句的分析由于负载均衡被发送到备节点，然后一个DML语句，例如一

个INSERT，被发送到pgpool-ll，那么，被分析的SELECT必须在主节点上执行。

因此，我们会在主节点上重新分析这个SELECT语句。

］

最后，pgpool-II的分析认为有错误的查询将被发送到主节点。

根据以上的描述，我们基本上可以放心的使用，不需要担

心主从库出现顺序错乱问题。

当然，重要的前提是，我们必须

通过PGPOO访问数据库，而不能直接物理连接到主库或从库

中。

但是，还是有个问题，例如：

在select方法中，调用了一

个函数，而这个函数是向表中增删改数据的，这时，会出现问

题。

因为，select默认是通过分发策略操作的，有可能会分发

到从库上，但是，从库是不允许更改数据的。

因此，对于这类

操作，需要将函数添加到pgpool.conf的black_function_list中。

53PGPOOL单点隐患分析

“流复制+PGPOO模式”使用的是多套数据库，因此，数据库本身不存在单点隐患。

但是，由于引入了PGPOO组件，这个组件存在单点隐患问题。

从理论上讲，可以通过PGPOOI集群的模式或者通过HA的模式，来解决这个隐患。

本来计划通过KeepAlived方式，使用虚拟IP,在读写库上部署来实现的，后来考虑到，无论怎么部署，只要某台机器宕机了，数据库的操作就受到了影响，无法继续提供正常的数据服务了，此时，就算PGP001能正常工作也没有根本上解决问题。

因此，PGPOOL勺HA没有意义。

同时，考虑到数据库是运行在云平台上的，云平台有一套HA机制，而且，目前最紧迫的是性能压力问题，而不是容灾问题。

因此，目前先忽略此问题。

54PGPOOL连接池

在单数据库时，直接通过数据库的max_connections参数来配置并行连接。

“流复制+PGPOOL模式”使用的是多套数据库，应用程序不直接与实体数据打交道，因此，需要正确配置PGPOOL中有关连接方面的参数。

这里边的参数很多，需要根据业务情况分别调整（也就是属于上线的调优过程），换句话说，数据库单改多之后，会有一个适应期（一般1周左右）。

5.5.文件空间占用情况

在单数据库时，是没有开放归档日志的功能的。

采用多数据库时，是通过归档日志来进行后台数据同步的。

因此，文件空间会比原来大，按512个归档日志计算的话，需要10G空间，因此，需要确保磁盘有足够的空间（最少20G空间）。

6.安装部署

6.1.单数据库安装

本文档重点描述的是在单数据库的基础上实现多库方案，同时，各个现场都已经具备了单数据库，并且，后续的操作都直接假设数据库中已经存在了数据，同时，假设PostgreSQL数据库软件安装在

/home/postgres/soft/pgsql目录下，数据文件存放在/home/postgres/data目录下，假设主库IP：

10.2.6.108，备库IP：

10.2.6.110，linux主机的用户名为：

postgres。

具体的单数据库安装过程，本节跳过。

6.2.PGPOOL安装配置（在线操作）

在进行安装配置时，需要停止数据库。

因此，会产生业务中断问题。

尽量的将能提前做的工作先做好。

6.2.1.PGPOOL安装（这里采用编译安装的方法）

下载pgpool源码，存放到主机的某个目录下，例如：

/home/postgres/install/目录下。

源程序下载地址：

。

然后，进行如下操作：

tarzxvfpgpool-II-3.3.4.tar.gz

cdpgpool-II-3.3.4

mkdir-p/home/postgres/soft/pgpool#用于存放最终的pgpool可执行软件

./configure--prefix=/home/postgres/soft/pgpool-with-pgsql=/home/postgres/soft/pgsql

make

makeinstall

cdsql

make

makeinstall

6.2.2.PGPOOLg己置

1.配置PGPOO环境变量到profile中，增加如下两行：

exportPGPOOLHOME=/home/postgres/soft/pgpool

exportPATH=$PGPOOLHOME/bin:

$PATH

2.安装pgpool_regclass,pgpool_recovery

运行psqlxxxx数据库名字，例如：

icpdb），语句和输出类似如下：

icpdb=#createextensionpgpool_regclass;

CREATEEXTENSION

icpdb=#CREATEEXTENSIONpgpool_recovery;

CREATEEXTENSION

icpdb=#\df

Listoffunctiamji

Sch&na|30-ajne|Resultdatatype|Argumentdat^atypes|Type

public|pg:

pDal_pgct1.|boolean|actiontest,stop.mode|namal

public|pgp口sl._ree口very|b口olean|富打削・豊reiiot*_hflsltextieefnote_data_direstoryteat|naejial

public|pgpooL_remote=start|booleanIreikote^hosttest,renotB_data_ditractcrytext|notnal

public|pepooLfvlIdi_zlog|teKt|arciv¥_dii：

ttst|nomal

（4rows）

icpdb=#\q

3.在数据库里增加一个专用的流复制健康检查的用户，例如，用

户名sr_user，密码sr_pass此值在后面的配置参数中有用到。

icpdb=#CREATEUSERsr_userWITHPASSWORD'sr_pass：

4.通过pg_md5命令，在PGPOOL中增加可以访问数据库的用户。

例如：

pg_md5-m-usr_usersr_pass

注意：

这条命令sr_user是用户名，sr_pass是密码。

通过使用

此命令后，会在pgpool/etc/pool_passwd文件中增加或更新一

条记录。

执行这条命令时，要求确保数据库中真实的存在此用

户和正确的密码，当然也可以先执行这条命令后，再到数据库

中创建对等的用户。

包括后续在数据库中每增加一个用户，都

需要使用此命令操作一下，否则，无法通过PGPOOI登录。

5.soft/pgpool/etc/pool_hba.conf配置

和PostgreSQL中使用的pg_hba.conf文件类似，PGPOOL使

用一个称之为"pool_hba.conf"的配置文件来支持类似的客户端认证功能。

将soft/pgpool/etc/pool_hba.conf.sample复制为pool_hba.conf,然

后编辑pool_hba.conf文件，增加如下内容:

hostallall10.2.6.108/32md5

hostallall10.2.6.110/32md5

hostreplicationpostgres10.2.6.108/32md5

hostreplicationpostgres10.2.6.110/32md5

6.增加PGPOO运行的目录

mkdir-p/home/postgres/soft/pgpool/rundir/tmp

mkdir-p/home/postgres/soft/pgpool/rundir/piddir

mkdir-p/home/postgres/soft/pgpool/rundir/oiddir

mkdir-p/home/postgres/soft/pgpool/rundir/logdir

7.soft/pgpool/etc/pgpool.conf配置

pgpool.conf，然后编辑

将soft/pgpool/etc/pgpool.conf.sample复制为

pgpool.conf文件,

修改如下内容:

listen_addresses='*'port=1521口，同时，把主库的原来的端口改成一个其他的端口，否则，

pcp_port=9898

#这是管理pgpool的管理端口，直接用缺省的就可以

socket_dir='/home/postgres/soft/pgpool/rundir/tmp'pcp_socket_dir='/home/postgres/soft/pgpool/rundir/tmp'

pid_file_name='/home/postgres/soft/pgpool/rundir/piddir/pgpool.pid'

memqcache_oiddir='/home/postgres/soft/pgpool/rundir/oiddir'logdir='/home/postgres/soft/pgpool/rundir/logdir'

然后，再增加如下内容:

backend_hostname0='10.26108'

backend_port0=5432

backend_weight0=1

backend_hostname1='10.2.6.110'

backend_port1=5432

backend_weight1=3

#主库IP

#主库的端口，现场缺省是1521

#主库在多台数据库查询的权重值

#备库1的IP

#备库1的端口，建议与主库一样

#备库1在多台数据库查询的权重值

#如果主备机器性能差不多的话，权重可以考虑1:

2--4

#如果有多个备库，依次写xxxx2=...（序号要记得改）

6.3.主库配置（在线操作）

1.配置pg_hba.conf，增加如下几行（这几行的意思：

对于与

10.2.6.108,110的IP,需要输入密码才可以访问和进行流复制：

hostallall10.2.6.108/32md5

hostallall10.2.6.110/32md5

hostreplicationpostgres10.2.6.108/32md5

hostreplicationpostgres10.2.6.110/32md5

2.建立归档日志的存放目录

mkdir-p/home/postgres/data/archive

chown-Rpostgres:

postgres/home/postgres/data/archive

3.将postgresql.conf复制为postgresql.conf.slave注意：

是没有

修改前复制，因为主库与备库的参数不能相同，有冲突。

4.配置postgresql.conf，增加如下几行在末尾：

wal」evel=hot_standby#在主库上设置此值

archive_mode=on#打开归档日志

archive_command='test!

-f/home/postgres/data/archive/archive_active||cp-i%p/home/postgres/data/archive/%f

archive_timeout=60#如果日志更新太少，导致长时间没有归档时，最少60秒同步

max_wal_senders=5#最大5个备库

wal_keep_segments=512#最多保存512个归档日志（一个归档日志16M）wal_sender_timeout=60s#设置流复制主机发送数据的超时时间

5.修改postgresql.conf中主库的端口，从1521调整为5432端口。

修改此文件中port=1521为port=5432。

6.提前配置好备库的相关文件，修改postgresql.conf.slave

增加如下配置：

hot_standby=on

max_standby_streaming_delay=30s#数据流备份的最大延迟时间wal_receiver_status_interval=10s#多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间

hot_standby_feedback=on#如果有错误的数据复制，是否向主进行反馈

7.提前配置好备库的相关文件，新增recovery.conf.slave文件（与

postgresql.conf.slave相同的目录）文件内容如下：

（如果端口不是5432的话，也要相应的修改）

restore_command='cp/home/postgres/data/archive/%f%p'

standby_mode='on'

primary_conninfo='host=10.2.6.108port=5432user=postgrespassword=zznode'recovery_target_timeline='latest'

6.4.从库实施前准备（在线操作）

1.创建跟主机一样的postgres组和postgres用户

2.准备好跟主机一样的profile文件

3.准备好跟主机一样的posgres的软件根目录和data根目录（此时

这些目录下都是空的，只需要准备这些根目录即可）

6.5.从库部署实施（离线操作）

1.停掉除数据库之外的所有应用，也就是说，要求没有应用程序与数据库的连接

2.重启主库（这个步骤是为了让在线时修改的配置参数生效）

3.在主库上执行如下linux命令：

（表示进入数据备份状态，如果端口不是5432的话，需要显式-pxxx来指明）

psql-p5432-c"SELECTpg_start_backup（'label',true）"

4.将主库上的pgsql软件和数据文件远程copy到备机上，在主机上执行：

scp-rp/home/postgres/softpostgres@10.2.6.110:

/home/postgres/

scp-rp/home/postgres/datapostgres@10.2.6.110:

/home/postgres/

5.在主库上执行如下linux命令：

（表示结束数据备份状态）

psql-p5432-c"SELECTpg_stop_backup（）"

6.在备机上，将/home/postgres/data/postmaster.pid文件删掉（这个是数据库的进程ID文件，是从主库那边远程copy过来的）

7.在备机上将postgresql.conf.slave复制成postgresql.