Use pgbouncer connect to GreenPlum’s segment node
背景
pgbouncer是PostgreSQL数据库的轻量级开源连接池，有着非常好的性能。
源码修改部分来自老唐(osdba)写的一篇BLOG，《让pgbouncer可以连接到greenplum的segment上》本文末尾有链接。
下面参照他在BLOG中提到的方法测试了一下，测试如下 :
软件准备 :
1. greenplum环境(本例使用的是3.3.3.4) ,
2. pgbouncer 1.4.2
3. libevent 2.0
操作过程 :
1. 安装libevent :
tar -zxvf libevent-2.0.16-stable.tar.gz
cd libevent-2.0.16-stable
./configure && make && make install
2. 修改pgbouncer源码, 参考osdba的BLOG :
修改src/client.c, (新增第127, 128行)增加对options的支持:
123 } else if (varcache_set(&client->vars, key, val)) {
124 slog_debug(client, "got var: %s=%s", key, val);
125 } else if (strlist_contains(cf_ignore_startup_params, key)) {
126 slog_debug(client, "ignoring startup parameter: %s=%s", key, val);
# add start point
127 } else if (strcmp(key,"options") == 0 ) {
128 slog_debug(client, "ignoring startup parameter: %s=%s", key, val);
# add stop point
129 } else {
130 slog_warning(client, "unsupported startup parameter: %s=%s", key, val);
131 disconnect_client(client, true, "Unsupported startup parameter: %s", key);
132 return false;
133 }
修改src/loader.c文件(新增第192,238-239,348-351行):
172 bool parse_database(void *base, const char *name, const char *connstr)
173 {
174 char *p, *key, *val;
175 PktBuf *msg;
176 PgDatabase *db;
177 int pool_size = -1;
178 int res_pool_size = -1;
179 int dbname_ofs;
180
181 char *tmp_connstr;
182 const char *dbname = name;
183 char *host = NULL;
184 char *port = "5432";
185 char *username = NULL;
186 char *password = "";
187 char *client_encoding = NULL;
188 char *datestyle = NULL;
189 char *timezone = NULL;
190 char *connect_query = NULL;
191 char *appname = NULL;
# add start point
192 char *options = NULL;
# add stop point
.....
206 while (*p) {
207 p = cstr_get_pair(p, &key, &val);
208 if (p == NULL) {
209 log_error("%s: syntax error in connstring", name);
210 goto fail;
211 } else if (!key[0])
212 break;
213
214 if (strcmp("dbname", key) == 0)
215 dbname = val;
216 else if (strcmp("host", key) == 0)
217 host = val;
218 else if (strcmp("port", key) == 0)
219 port = val;
220 else if (strcmp("user", key) == 0)
221 username = val;
222 else if (strcmp("password", key) == 0)
223 password = val;
224 else if (strcmp("client_encoding", key) == 0)
225 client_encoding = val;
226 else if (strcmp("datestyle", key) == 0)
227 datestyle = val;
228 else if (strcmp("timezone", key) == 0)
229 timezone = val;
230 else if (strcmp("pool_size", key) == 0)
231 pool_size = atoi(val);
232 else if (strcmp("reserve_pool", key) == 0)
233 res_pool_size = atoi(val);
234 else if (strcmp("connect_query", key) == 0)
235 connect_query = val;
236 else if (strcmp("application_name", key) == 0)
237 appname = val;
# add start point
238 else if (strcmp("options", key) == 0)
239 options = val;
# add stop point
.....
343 if (appname) {
344 pktbuf_put_string(msg, "application_name");
345 pktbuf_put_string(msg, appname);
346 }
347
# add start point
348 if (options) {
349 pktbuf_put_string(msg, "options");
350 pktbuf_put_string(msg, options);
351 }
# add stop point
3. 编译安装修改过的pgbouncer1.4.2
tar -zxvf pgbouncer-1.4.2.tgz
cd pgbouncer-1.4.2
./configure --prefix=/opt/pgbouncer
gmake
gmake install
4. 配置某个需要被pgbouncer连接的greenplum segment节点的pg_hba.conf, reload配置文件,
在pg_hba.conf加入对pgbouncer服务器的允许, 例如 :
host all all 0.0.0.0/0 md5
pg_ctl reload -D $PGDATA
5. 配置pgbouncer, 启动
# 在[databases]章节多出的部分是 options='-c gp_session_role=utility'
vi config.ini
[databases]
sanpdw = host=172.16.12.12 dbname=digoal port=50001 pool_size=5 options='-c gp_session_role=utility'
[pgbouncer]
pool_mode = transaction
listen_port = 11111
unix_socket_dir = /opt/pgbouncer/etc
listen_addr = *
auth_type = md5
auth_file = /opt/pgbouncer/etc/users11111.txt
logfile = /dev/null
pidfile = /opt/pgbouncer/etc/pgbouncer11111.pid
max_client_conn = 10000
reserve_pool_timeout = 0
server_reset_query =
admin_users = pgbouncer_admin
stats_users = pgbouncer_guest
ignore_startup_parameters = extra_float_digits
server_lifetime = 60
server_check_query = select 1
server_check_delay = 5
vi users11111.txt
"digoal" "md57bc4a6cbe56da1w1f1cbf27d3d3045d5"
chmod 700 config.ini
chmod 400 users11111.txt
启动pgbouncer
export LD_LIBRARY_PATH=/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib
./pgbouncer/bin/pgbouncer -d -u postgres ./pgbouncer/etc/config.ini
6. 连接测试
找一台安装了PostgreSQL客户端的机子连接pgbouncer的11111端口 :
psql -h 172.16.13.13 -p 11111 -U digoal -d digoal -W
输入密码后就登陆到GreenPlum的segment （172.16.12.12:50001）了.
psql -h 172.16.13.13 -p 11111 -U digoal -d digoal -W
Password for user digoal:
psql (9.0.1, server 8.2.13)
WARNING: psql version 9.0, server version 8.2.
Some psql features might not work.
Type "help" for help.
digoal=>
小结
1. 这种连接方式可以干什么事情呢? 我想了想, 以前遇到过在主节点无法创建表的情形, 原因是该表名在segment节点存在了, 遇到这种情况可能是删表的时候主节点删除了但是segment节点未删除, 当然这种情况很少见.
2. 在主库无法使用的时候可以利用这种方法对外提供少量服务.
3. 通过这种方式连进去一定要小心操作, 千万别在里面胡乱建表, greenplum的正规操作还是通过master来做比较靠谱.
参考
http://blog.osdba.net/?post=69