PostgreSQL 最佳实践 - 读写分离-CSDN博客

本文介绍了PostgreSQL数据库的读写分离最佳实践，特别是使用pgpool-II中间件配合流复制技术。重点讲解了pgpool-II的replication和stream模式，推荐使用stream模式，因为它利用了PostgreSQL的流复制特性。文中还详细阐述了pgpool-II的简单配置过程，包括设置流复制环境、安装pgpool、配置参数、创建检查用户以及解决常见连接错误。通过读写分离，可以有效提高PostgreSQL的并发处理能力和系统性能。

摘要由CSDN通过智能技术生成

背景

一直以来PostgreSQL数据库在scale up和scale out的方向都走得比较靠前，例如

单元化技术
oleg postgrespro的 PostgreSQL cluster，在分布式事务性能提升，选举算法方面的贡献非常大。
https://github.com/postgrespro/postgres_cluster

sim 他们的udr, bdr已经趋于成熟。
https://2ndquadrant.com/en/resources/bdr/
分片技术
10年前postgresql就非常成熟了，那就是skype开源的plproxy。
https://git.postgresql.org/gitweb/?p=skytools.git;a=summary
《阿里云 ApsaraDB for PostgreSQL 试用报告 - 2 教你RDS PG的水平分库》
https://yq.aliyun.com/articles/203

《阿里云 ApsaraDB for PostgreSQL 试用报告 - 3 水平分库 vs 单机性能》
https://yq.aliyun.com/articles/204

《阿里云 ApsaraDB for PostgreSQL 试用报告 - 4 水平分库之节点扩展》
https://yq.aliyun.com/articles/205

《PostgreSQL 最佳实践 - 水平分库(基于plproxy)》
https://yq.aliyun.com/articles/59372
NewSQL技术
这个技术PostgreSQL走得非常靠前，现在Postgres-XL, Postgres-XC, citusdb, FDW based几种技术，都在趋于成熟。
以PostgreSQL社区一贯的作风，加入到内核的功能一定是成熟的，例如fdw based sharding，已经加入到内核了。在9.6有了非常大的进步，包括sort , where , join的下推，这些查询在满足条件时都会在分片节点执行。
读写分离
目前读写分离最成熟的当属pgpool-II中间件，如果用户应用程序不希望自己来选择目标节点，可以使用pgpool-II来作为中间件使用。
多核并行
目前唯一支持多核并行的开源数据库，在某些大查询上面使用性能提升非常明显。
用户可以使用PG作为小型的数据仓库来使用，因为PG还支持机器学习库madlib，支持plpython, plr等服务端编程语言，完全可以支撑小型的分析需求(TB级别)。

《开源数据库 PostgreSQL 攻克并行计算难题》
https://yq.aliyun.com/articles/44655

《PostgreSQL 并行计算 - 助力实时精准营销应用》
https://yq.aliyun.com/articles/44649

《PostgreSQL 9.6 并行计算优化器算法浅析》
https://yq.aliyun.com/articles/59180

《PostgreSQL 并行计算在 xfs, ext4 下的表现》
https://yq.aliyun.com/articles/53985
GPU并行
PostgreSQL开放的接口(custom scan provider)，使得用户非常方便的可以扩展它的数据访问接口，GPU并行计算也是这么来实现的。

pgstrom 的benchmark来看，在OLAP领域性能提升非常的明显。

使用GPU的运算能力，高带宽，提升OLAP性能。
https://wiki.postgresql.org/wiki/PGStrom
https://github.com/pg-strom/devel

但是在国内，这些技术的推广非常少，知道的人本身就不多，更不要说非常了解这些技术的人。

本文主要针对读写分离这块，讲一下如何使用pgpool-II中间件以及PG的流复制技术实现PG的读写分离。

pgpool-II 三种负载均衡模式

pgpool 的 load balance可以基于三种模式来实现.

1. replication

2. stream

3. slony-I

replication模式

这三种模式最不推荐的是replication模式 , 它是由pgpool来控制的前端数据分发的复制功能, 例如一个插入语句, 将会在所有的backend database执行, 如果backend database的返回消息不一致, 则会选择degenerate少的部分backend(少数服从多数的意思), 或者是整个事务失败(由参数replication_stop_on_mismatch, failover_if_affected_tuples_mismatch 控制).

这其实是个坑, 因为pgpool未提及差异数据的修复.

所以极度不推荐使用.

类似产品有 http://www.continuent.com/

stream模式

stream模式使用了PostgreSQL自身提供的流复制特性. 相对来说是最完善的. 所以推荐使用.

不管哪种模式, load balance都要考虑哪些SQL可以被分发到standby节点, 目前只有只读查询可以被分发到standby节点, 并且还需要考虑函数的影响, 因为有些函数是会修改数据库的, 例如nextval, 或者自定义的函数里面包含了select以外的查询.

还需要考虑SELECT在事务中的情况, 对于事务中的查询不建议在standby错开节点.

另外还有基于游标的更新也是需要注意的.

pgpool的处理意见是, 包含函数的查询都只分发给master, 除非配置了white_function_list .

简单的配置过程

1. 配置流复制环境, 一主一备, 端口分别为1999,2000.

停库

pg93@db-172-16-3-39-> pg_ctl stop -m fast  
waiting for server to shut down.... done  
server stopped

配置pg_hba.conf

g93@db-172-16-3-39-> cd $PGDATA  
pg93@db-172-16-3-39-> ll  
total 4.0K  
drwx------ 16 pg93 pg93 4.0K May 13 11:16 pg_root  
cd pg_root  
vi pg_hba.conf  
host all all 0.0.0.0/0 md5  
host replication postgres 172.16.3.39/32 md5

创建备库
拷贝数据文件

pg93@db-172-16-3-39-> cp -r pg_root pg_root_2000  
pg93@db-172-16-3-39-> mv pg_root pg_root_1999  
pg93@db-172-16-3-39-> ll  
total 8.0K  
drwx------ 16 pg93 pg93 4.0K May 13 11:16 pg_root_1999  
drwx------ 16 pg93 pg93 4.0K May 13 11:17 pg_root_2000

配置postgresql.conf, 修改监听端口

pg93@db-172-16-3-39-> cd pg_root_2000  
pg93@db-172-16-3-39-> vi postgresql.conf   
port = 2000  

pg93@db-172-16-3-39-> grep "^[a-z]" postgresql.conf   
listen_addresses = '0.0.0.0'            # what IP address(es) to listen on;  
port = 2000     # (change requires restart)  
max_connections = 500                   # (change requires restart)  
unix_socket_directories = '.'   # comma-separated list of directories  
unix_socket_permissions = 0700          # begin with 0 to use octal notation  
password_encryption = on  
tcp_keepalives_idle = 60                # TCP_KEEPIDLE, in seconds;  
tcp_keepalives_interval = 10            # TCP_KEEPINTVL, in seconds;  
tcp_keepalives_count = 10               # TCP_KEEPCNT;  
shared_buffers = 2048MB                 # min 128kB  
maintenance_work_mem = 512MB            # min 1MB  
max_stack_depth = 8MB                   # min 100kB  
shared_preload_libraries = 'pg_stat_statements'         # (change requires restart)  
vacuum_cost_delay = 10                  # 0-100 milliseconds  
vacuum_cost_limit = 10000               # 1-10000 credits  
bgwriter_delay = 10ms                   # 10-10000ms between rounds  
wal_level = hot_standby                 # minimal, archive, or hot_standby  
synchronous_commit = off                # synchronization level;  
wal_sync_method = fdatasync             # the default is the first option  
wal_buffers = 16384kB                   # min 32kB, -1 sets based on shared_buffers  
wal_writer_delay = 10ms         # 1-10000 milliseconds  
checkpoint_segments = 256               # in logfile segments, min 1, 16MB each  
archive_mode = on               # allows archiving to be done  
archive_command = '/bin/date'           # command to use to archive a logfile segment  
max_wal_senders = 32            # max number of walsender processes  
wal_keep_segments = 512         # in logfile segments, 16MB each; 0 disables  
hot_standby = on                        # "on" allows queries during recovery  
max_standby_archive_delay = 300s        # max delay before canceling queries  
max_standby_streaming_delay = 300s      # max delay before canceling queries  
wal_receiver_status_interval = 1s       # send replies at least this often  
hot_standby_feedback = on               # send info from standby to prevent  
random_page_cost = 1.5                  # same scale as above  
effective_cache_size = 8192MB  
log_destination = 'csvlog'              # Valid values are combinations of  
logging_collector = on          # Enable capturing of stderr and csvlog  
log_di