postgresql15 编译配置odyssey连接池

1. 下载编译

git clone https://github.com/yandex/odyssey

cd  odyssey
# 编译
make local_build

cd build
# 创建安装目录
mkdir -p /usr/odyssey/bin
mkdir -p /usr/odyssey/conf
cp ./sources/odyssey /usr/odyssey/bin/
cp -pv ../config-examples/odyssey-dev.conf /usr/odyssey/conf
cp -r ../scripts /usr/odyssey/
tree /usr/odyssey


#将相应目录权限完全赋给postgres用户
chown -R postgres:postgres /usr/odyssey
# 运行
nohup /usr/odyssey/bin/odyssey /usr/odyssey/conf/odyssey-dev.conf &

tail -f nohup.out

2. 打包为docker镜像

打包目录如下

在此目录下运行

docker build -t odyssey .

Dockerfile 内容如下

# docker build -t odyssey .
FROM debian
MAINTAINER xuejianxin@163.com
COPY ./odyssey         /usr/bin/
COPY ./odyssey.conf    /etc/odyssey/
COPY ./entrypoint.sh   /entrypoint.sh
RUN chmod u+x  /usr/bin/odyssey && chmod u+x  /entrypoint.sh
#ENTRYPOINT ["odyssey", "/etc/odyssey/odyssey.conf"]
#CMD ["/usr/bin/odyssey","/etc/odyssey/odyssey.conf"]
#CMD ["sleep","infinity"]
#CMD nohup /usr/bin/odyssey /etc/odyssey/odyssey.conf &
ENTRYPOINT ["/entrypoint.sh"]

entrypoint.sh 内容如下

#!/bin/sh
nohup /usr/bin/odyssey  /etc/odyssey/odyssey.conf &
echo success
sleep infinity

3.在windows10 dockerDesktop 上测试

# 创建postgresql
docker run -d --user root ^
 --name postgres5432   ^
 --restart=on-failure:10 ^
 -p 5432:5432 ^
 -p 5438:5438 ^
 -e LANG=en_US.utf8 ^
 -e ALLOW_IP_RANGE=0.0.0.0/0 ^
 -e POSTGRES_INITDB_ARGS="--locale=en_US.UTF-8  --wal-segsize 64"  ^
 -e PGDATA=/home/postgres/data ^
 -v d:/data/pgsql/5437:/home/postgres ^
 --shm-size=1g  ^
 -e POSTGRES_PASSWORD=******  postgres 

# 创建odyssey,以net=container 方式连接到数据库
docker run ^
--net=container:postgres5432  ^
--name odyssey5438   ^
--restart=on-failure:10  ^
-v d:/data/odyssey/odyssey-dev.conf:/etc/odyssey/odyssey.conf   ^
-d odyssey

# 或者可以使用pgbouncer

docker run -d  ^
--name pgbouncer5438     ^
--restart=on-failure:10 ^
--net=container:postgres5437 ^
-e DATABASE_URL="postgres://postgres:******@localhost:5432/mydb" ^
-e POOL_MODE=session  ^
-e MIN_POOL_SIZE=50 ^
-e DEFAULT_POOL_SIZE=200 ^
-e MAX_CLIENT_CONN=500 ^
-e LISTEN_BACKLOG=200 ^
-e AUTH_TYPE=plain ^
-e SERVER_RESET_QUERY="DISCARD ALL" ^
-e IGNORE_STARTUP_PARAMETERS=extra_float_digits,options,  ^
-e LISTEN_PORT=5438 ^
edoburu/pgbouncer

d:/data/odyssey/odyssey-dev.conf 文件内容如下:

pid_file "/tmp/odyssey.pid"
daemonize yes
unix_socket_dir "/tmp"
unix_socket_mode "0644"
log_format "%p %t %l [%i %s] (%c) %m\n"
log_to_stdout yes
log_syslog no
log_syslog_ident "odyssey"
log_syslog_facility "daemon"
log_debug no
log_config no
log_session no
log_query no
log_stats no
stats_interval 60
log_general_stats_prom yes
log_route_stats_prom no
promhttp_server_port 7777
workers "auto"
resolvers 1
readahead 8192
cache_coroutine 0
coroutine_stack_size 16
nodelay yes
keepalive 15
keepalive_keep_interval 75
keepalive_probes 9
keepalive_usr_timeout 0

listen {
	host "*"
	port 5438
	backlog 128
	compression yes
	tls "disable"
}
storage "postgres_server" {
	type "remote"
	# 注意此处
	host "127.0.0.1"
	port 5432
	target_session_attrs "read-write"
}

database "exam2021" {
	user "postgres" {
		authentication "none"
		storage "postgres_server"
		pool "session"
		pool_size 200
		pool_timeout 0
		pool_ttl 60
		pool_cancel no
		pool_rollback yes
		pool_discard yes
		client_fwd_error no
		log_debug no
	}
}
locks_dir "/tmp/odyssey"
graceful_die_on_errors yes
enable_online_restart no
bindwith_reuseport yes

4. 问题记录

在测试环境应用odyssey时出现以下莫名其妙错误，换成pgbouncer 时没有问题。
看来还是要谨慎使用。

2023-05-22 14:38:27.820 CST [10815] ERROR:  relation "sys_11" does not exist at character 371
2023-05-22 14:38:27.820 CST [10815] STATEMENT:  select  
                         *
                         
                  from sys_11 t
                         
                         WHERE  t.user_name = $1
2023-05-22 14:38:30.631 CST [10815] ERROR:  relation "sys_11" does not exist at character 371

5.补充一个dockerDesktop 的pgbouncer 配置

数据连接的几种方式：
appdb=host=db01 port=5666 dbname=appdb

appdb01=host=db01 port=5666 user=appuser password=1qaz@WSX 
dbname=appdb01

*=host=db01 port=5432

注意:如果在连接串中没有指定user和password,那么pgbouncer将使用给客户端连接pgbouncer时的用户名和密码来连接后端数据库,
并为每个不同的用户建立一个连接池;
如果连接中指定了user和password,pgbouncer将使用这里设置的用户名和密码来连接后端数据库,这样对使用这项配置的数据库来说,就只有一个连接池了.
-- 创建数据库
docker run -d --user root ^
 --name postgres5437   ^
 --restart=on-failure:10 ^
 -p 5437:5432 ^
 -p 5438:5438 ^
 -p 5439:5439 ^
 -e LANG=en_US.utf8 ^
 -e ALLOW_IP_RANGE=0.0.0.0/0 ^
 -e POSTGRES_INITDB_ARGS="--locale=en_US.UTF-8  --wal-segsize 64"  ^
 -e PGDATA=/home/postgres/data ^
 -v d:/data/pgsql/5437:/home/postgres ^
 -v d:/data/d1/exam:/tmp/exam/  ^
 --shm-size=5g  ^
 -e POSTGRES_PASSWORD=123456  postgres 


-- 创建pgbouncer
docker run -d  ^
--name pgbouncer5438     ^
--restart=on-failure:10 ^
--net=container:postgres5437 ^
-e DATABASE_URL="postgres://postgres:123456@localhost:5432/mydb" ^
-e POOL_MODE=session  ^
-e MIN_POOL_SIZE=50 ^
-e DEFAULT_POOL_SIZE=200 ^
-e MAX_CLIENT_CONN=500 ^
-e LISTEN_BACKLOG=200 ^
-e SERVER_RESET_QUERY="DISCARD ALL" ^
-e IGNORE_STARTUP_PARAMETERS=extra_float_digits,options,  ^
-e LISTEN_PORT=5438 ^
edoburu/pgbouncer

生成的配置如下

################## Auto generated ##################
[databases]
* = host=localhost port=5432 user=postgres

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 5438
unix_socket_dir =
user = postgres
auth_file = /etc/pgbouncer/userlist.txt
auth_type = any
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
pool_mode = session
max_client_conn = 500
default_pool_size = 200
min_pool_size = 50
ignore_startup_parameters = extra_float_digits,options,

# Log settings
admin_users = postgres

# Connection sanity checks, timeouts
server_reset_query = DISCARD ALL

# TLS settings

# Dangerous timeouts
listen_backlog = 200
################## end file ##################

参考：

官方地址

高可用: 体验使用Odyssey连接池(一)
高可用: 体验使用Odyssey连接池(二)