背景
一直以来PostgreSQL数据库在scale up和scale out的方向都走得比较靠前，例如
-
单元化技术
oleg postgrespro的 PostgreSQL cluster，在分布式事务性能提升，选举算法方面的贡献非常大。
https://github.com/postgrespro/postgres_clustersim 他们的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_directory = 'pg_log' # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
log_file_mode = 0600 # creation mode for log files,
log_truncate_on_rotation = on # If on, an existing log file with the
log_rotation_age = 1d # Automatic rotation of logfiles will
log_rotation_size = 10MB # Automatic rotation of logfiles will
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose # terse, default, or verbose messages
log_lock_waits = on # log lock waits >= deadlock_timeout
log_statement = 'ddl' # none, ddl, mod, all
log_timezone = 'PRC'
track_activity_query_size = 4096 # (change requires restart)
autovacuum = on # Enable autovacuum subprocess? 'on'
log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
autovacuum_freeze_max_age = 1500000000 # maximum XID age before forced vacuum
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'C' # locale for system error message
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C' # locale for number formatting
lc_time = 'C' # locale for time formatting
default_text_search_config = 'pg_catalog.english'
deadlock_timeout = 1s
pg_stat_statements.max = 1000
pg_stat_statements.track = all
配置recovery.conf, 主的文件名为recovery.done
pg93@db-172-16-3-39-> cp $PGHOME/share/recovery.conf.sample ./
pg93@db-172-16-3-39-> mv recovery.conf.sample recovery.conf
vi recovery.conf
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=172.16.3.39 port=1999 user=postgres keepalives_idle=60'
pg93@db-172-16-3-39-> cp recovery.conf ../pg_root_1999/recovery.done
pg93@db-172-16-3-39-> vi ../pg_root_1999/recovery.done
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=172.16.3.39 port=2000 user=postgres keepalives_idle=60'
配置连接秘钥
vi ~/.pgpass
172.16.3.39:1999:*:postgres:postgres
172.16.3.39:2000:*:postgres:postgres
chmod 400 ~/.pgpass
启动主库和备库, 并确认流复制正常
pg93@db-172-16-3-39-> pg_ctl start -D /pgdata/digoal/1921/data03/pg93/pg_root_1999
pg93@db-172-16-3-39-> pg_ctl start -D /pgdata/digoal/1921/data03/pg93/pg_root_2000
pg93@db-172-16-3-39-> psql -h 127.0.0.1 -p 1999
Password:
psql (9.3.1)
Type "help" for help.
digoal=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
digoal | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication | {}
digoal=# \q
pg93@db-172-16-3-39-> psql -h 127.0.0.1 -p 2000
Password:
psql (9.3.1)
Type "help" for help.
digoal=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
ps -ewf|grep stream
postgres: wal sender process postgres 172.16.3.39(21679) streaming 2/E4
2. 安装pgpool
[root@db-172-16-3-150 soft_bak]# wget http://www.pgpool.net/download.php?f=pgpool-II-3.3.3.tar.gz
[root@db-172-16-3-150 soft_bak]# mv download.php\?f\=pgpool-II-3.3.3.tar.gz pgpool.tar.gz
[root@db-172-16-3-150 soft_bak]# tar -zxvf pgpool.tar.gz
[root@db-172-16-3-150 soft_bak]# cd pgpool-II-3.3.3/
[root@db-172-16-3-150 pgpool-II-3.3.3]#
这一步需要注意, 最好pgpool依赖的postgresql版本和backend database的数据库版本一致. 例如都是9.3.1
[root@db-172-16-3-150 pgpool-II-3.3.3]# ./configure --prefix=/opt/pgpool3.3.3 --with-pgsql=/home/pg93/pgsql9.3.1
[root@db-172-16-3-150 pgpool-II-3.3.3]# gmake && gmake install
3. 配置pgpool
[root@db-172-16-3-150 etc]# cd /opt/pgpool3.3.3/etc/
[root@db-172-16-3-150 etc]# cp pool_hba.conf.sample pool_hba.conf
[root@db-172-16-3-150 etc]# cp pgpool.conf.sample-stream pgpool.conf
[root@db-172-16-3-150 etc]# cp pcp.conf.sample pcp.conf
[root@db-172-16-3-150 etc]# vi pgpool.conf
listen_addresses = '0.0.0.0'
port = 9999
socket_dir = '/tmp'
pcp_port = 9898
pcp_socket_dir = '/tmp'
backend_hostname0 = '172.16.3.39'
backend_port0 = 1999
backend_weight0 = 1
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = '172.16.3.39'
backend_port1 = 2000
backend_weight1 = 1
backend_flag1 = 'ALLOW_TO_FAILOVER'
enable_pool_hba = on
pool_passwd = 'pool_passwd'
authentication_timeout = 60
ssl = off
num_init_children = 32
max_pool = 4
child_life_time = 300
child_max_connections = 0
connection_life_time = 0
client_idle_limit = 0
log_destination = 'syslog'
print_timestamp = on
log_connections = on
log_hostname = off
# 以下两个参数用于调试, 能看到sql balance的情况.
log_statement = on
log_per_node_statement = on
log_standby_delay = 'always'
syslog_facility = 'LOCAL0'
syslog_ident = 'pgpool'
debug_level = 0
pid_file_name = '/var/run/pgpool/pgpool.pid'
logdir = '/tmp'
connection_cache = on
reset_query_list = 'ABORT; DISCARD ALL'
replication_mode = off
replicate_select = off
insert_lock = off
lobj_lock_table = ''
replication_stop_on_mismatch = off
failover_if_affected_tuples_mismatch = off
load_balance_mode = on
ignore_leading_white_space = on
white_function_list = ''
black_function_list = 'currval,lastval,nextval,setval'
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 2
sr_check_user = 'srcheck'
sr_check_password = 'SRCHECK'
delay_threshold = 1024000
follow_master_command = ''
parallel_mode = off
pgpool2_hostname = ''
health_check_period = 0
health_check_timeout = 20
health_check_user = 'nobody'
health_check_password = ''
health_check_max_retries = 0
health_check_retry_delay = 1
failover_command = ''
failback_command = ''
fail_over_on_backend_error = on
search_primary_node_timeout = 10
recovery_user = 'nobody'
recovery_password = ''
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0
use_watchdog = off
trusted_servers = ''
ping_path = '/bin'
wd_hostname = ''
wd_port = 9000
wd_authkey = ''
delegate_IP = ''
ifconfig_path = '/sbin'
if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0'
if_down_cmd = 'ifconfig eth0:0 down'
arping_path = '/usr/sbin' # arping command path
arping_cmd = 'arping -U $_IP_$ -w 1'
clear_memqcache_on_escalation = on
wd_escalation_command = ''
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
heartbeat_destination0 = 'host0_ip1'
heartbeat_destination_port0 = 9694
heartbeat_device0 = ''
wd_life_point = 3
wd_lifecheck_query = 'SELECT 1'
wd_lifecheck_dbname = 'template1'
wd_lifecheck_user = 'nobody'
wd_lifecheck_password = ''
relcache_expire = 0
relcache_size = 256
check_temp_table = on
memory_cache_enabled = off
memqcache_method = 'shmem'
memqcache_memcached_host = 'localhost'
memqcache_memcached_port = 11211
memqcache_total_size = 67108864
memqcache_max_num_cache = 1000000
memqcache_expire = 0
memqcache_auto_cache_invalidation = on
memqcache_maxcache = 409600
memqcache_cache_block_size = 1048576
memqcache_oiddir = '/var/log/pgpool/oiddir'
white_memqcache_table_list = ''
black_memqcache_table_list = ''
生成pgpool管理秘钥, 并配置
[root@db-172-16-3-150 etc]# /opt/pgpool3.3.3/bin/pg_md5 -u sup_pool sup_pool_pwd
c21777db255631573e4233403773bb3b
[root@db-172-16-3-150 etc]# vi pcp.conf
sup_pool:c21777db255631573e4233403773bb3b
配置pgpool pool_hba.conf
[root@db-172-16-3-150 etc]# vi pool_hba.conf
# trust会导致错误
local all all trust
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 md5
配置pgpool-ii密码文件, 从postgresql数据库pg_authid获取
digoal=# select rolname,rolpassword from pg_authid;
rolname | rolpassword
----------+-------------------------------------
postgres | md53175bce1d3201d16594cebf9d7eb3f9d
digoal | md5462f71c79368ccf422f8a773ef40074d
srcheck | md5662c10f61b27a9ab38ce69157186b25f
(3 rows)
[root@db-172-16-3-150 etc]# vi pool_passwd
postgres:md53175bce1d3201d16594cebf9d7eb3f9d
4. 创建check用户, pgpool用它连接到数据库并 检查数据库是否正常
pg93@db-172-16-3-39-> psql -h 127.0.0.1 -p 1999
Password:
psql (9.3.1)
Type "help" for help.
digoal=# create role srcheck nosuperuser login encrypted password 'SRCHECK';
CREATE ROLE
5. 启动pgpool
[root@db-172-16-3-150 etc]# /opt/pgpool3.3.3/bin/pgpool -f /opt/pgpool3.3.3/etc/pgpool.conf
[root@db-172-16-3-150 etc]# tail -f -n 2 /var/log/messages
May 13 11:56:45 db-172-16-3-150 pgpool[28153]: pgpool-II successfully started. version 3.3.3 (tokakiboshi)
May 13 11:56:45 db-172-16-3-150 pgpool[28153]: find_primary_node: primary node id is 0
6. 常见连接错误
问题1
[root@db-172-16-3-150 etc]# su - pg93
pg93@db-172-16-3-150-> psql -h 127.0.0.1 -p 9999 -U postgres digoal
psql: ERROR: MD5 authentication is unsupported in replication, master-slave and parallel modes.
HINT: check pg_hba.conf
这个错误的原因已经很清楚了, 需要修改pool_hba.conf, 对应的trust改成md5 . 对应的错误代码 :
pool_auth.c
/* md5 authentication? */
else if (authkind == 5)
{
/* If MD5 auth is not active in pool_hba.conf, it cannot be
* used with other than raw mode.
*/
if (frontend->auth_method != uaMD5 && !RAW_MODE && NUM_BACKENDS > 1)
{
pool_send_error_message(frontend, protoMajor, AUTHFAIL_ERRORCODE,
"MD5 authentication is unsupported in replication, master-slave and parallel modes.",
"",
"check pg_hba.conf",
__FILE__, __LINE__);
return -1;
}
错误修复
[root@db-172-16-3-150 pgpool-II-3.3.3]# cd /opt/pgpool3.3.3/etc/
[root@db-172-16-3-150 etc]# vi pool_hba.conf
host all all 127.0.0.1/32 md5
[root@db-172-16-3-150 etc]# /opt/pgpool3.3.3/bin/pgpool -f /opt/pgpool3.3.3/etc/pgpool.conf reload
这里存在BUG, 从trust改成md5后, 报错是没了, 但是密码也不提示就直接进了(这显然是有问题的).
pg93@db-172-16-3-150-> psql -h 127.0.0.1 -p 9999 -U postgres digoal
psql (9.3.3, server 9.3.1)
Type "help" for help.
digoal=# \q
连接已配置为md5验证的条目. 已有的172.16.3.150. 正常
pg93@db-172-16-3-150-> psql -h 172.16.3.150 -p 9999 -U postgres digoal
Password for user postgres:
psql (9.3.3, server 9.3.1)
Type "help" for help.
digoal=# \q
问题2
使用在pool_passwd中么有配置的用户连接.
pg93@db-172-16-3-150-> psql -h 172.16.3.150 -p 9999 -U digoal digoal
psql: ERROR: "MD5" authentication with pgpool failed for user "digoal"
这个错误并没有提示输入密码就出错, 原因是在pool_passwd文件中没有加入digoal的密码.
修复错误, 在pool_passwd中添加条目
[root@db-172-16-3-150 etc]# vi pool_passwd
digoal:md5462f71c79368ccf422f8a773ef40074d
[root@db-172-16-3-150 etc]# /opt/pgpool3.3.3/bin/pgpool -f /opt/pgpool3.3.3/etc/pgpool.conf reload
[root@db-172-16-3-150 etc]# su - pg93
pg93@db-172-16-3-150-> psql -h 127.0.0.1 -p 9999 -U digoal digoal
Password for user digoal:
psql (9.3.3, server 9.3.1)
Type "help" for help.
digoal=#
7. 测试load balance
测试hint是否可用, replication表示发到主节点
digoal=# /*REPLICATION*/ SELECT 1;
?column?
----------
1
(1 row)
digoal=# SELECT 1;
?column?
----------
1
(1 row)
查看日志
May 13 14:00:24 db-172-16-3-150 pgpool[30046]: statement: /*REPLICATION*/ SELECT 1;
May 13 14:00:24 db-172-16-3-150 pgpool[30046]: DB node id: 0 backend pid: 19371 statement: /*REPLICATION*/ SELECT 1;
May 13 14:00:33 db-172-16-3-150 pgpool[30046]: statement: SELECT 1;
May 13 14:00:33 db-172-16-3-150 pgpool[30046]: DB node id: 1 backend pid: 19372 statement: SELECT 1;
do默认分发到master节点, 因为pgpool不能确认do里面是否有修改数据库的动作.
digoal=# do language plpgsql $$
digoal$# declare
digoal$# begin
digoal$# raise notice 'test';
digoal$# end;
digoal$# $$;
NOTICE: test
DO
May 13 14:16:06 db-172-16-3-150 pgpool[30072]: statement: do language plpgsql $$#012declare #012begin#012 raise notice 'test';#012end;#012$$;
May 13 14:16:06 db-172-16-3-150 pgpool[30072]: DB node id: 0 backend pid: 20575 statement: do language plpgsql $$#012declare #012begin#012 raise notice 'test';#012end;#012$$;
May 13 14:16:06 db-172-16-3-150 pgpool[30072]: pool_send_and_wait: Error or notice message from backend: : DB node id: 0 backend pid: 20575 statement: do language plpgsql $$#012declare #012begin#012 raise notice 'test';#012end;#012$$; message: test
在仅仅设置了黑名单的情况下, 所有状态的函数(volatile, stable, immutable)都分发到standby节点.
May 13 14:18:07 db-172-16-3-150 pgpool[30072]: statement: select now();
May 13 14:18:07 db-172-16-3-150 pgpool[30072]: DB node id: 1 backend pid: 20576 statement: select now();
May 13 14:18:28 db-172-16-3-150 pgpool[30072]: statement: select pg_is_in_recovery();
May 13 14:18:28 db-172-16-3-150 pgpool[30072]: DB node id: 1 backend pid: 20576 statement: select pg_is_in_recovery();
May 13 14:22:46 db-172-16-3-150 pgpool[30072]: statement: select clock_timestamp();
May 13 14:22:46 db-172-16-3-150 pgpool[30072]: DB node id: 1 backend pid: 20576 statement: select clock_timestamp();
例子, 发到standby节点
digoal=# create or replace function ft1(v_id int) returns int as
$$
declare
begin
update t set info='new' where id=v_id;
return 0;
end;
$$ language plpgsql strict;
CREATE FUNCTION
digoal=# select ft1(1);
ERROR: cannot execute UPDATE in a read-only transaction
CONTEXT: SQL statement "update t set info='new' where id=v_id"
PL/pgSQL function ft1(integer) line 4 at SQL statement
使用hint强行在master节点执行 :
digoal=# /*REPLICATION*/ select ft1(1);
ft1
-----
0
(1 row)
如果同时配置了黑名单和白名单, 那么以白名单为准, 不在白名单的都会发到master节点.
[root@db-172-16-3-150 etc]# vi pgpool.conf
white_function_list = 'ft'
[root@db-172-16-3-150 etc]# /opt/pgpool3.3.3/bin/pgpool -f /opt/pgpool3.3.3/etc/pgpool.conf reload
May 13 14:36:08 db-172-16-3-150 pgpool[30056]: statement: select ft(1);
May 13 14:36:08 db-172-16-3-150 pgpool[30056]: DB node id: 1 backend pid: 22331 statement: select ft(1);
May 13 14:36:14 db-172-16-3-150 pgpool[30056]: statement: select ft1(1);
May 13 14:36:14 db-172-16-3-150 pgpool[30056]: DB node id: 0 backend pid: 22330 statement: select ft1(1);
May 13 14:36:18 db-172-16-3-150 pgpool[30056]: statement: select now();
May 13 14:36:18 db-172-16-3-150 pgpool[30056]: DB node id: 0 backend pid: 22330 statement: select now();
May 13 14:36:35 db-172-16-3-150 pgpool[30056]: statement: select clock_timestamp();
May 13 14:36:35 db-172-16-3-150 pgpool[30056]: DB node id: 0 backend pid: 22330 statement: select clock_timestamp();
update正常分发到master节点
May 13 14:31:02 db-172-16-3-150 pgpool[30072]: statement: update t set info='new' where id=1;
May 13 14:31:02 db-172-16-3-150 pgpool[30072]: DB node id: 0 backend pid: 20575 statement: update t set info='new' where id=1;
事务中包含游标, 游标强制在master节点执行
digoal=# begin;
BEGIN
digoal=# select 1;
?column?
----------
1
(1 row)
digoal=# declare cur cursor for select * from t;
DECLARE CURSOR
digoal=# fetch next from cur;
id | info | crt_time
----+----------------------------------+----------------------------
3 | 37c8aae0733801d116e1c83ab60f183c | 2013-10-12 17:33:14.654526
(1 row)
digoal=# update t set info='new' where current of cur;
UPDATE 1
digoal=# end;
COMMIT
这里我们看到, begin, end是在所有节点执行的, 但是事务中的select是在standby中执行的, 游标是在master执行的.
May 13 14:38:24 db-172-16-3-150 pgpool[30056]: statement: begin;
May 13 14:38:24 db-172-16-3-150 pgpool[30056]: DB node id: 0 backend pid: 22330 statement: begin;
May 13 14:38:24 db-172-16-3-150 pgpool[30056]: DB node id: 1 backend pid: 22331 statement: begin;
May 13 14:38:25 db-172-16-3-150 pgpool[30056]: statement: select 1;
May 13 14:38:25 db-172-16-3-150 pgpool[30056]: DB node id: 1 backend pid: 22331 statement: select 1;
May 13 14:38:32 db-172-16-3-150 pgpool[30056]: statement: declare cur cursor for select * from t;
May 13 14:38:32 db-172-16-3-150 pgpool[30056]: DB node id: 0 backend pid: 22330 statement: declare cur cursor for select * from t;
May 13 14:38:44 db-172-16-3-150 pgpool[30056]: statement: fetch next from cur;
May 13 14:38:44 db-172-16-3-150 pgpool[30056]: DB node id: 0 backend pid: 22330 statement: fetch next from cur;
May 13 14:38:46 db-172-16-3-150 pgpool[30079]: Replication of node:1 is behind 1840 bytes from the primary server (node:0)
May 13 14:38:47 db-172-16-3-150 pgpool[30056]: statement: update t set info='new' where current of cur;
May 13 14:38:47 db-172-16-3-150 pgpool[30056]: DB node id: 0 backend pid: 22330 statement: update t set info='new' where current of cur;
May 13 14:38:48 db-172-16-3-150 pgpool[30079]: Replication of node:1 is behind 1840 bytes from the primary server (node:0)
May 13 14:38:49 db-172-16-3-150 pgpool[30056]: statement: end;
May 13 14:38:49 db-172-16-3-150 pgpool[30056]: DB node id: 1 backend pid: 22331 statement: end;
May 13 14:38:49 db-172-16-3-150 pgpool[30056]: DB node id: 0 backend pid: 22330 statement: end;
对于先执行更新的事务, 那么后期的查询都会在master节点执行.
May 13 15:33:11 db-172-16-3-150 pgpool[30056]: statement: begin;
May 13 15:33:11 db-172-16-3-150 pgpool[30056]: DB node id: 0 backend pid: 22330 statement: begin;
May 13 15:33:11 db-172-16-3-150 pgpool[30056]: DB node id: 1 backend pid: 22331 statement: begin;
May 13 15:33:13 db-172-16-3-150 pgpool[30056]: statement: declare cur cursor for select * from t;
May 13 15:33:13 db-172-16-3-150 pgpool[30056]: DB node id: 0 backend pid: 22330 statement: declare cur cursor for select * from t;
May 13 15:33:15 db-172-16-3-150 pgpool[30056]: statement: fetch next from cur;
May 13 15:33:15 db-172-16-3-150 pgpool[30056]: DB node id: 0 backend pid: 22330 statement: fetch next from cur;
May 13 15:33:16 db-172-16-3-150 pgpool[30056]: statement: update t set info='new' where current of cur;
May 13 15:33:16 db-172-16-3-150 pgpool[30056]: DB node id: 0 backend pid: 22330 statement: update t set info='new' where current of cur;
May 13 15:33:17 db-172-16-3-150 pgpool[30056]: statement: select * from t limit 1;
May 13 15:33:17 db-172-16-3-150 pgpool[30056]: DB node id: 0 backend pid: 22330 statement: select * from t limit 1;
May 13 15:33:19 db-172-16-3-150 pgpool[30056]: statement: select 1;
May 13 15:33:19 db-172-16-3-150 pgpool[30056]: DB node id: 0 backend pid: 22330 statement: select 1;
参考
1. http://www.pgpool.net/docs/latest/pgpool-en.html
2. http://blog.163.com/digoal@126/blog/static/1638770402014316448141/
3. http://www.continuent.com/
