pgbouncer 无法连接到本地 postgresql 数据库（超时）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16036019/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
pgbouncer can't connect to local postgresql database (timeout)
提问by Tomas Jacobsen
Im trying to get pgbouncer to work on my standalone postgresql server, but when I try to connect to the database using the pgbouncer port, I get an timeout error.
我试图让 pgbouncer 在我的独立 postgresql 服务器上工作，但是当我尝试使用 pgbouncer 端口连接到数据库时，出现超时错误。
Postgresql: 9.1.8
PostgreSQL: 9.1.8
pgBouncer: 1.4.2
pgBouncer：1.4.2
OS: Ubuntu 12.04.2 LTS (GNU/Linux 3.8.4-x86_64-linode31 x86_64)
操作系统：Ubuntu 12.04.2 LTS（GNU/Linux 3.8.4-x86_64-linode31 x86_64）
Here is my conf file:
这是我的 conf 文件：
[databases]
postgres = host=127.0.0.1 port=5432 dbname=postgres
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
logfile = /home/username/pg_log/pgbouncer.log
pidfile = /tmp/pgbouncer.pid
listen_addr = xxx.xxx.xxx.xxx
listen_port = 6432
unix_socket_dir = /var/run/postgresql
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
stats_users = stats, root
user = postgres
pool_mode = session
server_reset_query = DISCARD ALL
max_client_conn = 1000
default_pool_size = 20
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
Im trying to test the connection like this:
我试图像这样测试连接：
psql -p 6432 -U postgres mydb
Here is my log file:
这是我的日志文件：
28396 LOG listening on xxx.xxx.xxx.xxx:6432
28396 LOG listening on unix:/var/run/postgresql/.s.PGSQL.6432
28396 LOG process up: pgbouncer 1.4.2, libevent 2.0.16-stable (epoll), adns: evdns2
28864 LOG C-0x23f7890: mydb/postgres@unix:6432 login attempt: db=mydb user=postgres
28864 LOG C-0x23f7890: mydb/postgres@unix:6432 closing because: client unexpected eof (age=0)
28864 LOG C-0x23f7890: mydb/postgres@unix:6432 login attempt: db=mydb user=postgres
28864 LOG S-0x2415240: mydb/[email protected]:5432 new connection to server
28864 LOG S-0x2415240: mydb/[email protected]:5432 closing because: connect failed (age=0)
28864 LOG S-0x2415240: mydb/[email protected]:5432 new connection to server
28864 LOG S-0x2415240: mydb/[email protected]:5432 closing because: connect failed (age=0)
28864 LOG S-0x2415240: mydb/[email protected]:5432 new connection to server
28864 LOG S-0x2415240: mydb/[email protected]:5432 closing because: connect failed (age=0)
28864 LOG S-0x2415240: mydb/[email protected]:5432 new connection to server
28864 LOG S-0x2415240: mydb/[email protected]:5432 closing because: connect failed (age=0)
28864 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us
28864 LOG C-0x23f7890: mydb/postgres@unix:6432 closing because: client_login_timeout (server down) (age=60)
28864 WARNING C-0x23f7890: mydb/postgres@unix:6432 Pooler Error: client_login_timeout (server down)
28864 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us
28864 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us
Is there something else I need to change to make pgbouncer work?
还有什么我需要改变才能使 pgbouncer 工作吗？
回答by Tomas Jacobsen
Change
改变
listen_addresses = 'private_ip'
to
到
listen_addresses = '*'
In the postgresql.conf file.
在 postgresql.conf 文件中。
回答by marcj
Looks like you are trying to connect using a Unix socket. Depending on your Postgres installation, the socket may have been created in /tmp rather than /var/run/postgres. I've run into this same issue and it has been discussed here.
看起来您正在尝试使用 Unix 套接字进行连接。根据您的 Postgres 安装，套接字可能是在 /tmp 而不是 /var/run/postgres 中创建的。我遇到了同样的问题，这里已经讨论过了。
Try changing the pgbouncer.ini config to this:
尝试将 pgbouncer.ini 配置更改为：
unix_socket_dir = /tmp
You could also try just connecting to localhost by adding the -h as follows:
您也可以尝试通过添加 -h 来连接到本地主机，如下所示：
psql -p 6432 -h localhost -U postgres mydb
Hope this helps!
希望这可以帮助！
