devbox
2023面试高手
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Postgresql HA（PG-POOL）安装_postgresql 13.13 disable_load_balance_on_write = '

作者：2023面试高手 | 2024-03-01 11:05:31

赞

踩

postgresql 13.13 disable_load_balance_on_write = 'trans_transaction!

目录

二、安装步骤

2.1 用户配置

2.1.1 配置专属用户

2.2 环境依赖

2.3 源码下载

2.4 源码安装

2.5 初始化

2.5.1 初始化数据库

2.6 服务管理

2.5.1 启/停数据库

2.5.1 刷新配置

三、主备同步

3.1 主库配置 

3.2 备库初始化（同步）

3.3 验证

四、高可用（HA）

4.1 安装PG-POOL

4.2 PG-POOL Master配置

4.2.1 pgpool.conf配置

4.2.2 pcp.conf配置(选择性配置)

4.2.3 pool_hba.conf配置(选择性配置)

4.2.4 failover.sh配置

4.2.5 follow_master.sh配置

4.2.6 restore_1st.sh配置

4.2 PG-POOL Slave配置

4.3 服务管理

4.4 节点管理

附录

SSH 免密通信安装

调优参数(postgresql.conf)

常见问题

开发注意事项

一、服务器配置

服务器IPCPU内存磁盘备注
172.18.2.1816核64G1000GPostgres主节点、PG-POOL主节点
172.18.2.2016核64G1000G备库
172.18.2.3316核64G1000G备库
172.18.2.100虚拟IP，PG_POOL VPI

Postgres版本：11.5

Pg-Pool版本：4.1

二、安装步骤

2.1 用户配置

2.1.1 配置专属用户

  1. #1. 创建用户组
  2. groupadd postgres
  3. #2. 创建用户
  4. useradd -m -g postgres postgres
  5. #3. 为用户增加密码(密码)
  6. passwd postgres

2.2 环境依赖

编译PostgreSQL需要下列软件包：

  • 要求GNU make版本3.80或以上；其他的make程序或更老的GNU make版本将不会工作（GNU make有时以名字gmake安装）。要测试GNU make可以输入：

    make --version

  • 你需要一个ISO/ANSI C 编译器（至少是 C89兼容的）。我们推荐使用最近版本的GCC，不过，众所周知的是PostgreSQL可以利用许多不同厂商的不同编译器进行编译。

  • 除了gzip和bzip2之外，我们还需要tar来解包源代码发布。

  • 默认时将自动使用GNU Readline库。它允许psql（PostgreSQL的命令行 SQL 解释器）记住你输入的每一个命令并且允许你使用箭头键来找回和编辑之前的命令。如果你不想用它，那么你必需给configure声明--without-readline选项。作为一种可选方案，你常常可以使用 BSD许可证的libedit库，它最初是在NetBSD上开发的。libedit库是GNU Readline兼容的， 如果没有发现libreadline或者configure使用了--with-libedit-preferred选项，都会使用这个库。如果你使用的是一个基于包的 Linux 发布，那么要注意你需要readline和readline-devel两个包，特别是如果这两个包在你的版本里是分开的时候。

  • 默认的时候将使用zlib压缩库。 如果你不想使用它，那么你必须给configure声明--without-zlib选项。使用这个选项关闭了在pg_dump和pg_restore中对压缩归档的支持。

  • 依赖zlib-devel、readline-devel

    1. yum install -y zlib-devel
    2. yum install -y readline-devel

2.3 源码下载

PostgreSQL 11.5 源代码可以从官方网站 https://www.postgresql.org/download/的下载区中获得。将得到一个名为postgresql-11.5.tar.gz或postgresql-11.5.tar.bz2的文件

2.4 源码安装

  1. #1. 创建并修改安装目录权限
  2. mkdir -p /opt/software/postgresql
  3. chown -R postgres:postgres /opt/software/postgresql
  4. chmod 750 -R /opt/software/postgresql
  5. #2. 配置数据目录
  6. mkdir -p /data/postgresql/data
  7. chown -R postgres:postgres /data/postgresql
  8. chmod 750 -R /data/postgresql
  9. #3.进入/opt/software文件夹，解压源码
  10. tar -zxvf postgresql-11.5.tar.gz
  11. #4.进入源码目录/opt/software/postgresql-11.5 执行配置脚本
  12. ./configure --prefix=/opt/software/postgresql
  13. #5.编译
  14. make
  15. #6. 回归测试（这条命令不能以 root 运行；请在非特权用户下运行该命令，可跳过）
  16. make check
  17. #7. 安装文件
  18. make install
  19. #8.添加依赖包环境变量，修改/etc/profile
  20. LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/software/postgresql/lib
  21. export LD_LIBRARY_PATH
  22. #9.添加安装路径，修改/etc/profile
  23. PG_HOME=/opt/software/postgresql
  24. PATH=$PG_HOME/bin:$PATH
  25. export PATH
  26. #10. 刷新配置
  27. source /etc/profile
  28. #11. 创建socket目录
  29. mkdir -p /data/postgresql/socket

2.5 初始化

2.5.1 初始化数据库

  1. #1. 数据库初始化需要先切换到postgres用户
  2. su postgres
  3. #2. 执行初始化语句
  4. initdb -E UTF-8 -D /data/postgresql/data --locale=en_US.UTF-8 -U postgres -W
  5. #3. 修改数据库配置，允许远程连接，以及端口、连接数等配置
  6. vim /data/postgresql/data/postgresql.conf
  7. #4. 添加远程连接信息
  8. vim /data/postgresql/data/pg_hba.conf
  9. 加入以下配置
  10. host    all             all             0.0.0.0/0               md5

2.6 服务管理

2.5.1 启/停数据库

  1. #1. 数据库启动需要先切换到postgres用户
  2. su postgres
  3. #2. 启动命令
  4. pg_ctl -D /data/postgresql/data -l /data/postgresql/logfile start
  5. #3. 重启命令
  6. pg_ctl -D /data/postgresql/data -l /data/postgresql/logfile restart
  7. #3. 停止命令
  8. pg_ctl -D /data/postgresql/data -l /data/postgresql/logfile stop

2.5.1 刷新配置

  1. #1. 数据库启动需要先切换到postgres用户
  2. su postgres
  3. #2. 刷新命令
  4. /opt/software/postgresql/bin/pg_ctl reload -D /data/postgresql/data

三、主备同步

User NamePasswordDetail
replrep流复制账号
pgpoolpgpool健康检查、复制时延检查账号
postgrespostgres在线恢复账号

3.1 主库配置 

  1. #1. 连接主库（172.18.2.18）
  2. psql -U postgres -h 127.0.0.1 -d postgres
  3. #2.添加数据同步账号：rep
  4. CREATE USER rep WITH PASSWORD '密码' REPLICATION;
  5. CREATE ROLE pgpool WITH PASSWORD '密码' LOGIN;
  6. #3 授予所有权限
  7. GRANT ALL PRIVILEGES ON all tables in schema public TO rep;
  8. GRANT ALL PRIVILEGES ON all tables in schema public TO pgpool;
  9. #3. 创建WAL日志归档路径
  10. mkdir -p /data/postgresql/archivedir
  11. #4. 修改数据库配置，允许远程连接，以及端口、连接数等配置
  12. vim /data/postgresql/data/postgresql.conf
  13. listen_addresses = '*'
  14. archive_mode = on
  15. archive_command = 'gzip < %p > /data/postgresql/data/archivedir/%f.gz'
  16. max_wal_senders = 20
  17. max_replication_slots = 20
  18. wal_level = replica
  19. hot_standby = on
  20. wal_log_hints = on
  21. #5. 添加流复制和在线恢复等账号的免密登录
  22. vim /home/postgres/.pgpass
  23. 172.18.2.18:5432:replication:rep:密码
  24. 172.18.2.20:5432:replication:rep:密码
  25. 172.18.2.33:5432:replication:rep:密码
  26. 172.18.2.18:5432:postgres:postgres:密码
  27. 172.18.2.20:5432:postgres:postgres:密码
  28. 172.18.2.33:5432:postgres:postgres:密码
  29. chmod 600  /home/postgres/.pgpass
  30. #4. 添加远程连接信息
  31. vim /data/postgresql/data/pg_hba.conf
  32. 加入以下配置
  33. host    replication     rep      0.0.0.0/0       trust 

3.2 备库初始化（同步）

  1. #1.备用节点执行主库的安装步骤，参考第二节：安装步骤，执行2.1~2.4（可以选择直接把安装后的文件使用scp直接copy过来）
  2. #2. 切换账号postgres
  3. su postgres
  4. #3. 同步主库配置及数据
  5. #执行命名对应参数说明：
  6. #-h  启动的主库数据库地址               -p  主库数据库端口
  7. #-U  流复制用户                        -w  不使用密码验证
  8. #-Fp 备份输出正常的数据库目录           -Xs 使用流复制的方式进行复制
  9. #-Pv 输出复制过程的详细信息             -R  为备库创建recovery.conf文件
  10. #-D  指定创建的备库的数据库目录
  11. pg_basebackup -h 172.18.2.18 -p 5432 -U rep  -Fp -Xs -Pv -R -D /data/postgresql/data
  12. #4. 修改recovery.conf 加入：
  13. restore_command = 'scp postgresql@172.18.2.18:/data/postgresql/data/archivedir/%f.gz /data/postgresql/data/archivedir/ && gunzip < /data/postgresql/data/archivedir/%f.gz > %p'
  14. #5. 启动服务
  15. pg_ctl -D /data/postgresql/data -l /data/postgresql/logfile start

3.3 验证

  1. #1. 查看主备信息 备库是t，主库是f。
  2. select pg_is_in_recovery();

四、高可用（HA）

4.1 安装PG-POOL

  1. #1. 解压pgpool-II-4.1.0.tar.gz至/opt/software
  2. tar -zxvf pgpool-II-4.1.0.tar.gz -C /opt/software
  3. #2. 编译，进入解压后的文件目录，配置安装信息
  4. cd /opt/software/pgpool-II-4.1.0
  5. #参数说明
  6. #配置pgpool的安装路径：--prefix=/opt/software/pgpool-2/
  7. #配置pg的安装路径：--with-pgsql=/opt/software/postgresql
  8. ./configure --prefix=/opt/software/pgpool-2/ --with-pgsql=/opt/software/postgresql
  9. #3. 编译、安装
  10. make
  11. make install
  12. #4. 编译安装在线恢复组件
  13. cd /opt/software/pgpool-II-4.1.0/src/sql/pgpool-recovery
  14. make
  15. make install
  16. # 创建恢复模板
  17. su postgres
  18. psql -f pgpool-recovery.sql template1
  19. # 修改postgres.conf添加以下内容
  20. pgpool.pg_ctl = '/usr/local/pgsql/bin/pg_ctl'
  21. #5. 配置安装路径，修改/etc/profile
  22. PG_POOL_HOME=/opt/software/pgpool-2/
  23. PATH=$PG_POOL_HOME/bin:$PATH
  24. #6. 刷新配置
  25. source /etc/profile
  26. #7. 新建文件路径：
  27. mkdir -p /opt/software/pgpool-2/run/pgpool
  28. mkdir -p /opt/software/pgpool-2/script 
  29. mkdir -p /opt/software/pgpool-2/log/pgpool
  30. #8. 修改目录权限
  31. chown -R postgresql:postgresql /opt/software/pgpool-2

4.2 PG-POOL Master配置

4.2.1 pgpool.conf配置

  1. # 基础配置
  2. pid_file_name = '/opt/software/pgpool-2/run/pgpool/pgpool.pid' # pid 文件位置， 如果不配置有默认的
  3. logdir = '/opt/software/pgpool-2/log/pgpool'                   # status 文件存储位置
  4. # 通用
  5. listen_addresses = '*'
  6. port = 9999
  7. socket_dir='/opt/software/pgpool-2/run/pgpool' #存储PG-POOl连接信息
  8. pcp_listen_addresses = '*'
  9. pcp_port = 9898
  10. pcp_socket_dir='/opt/software/pgpool-2/run/pgpool' #存储PCP连接信息
  11.  
  12. num_init_children=32 #PG-POOL 客户端连接数、默认32，超过该连接数，新的连接将被阻塞，进入等待队列，直到有连接被关闭,等待队列请求最大数与net.core.somaxconn有关
  13. listen_backlog_multiplier=100 #单个PG-POOL进程允许前端请求队列的最大值，允许的最大连接数=num_init_children*listen_backlog_multiplier
  14. reserved_connections=0 #PG-POOL 保留连接数 默认为0，表示不做保留，当此项大于0时，等待队列失效，最大连接数=num_init_children-reserved_connections，超过最大连接数的连接请求将被拒绝
  15.  
  16. enable_pool_hba=off #默认为false，此项设置为true时，将启用pool_hba.conf中的用户认证，添加用户认证需要修改pool_hba.conf并刷新配置：pgpool reload
  17. pool_passwd='' #配置账户密码文件路径，默认为pool_passwd，配置''时，关闭使用文件配置
  18.  
  19. allow_clear_text_frontend_auth=true #如果PostgreSQL后端服务器需要md5或SCRAM认证才能进行某些用户的身份验证，但该用户的密码不在"pool_passwd"文件中，那么启用allow_clear_text_frontend_auth将允许PG-POOL使用明文密码进行认证,默认为false
  20.  
  21. authentication_timeout=60 # 认证超时时间，默认60s,设置为0表示禁用超时
  22. # 后台数据库链接信息配置，PGPOOL连接PG服务器
  23. backend_hostname0 = '172.18.2.18'           # 数据库信息 PG-POOL 使用该连接访问PG数据库
  24. backend_port0 = 5432						# 数据库端口
  25. backend_weight0 = 1                         # 这个权重和后面负载比例相关
  26. backend_data_directory0 = '/data/postgresql/data'	#用于在线恢复
  27. backend_flag0 = 'ALLOW_TO_FAILOVER'
  28. backend_application_name0='master218'      #对应postgresql.conf的application_name
  29.  
  30. backend_hostname1 = '172.18.2.20'           # 数据库信息 PG-POOL 使用该连接访问PG数据库
  31. backend_port1 = 5432						# 数据库端口
  32. backend_weight1 = 1                         # 这个权重和后面负载比例相关
  33. backend_data_directory1 = '/data/postgresql/data'	#用于在线恢复
  34. backend_flag1 = 'ALLOW_TO_FAILOVER'
  35. backend_application_name0='slave220'      #对应postgresql.conf的application_name
  36.  
  37. backend_hostname2 = '172.18.2.33'           # 数据库信息 PG-POOL 使用该连接访问PG数据库
  38. backend_port2 = 5432						# 数据库端口
  39. backend_weight2 = 1                         # 这个权重和后面负载比例相关
  40. backend_data_directory2 = '/data/postgresql/data'	#用于在线恢复
  41. backend_flag2 = 'ALLOW_TO_FAILOVER'
  42. backend_application_name0='slave233'      #对应postgresql.conf的application_name
  43.  
  44. connection_cache=on 						#PG-POOL连接数据库缓存的连接数，如果设置为off，所有连接都不会缓存
  45. max_pool=20 								#单个PG-POOL进程允许的最大缓存连接数，如果一个请求是相同的数据库、相同的用户、相同的参数（SQL）会使用缓存的连接，反之创建一个新的连接。因此连接后台数据库的连接数可能达到num_init_children*max_pool
  46.  
  47. # 流复制相关配置
  48. replication_mode = off                      # 复制模式关闭,复制模式和主从模式互斥
  49. load_balance_mode = on                      # 负载均衡打开
  50. master_slave_mode = on                      # 主从打开
  51. master_slave_sub_mode = 'stream'            # 主从之间模式为流传输stream
  52. disable_load_balance_on_write = 'transaction' 			# 是否关闭写人后负载均衡，此项设置为transaction时，如果写入后立即查询一行，则查询操作会发送到主节点，当此项设置为off时，查询操作会进行负载均衡，随机发送到一个节点进行查询，若数据同步还未完成，此时可能导致读取的数据未更新
  53. statement_level_load_balance = on 			#设置为 on 时，将为每个读取查询决定负载平衡节点。 当设置为 off 时，负载均衡节点在会话开始时间决定，直到会话结束才会改变。 例如，在使用连接池的应用中，对后端服务器保持连接打开状态，因为会话可能会保持很长时间，所以直到会话结束，负载均衡节点才会发生变化。 在此类应用程序中，启用 statement_level_load_balance 时，可以决定每个查询的负载平衡节点，而不是每个会话。 默认为关闭。
  54. sr_check_period = 10                         # 流复制检查相关配置
  55. sr_check_user = 'pgpool'
  56. sr_check_password = ''
  57. sr_check_database = 'wnos'
  58. delay_threshold=10737418240							#指定备用服务器上相对于主服务器的复制延迟的最大容忍级别（以 WAL 字节为单位）。 如果延迟超过此配置级别，即使启用了 load_balance_mode，Pgpool-II 也会停止向备用服务器发送 SELECT 查询并开始将所有内容路由到主服务器，直到备用服务器赶上主服务器。 将此参数设置为 0 将禁用延迟检查。 每个 sr_check_period 执行此延迟阈值检查。 默认值为 0。
  59. log_standby_delay = 'if_over_threshold' 		#指定何时记录复制延迟。none:不记录 'always'：检查一次记录一次 'if_over_threshold'：超过了阈值
  60.  
  61. # 数据库运行状况检查，以便Pgpool-II执行故障转移: 数据库的主备切换，可以为每个PG服务器单独配置，对应前面配置的backend_hostname 例如：health_check_period0=30 无数字后缀，默认为全部服务器应用该配置
  62. health_check_period = 20                    # Health check period, Disabled (0) by default 
  63. health_check_timeout = 20                   # 健康检查的超时时间,0 永不超时
  64. health_check_user = 'pgpool'              # 健康检查的用户
  65. health_check_password = ''          # 健康检查的用户密码
  66. health_check_database = 'postgres'          # 健康检查的数据库
  67. health_check_max_retries=3 					#指定在健康检查失败时放弃和启动故障转移之前要执行的最大重试次数。默认为0
  68. health_check_retry_delay=10 				#健康检查重试间隔
  69. connect_timeout=10000						# 使用connect() 进行系统调用，调用超时的时间，该参数同样于建立连接池超时时间（ms）,默认10000ms
  70.  
  71. # 故障后处理, 为了当postgresql数据库挂掉之后执行相应的策略,故障转移顺序根据backend_hostname的后缀有关，例如backend_hostname0出故障会优先转移到backend_hostname1而非backend_hostname2
  72. # 这个脚本时放在pgpool的目录下, 确切的说是由pgpool执行脚本来维护集群中数据库的状态
  73. failover_command = '/opt/software/pgpool-2/script/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'  #指定在 PostgreSQL 后端节点断开(宕机)时要运行的用户命令
  74. # failback_command= 						#指定当 PostgreSQL 后端节点附加到 Pgpool-II 时要运行的用户命令。
  75. follow_master_command = 'opt/software/pgpool-2/script/follow_master.sh %d %h %p %D %m %H %M %P %r %R'                # 2台服务器不配置， 如果使用3台PostgreSQL服务器，则需要指定follow_master_command在主节点故障转移上的故障转移后运行。如果有两个PostgreSQL服务器，则无需follow_master_command设置。
  76. failover_on_backend_error=on 				# 当设置为 on 时，Pgpool-II 将 PostgreSQL 后端连接上的读/写错误视为后端节点故障，并在断开当前会话后触发该节点上的故障转移。 当此项设置为 off 时，Pgpool-II 仅在出现此类错误时报告错误并断开会话
  77. search_primary_node_timeout = 300 			#指定发生故障转移时搜索主节点的最长时间（以秒为单位）。 如果在此配置时间内没有找到主节点，Pgpool-II 将放弃寻找主节点。 默认值为 300，将此参数设置为 0 意味着永远尝试。
  78. detach_false_primary = on 					#断开失效的主节点，避免脑裂
  79. auto_failback=true 							#设置为 on 时，如果节点状态为 down 但流式复制正常工作，则备用节点将自动进行故障恢复。 
  80. auto_failback_interval=60 					#指定自动故障回复的执行间隔的最短时间（以秒为单位）。 在上一次自动故障恢复之后的指定时间过去之前，不会执行下一次自动故障恢复。 例如，当 Pgpool-II 频繁检测到后端因为网络错误而关闭时，您可以通过将此参数设置为足够大的值来避免重复故障转移和故障回复。 默认值为 60。将此参数设置为 0 表示自动故障回复不等待。
  81.  
  82. use_watchdog = on                           # 激活看门狗配置
  83. wd_hostname = '172.18.2.18'                      # 当前主机(也可使用IP)
  84. wd_port = 9000                              # 工作端口
  85.  
  86. # 虚拟IP指定
  87. delegate_IP = '172.18.2.100'				# 此IP作为虚拟IP，需要在网段内不存在
  88. if_cmd_path = '/sbin'                       # 如果if_up_cmd, if_down_cmd 以/开头, 忽略此配置
  89. # 命令中的`eth0` 请根据自己机器上ip addr 实际的网卡名称进行修改
  90. # 当前节点启动指定虚拟IP的命令
  91. if_up_cmd = '/sbin/ip addr add $_IP_$/24 dev eth0 label eth0:0'
  92. # 当前节点指定关闭虚拟IP的命令
  93. if_down_cmd = '/sbin/ip addr del $_IP_$/24 dev eth0'
  94. # 指定用于在虚拟 IP 切换后发送 ARP 请求的命令
  95. arping_cmd = '/usr/sbin/arping -U $_IP_$ -w 1 -I eth0'
  96. # watchdog 健康检查
  97. wd_heartbeat_port = 9694                    # 健康检查端口
  98. wd_heartbeat_keepalive = 2
  99. wd_heartbeat_deadtime = 30
  100. # 其他机器地址配置(多台请增加配置)
  101. heartbeat_destination0 = '172.18.2.20'
  102. heartbeat_destination_port0 = 9694
  103. heartbeat_device0 = 'eth0'
  104. heartbeat_destination1 = '172.18.2.33'
  105. heartbeat_destination_port1 = 9694
  106. heartbeat_device1 = 'eth0'
  107.  
  108. # 其他pgpgool节点链接信息(多台请增加配置)
  109. other_pgpool_hostname0 = '172.18.2.20'            # 其他节点地址
  110. other_pgpool_port0 = 9999
  111. other_wd_port0 = 9000                       # 其他节点watchdof 端口
  112. other_pgpool_hostname1 = '172.18.2.33'            # 其他节点地址
  113. other_pgpool_port1 = 9999
  114. other_wd_port1 = 9000                       # 其他节点watchdof 端口
  115.  
  116. # watchdog 发生故障后, 处理的相关配置(宕机, pgpool进程终止)
  117. # 当某个节点故障后, 
  118. failover_when_quorum_exists = on
  119. failover_require_consensus = on
  120. allow_multiple_failover_requests_from_node = on
  121. enable_consensus_with_half_votes = on
  122.  
  123. # 在线恢复(master 恢复后自动变为备库)此配置将在多个pgpool-ii 节点时无效
  124. recovery_user = 'postgres'
  125. recovery_password = ''
  126. recovery_1st_stage_command = 'recovery_1st_stage'      # 在线恢复第一阶段执行的脚本，这个脚本时放在postgresql数据目录下的
  127. #recovery_2nd_stage_command = ''  	 # 在线恢复第二阶段执行的脚本，这个脚本时放在postgresql数据目录下的
  128. recovery_timeout=3600 		#如果在线恢复未在此时间内完成，则指定以秒为单位取消在线恢复的超时时间。 由于Pgpool-II在第二阶段在线恢复时不接受连接，因此该参数可用于取消在线恢复以管理在线恢复期间的服务停机时间。
  129. client_idle_limit_in_recovery = 1800 #指定在联机恢复期间，如果自上次查询以来客户端保持空闲，则断开客户端的时间（以秒为单位）。 client_idle_limit_in_recovery 与client_idle_limit 类似，但只在在线恢复的第二阶段生效。此项值需要小于recovery_timeout
  130.  
  131.  
  132. #缓存
  133. memory_cache_enabled=on # 默认关闭，内存查询缓存可用于 Pgpool-II 的所有模式。 内存查询缓存保留 SELECT 结果并重用结果。 当底层表被更新时，相应的缓存条目将被删除（如果 memqcache_auto_cache_invalidation 开启。该参数默认开启），因此不需要重新启动 Pgpool-II。
  134. memqcache_method = 'shmem' 	#指定用于缓存的存储类型'shmem'：使用共享的内存 'memcached'：使用缓存 默认：shmem
  135. memqcache_expire = 1800	#以秒为单位指定查询缓存的生命周期。 默认值为 0。这意味着缓存不会过期并且缓存保持有效直到表更新。
  136. memqcache_auto_cache_invalidation = on #动态清理缓存
  137. memqcache_maxcache = 10240	#指定要缓存的 SELECT 查询结果的最大字节数。 Pgpool-II 不会缓存数据大小大于该值的结果。 此值应该小于等于：memqcache_cache_block_size当由于大小限制而拒绝缓存数据时，将会打印LOG日志
  138. #white_memqcache_table_list= 	#缓存白名单,多个之间用,分割 此参数仅针对视图和中间结果集
  139. #black_memqcache_table_list=	#缓存黑名单,多个之间用,分割 配置的表将不会被缓存
  140. memqcache_total_size = 50000000			# 缓存最大空间
  141. memqcache_max_num_cache = 1000000 	# 指定缓存条目的数量。 这用于定义缓存管理空间的大小。推荐缓存空间=memqcache_max_num_cache*48
  142. memqcache_cache_block_size = 10240		#指定缓存块大小。 Pgpool-II 使用按 memqcache_cache_block_size 块排列的缓存内存。 SELECT 结果被打包到块中并且必须适合单个块。 并且大于 memqcache_cache_block_size 的结果不被缓存。memqcache_cache_block_size 必须至少设置为 512。
  143. memqcache_oiddir = '/opt/software/pgpool-2/run/oiddir' #memqcache_oiddir 目录包含数据库的子目录。 目录名称是数据库的 OID。 此外，每个数据库目录都包含 SELECT 语句使用的每个表的文件。 同样，文件名是表的 OID。 这些文件包含指向查询缓存的指针，用作删除缓存的键。

4.2.2 pcp.conf配置(选择性配置)

  1. #1. 对rep、postgres用户的密码分别进行MD5加密并添加到pcp.conf
  2. echo 'pgpool:'`pg_md5 密码` >> /opt/software/pgpool-2/etc/pcp.conf

4.2.3 pool_hba.conf配置(选择性配置)

  1. #1. 复制pool_hba.conf.sample 重命名为pool_hba.conf
  2. cp pool_hba.conf.sample pool_hba.conf
  3. #2. 追加配置
  4. host    all         pgpool           0.0.0.0/0          md5
  5. host    all         postgres         0.0.0.0/0          md5

4.2.4 failover.sh配置

  1. #1. 创建文件failover.sh
  2. cp /opt/software/pgpool-2/etc/failover.sh.sample /opt/software/pgpool-2/script/failover.sh
  3. #2.修改PGHOME
  4. PGHOME=/opt/software/postgresql
  5. #3. 修改权限
  6. chown postgres:postgres /opt/software/pgpool-2/script/failover.sh

4.2.5 follow_master.sh配置

  1. #1. 创建文件follow_master.sh
  2. cp /opt/software/pgpool-2/etc/follow_master.sh.sample /opt/software/pgpool-2/script/follow_master.sh
  3. #2.修改以下配置
  4. PGHOME=/opt/software/postgresql
  5. ARCHIVEDIR=/data/postgresql/data/archivedir
  6. REPLUSER=rep
  7. PCP_USER=postgres
  8. PGPOOL_PATH=/opt/software/pgpool-2
  9. PCP_PORT=9898
  10. #3. 修改权限
  11. chown postgres:postgres /opt/software/pgpool-2/script/follow_master.sh
  12. #4. 添加PG免密登录
  13. echo '172.18.2.18:9898:pgpool:密码' >> /home/postgres/.pcppass
  14. echo '172.18.2.20:9898:pgpool:密码' >> /home/postgres/.pcppass
  15. echo '172.18.2.33:9898:pgpool:密码' >> /home/postgres/.pcppass
  16. chmod 600 /home/postgres/.pcppass
  17. chown postgres:postgres /home/postgres/.pcppass

4.2.6 restore_1st.sh配置

  1. #1. 创建文件recovery_1st_stage、pgpool_remote_start
  2. cp -p /opt/software/pgpool-2/etc/recovery_1st_stage.sample /data/postgresql/data/recovery_1st_stage
  3. cp -p /opt/software/pgpool-2/etc/pgpool_remote_start.sample /data/postgresql/data/pgpool_remote_start
  4. chown postgres:postgres /data/postgresql/data/recovery_1st_stage
  5. chown postgres:postgres /data/postgresql/data/pgpool_remote_start
  6. #2. 修改recovery_1st_stage
  7. PGHOME=/opt/software/postgresql
  8. ARCHIVEDIR=/data/postgresql/archivedir
  9. REPLUSER=rep
  10. #修改pgpool_remote_start
  11. PGHOME=/opt/software/postgresql

4.2 PG-POOL Slave配置

  1. #1.使用root账号拷贝安装文件
  2. scp -r root@172.18.2.18:/opt/software/pgpool-2 /opt/software/
  3. #2. 修改目录权限
  4. chown -R postgres:postgres /opt/software/pgpool-2
  5. #3. 切换postgres账号
  6. su postgres
  7. #4. 拷贝配置文件
  8. scp -r postgres@172.18.2.18:/home/postgres/.pgpass /home/postgres/.pgpass
  9. scp -r postgres@172.18.2.18:/data/postgres/data/recovery_1st_stage /data/postgresql/data/
  10. scp -r postgres@172.18.2.18:/data/postgres/data/pgpool_remote_start /data/postgresql/data/
  11. #5. 修改pgpool.conf
  12. use_watchdog = on                           # 激活看门狗配置
  13. wd_hostname = '172.18.2.20'                      # 当前主机(也可使用IP)
  14. wd_port = 9000                              # 工作端口
  15. heartbeat_destination0 = '172.18.2.18' #从节点换成主节点的
  16. heartbeat_destination_port0 = 9694 #从节点换成主节点的
  17. heartbeat_device0 = 'eth0' #从节点换成主节点的
  18. # 其他pgpgool节点链接信息(多台请增加配置)
  19. other_pgpool_hostname0 = '172.18.2.20'            # 其他节点地址
  20. other_pgpool_port0 = 9999
  21. other_wd_port0 = 9000

4.3 服务管理

  1. #1. 启动（pgpool启动需要root权限）
  2. pgpool -n -d -D > /opt/software/pgpool-2/log/pgpool/pgpool.log 2>&1 &     # 有debug日志
  3. pgpool -n -D > /opt/software/pgpool-2/log/pgpool/pgpool.log 2>&1 &        # 无debug日志
  4. #2. 停止
  5. pgpool -m fast stop

4.4 节点管理

  1. #1. 查看节点状态
  2. show pool_nodes;

附录

SSH 免密通信安装

  1. #1. 查看是否安装了ssh服务
  2. rpm -qa | grep openssh
  3. #2. 生成key,一直回车即可
  4. ssh-keygen -t rsa
  5. #3. 发送秘钥到对应的主机
  6. ssh-copy-id 主机

调优参数(postgresql.conf)

PostgreSQL性能调优重要的配置参数

选项默认值说明是否优化原因
max_connections100允许客户端连接的最大数目是根据实际使用情况调整
hot_standby_feedbackoffSpecifies whether or not a hot standby will send feedback to the primary or upstream standby about queries currently executing on the standby. This parameter can be used to eliminate query cancels caused by cleanup records, but can cause database bloat on the primary for some workloads. Feedback messages will not be sent more frequently than once per wal_receiver_status_interval. The default value is off. This parameter can only be set in the postgresql.conf file or on the server command line.是on
wal_receiver_timeout60备库日志接收超时时间（s）是受网络影响，超时会造成同步失败，建议设置为5min
wal_sender_timeout60主库日志发送超时时间(s)是受网络影响，超时会造成同步失败，建议设置为5min
max_wal_size1GB主库日志发送最大字节数是过小会产生多个片段文件，过大容易造成超时，配合wal_receiver_timeout和wal_sender_timeout调整
min_wal_size80MB主库日志发送最小字节数是
fsyncon强制把数据同步更新到磁盘是因为系统的IO压力很大，为了更好的测试其他配置的影响，把改参数改为off
shared_buffers24MB决定有多少内存可以被PostgreSQL用于缓存数据（推荐内存的1/4)是在IO压力很大的情况下，提高该值可以减少IO
work_mem1MB使内部排序和一些复杂的查询都在这个buffer中完成是有助提高排序等操作的速度，并且减低IO
effective_cache_size128MB优化器假设一个查询可以用的最大内存，和shared_buffers无关（推荐内存的1/2)是设置稍大，优化器更倾向使用索引扫描而不是顺序扫描
maintenance_work_mem16MB这里定义的内存只是被VACUUM等耗费资源较多的命令调用时使用是把该值调大，能加快命令的执行
wal_buffer768kB日志缓存区的大小是可以降低IO，如果遇上比较多的并发短事务，应该和commit_delay一起用
checkpoint_segments3设置wal log的最大数量数（一个log的大小为16M）是默认的48M的缓存是一个严重的瓶颈，基本上都要设置为10以上
checkpoint_completion_target0.5表示checkpoint的完成时间要在两个checkpoint间隔时间的N%内完成是能降低平均写入的开销
commit_delay0事务提交后，日志写到wal log上到wal_buffer写入到磁盘的时间间隔。需要配合commit_sibling是能够一次写入多个事务，减少IO，提高性能
commit_siblings5设置触发commit_delay的并发事务数，根据并发事务多少来配置是减少IO，提高性能

常见问题

  • centos7启动报错，命令缺少沾滞位的关系

  1. 2021-08-19 14:16:50: pid 22259: WARNING:  checking setuid bit of if_up_cmd
  2. 2021-08-19 14:16:50: pid 22259: DETAIL:  ifup[/sbin/ip] doesn't have setuid bit
  3. 2021-08-19 14:16:50: pid 22259: WARNING:  checking setuid bit of if_down_cmd
  4. 2021-08-19 14:16:50: pid 22259: DETAIL:  ifdown[/sbin/ip] doesn't have setuid bit

        原因：该命令缺少沾滞位的关系(centos7)

        解决方案：

  1. cd /sbin/
  2. chmod +s ifup
  3. chmod +s ip
  4. chmod +s arping
  • 同步数据报错：requested WAL segment * has already been removed

        原因：未配置unix_socket_directories目录，该目录默认在tmp目录下，指定服务器要侦听来自客户端应用程序的连接的Unix域套接字的目录。 列出由逗号分隔的多个目录可以创建多个套接字。 条目之间的空白被忽略; 如果您需要在名称中包含空格或逗号，请用双引号括住目录名称。 一个空值指定不侦听任何Unix域套接字，在这种情况下，只有TCP / IP套接字可用于连接到服务器。/tmp目录的定时清理，会断开socket从而导致系统判定为文件已传输，从而归档wal，导致pg_wal下的分段文件被删除

        解决办法：修改postgresql.conf 配置unix_socket_directories到外部目录，示例：unix_socket_directories=

  • PG-POOL 启动后，主节点未生成虚拟VIP

    原因1：虚拟VIP IP被占用 解决办法：更换虚拟VIP的IP

    原因2：启动账号无root权限 解决办法：添加入sudoers 或者使用root启动

  • opt/software/postgresql/bin/psql template1 -c "CREATE EXTENSION pgpool_recovery" 报错

    1. psql: could not connect to server: No such file or directory
    2. 	Is the server running locally and accepting
    3. 	connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

    原因1：postgres端口非5432 解决办法：命令添加参数指定Postgres端口：-p 端口

    原因2：修改了unix_socket_directories 解决办法：连接数据库执行一下sql脚本：

    CREATE EXTENSION pgpool_recovery; 
\dx+ pgpool_recovery;
  • 故障转移问题

        故障转移和在线恢复是通过postgres远程命令将某个备库（使用PG-POOL实际上固定的，跟pgpool.conf文件中配置的顺序有关）提升为主库、然后其他备库包括恢复后变为备库的旧主库，从新的主库拉取数据，然后重启，所以相关故障转移脚本可以自行编写，注意的是：若直接使用官方给的故障转移脚本，但相关安装、配置路径与默认路径不一致时，请逐个核查脚本文件内容的文件路径，例如：.pgpass路径，本文中，没有描述这一块，各位可根据实际配置的路径去做修改，便于更好的理解故障转移的过程。

开发注意事项

While using Streaming replication and Hot Standby, it is important to determine which query can be sent to the primary or the standby, and which one should not be sent to the standby. Pgpool-II's Streaming Replication mode carefully takes care of this.

We distinguish which query should be sent to which node by looking at the query itself.

  • These queries should be sent to the primary node only

    • INSERT, UPDATE, DELETE, COPY FROM, TRUNCATE, CREATE, DROP, ALTER, COMMENT

    • SELECT ... FOR SHARE | UPDATE

    • SELECT in transaction isolation level SERIALIZABLE

    • LOCK command more strict than ROW EXCLUSIVE MODE

    • DECLARE, FETCH, CLOSE

    • SHOW

    • Some transactional commands:

      • BEGIN READ WRITE, START TRANSACTION READ WRITE

      • SET TRANSACTION READ WRITE, SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE

      • SET transaction_read_only = off

    • Two phase commit commands: PREPARE TRANSACTION, COMMIT PREPARED, ROLLBACK PREPARED

    • LISTEN, UNLISTEN, NOTIFY

    • VACUUM

    • Some sequence functions (nextval and setval)

    • Large objects creation commands

    • Multi-statement queries (multiple SQL commands on single line)

  • These queries can be sent to both the primary node and the standby node. If load balancing is enabled, these types of queries can be sent to the standby node. However, if delay_threshold is set and the replication delay is higher than delay_threshold, queries are sent to the primary node.

    • SELECT not listed above

    • COPY TO STDOUT

    • EXPLAIN

    • EXPLAIN ANALYZE and the query is SELECT not including writing functions

    • SHOW

  • These queries are sent to both the primary node and the standby node

    • SET

    • DISCARD

    • DEALLOCATE ALL

In an explicit transaction:

  • Transaction starting commands such as BEGIN are sent to both the primary node and the standby node.

  • Following SELECT and some other queries that can be sent to both primary or standby are executed in the transaction or on the standby node.

  • Commands which cannot be executed on the standby such as INSERT are sent to the primary. After one of these commands, even SELECTs are sent to the primary node, This is because these SELECTs might want to see the result of an INSERT immediately. This behavior continues until the transaction closes or aborts.

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/2023面试高手/article/detail/173399
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号