无法连接到本地PostgreSQL [英] Can not connect to local PostgreSQL
问题描述
我设法弄清楚了本地开发环境.
I've managed to bork my local development environment.
我所有的本地Rails应用现在都出现错误:
All my local Rails apps are now giving the error:
PGError
could not connect to server: Permission denied
Is the server running locally and accepting
connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?
我不知道是什么原因造成的.
I've no idea what's caused this.
在寻找解决方案时，我已经更新了所有捆绑的gem，更新的系统gem，更新的MacPorts.没事.
While searching for a solution I've updated all bundled gems, updated system gems, updated MacPorts. No joy.
其他人已经报告了从OSX Leopard升级到Lion时出现的此问题，原因是应该使用哪个版本的Postgres(即OSX版本或MacPorts版本)感到困惑.我已经经营Lion几个月了，所以现在应该发生这种情况似乎很奇怪.
Others have reported this issue when upgrading from OSX Leopard to Lion, due to confusion over which version of Postgres should be used (i.e., OSX version or MacPorts version). I've been running Lion for several months, so it seems strange that this should happen now.
在不首先了解问题所在的情况下，我不愿意过多地摆弄.我如何才能有条不紊地进行调试?
I'm reluctant to mess around too much without first understanding what the problem is. How can I debug this methodically?
如何确定我的系统上有多少个PostgreSQL版本，正在访问哪个版本以及它的位置?如果使用了错误的PostgreSQL，我该如何解决?
How can I determine how many versions of PostgreSQL are on my system, which one is being accessed, and where it is located? How do I fix this if the wrong PostgreSQL is being used?
很抱歉出现菜鸟问题.我仍在学习这是如何工作的！感谢您的指导.
Sorry for the noob questions. I'm still learning how this works! Thanks for any pointers.
编辑
根据以下建议和评论进行的一些更新.
Some updates based on suggestions and comments below.
我试图运行pg_lsclusters，该错误返回了command not found错误.
I tried to run pg_lsclusters which returned a command not found error.
然后我尝试将pg_hba.conf文件本地化，并找到了这三个示例文件:
I then tried to local my pg_hba.conf file and found these three sample files:
/opt/local/share/postgresql84/pg_hba.conf.sample
/opt/local/var/macports/software/postgresql84/8.4.7_0/opt/local/share/postgresql84/pg_hba.conf.sample
/usr/share/postgresql/pg_hba.conf.sample
所以我假设安装了3个版本的PSQL? Macports，默认为OSX和???.
So I assume 3 versions of PSQL are installed? Macports, OSX default and ???.
然后我搜索了返回的launchctl启动脚本ps -ef | grep postgres
I then did a search for the launchctl startup script ps -ef | grep postgres which returned
0 56 1 0 11:41AM ?? 0:00.02 /opt/local/bin/daemondo --label=postgresql84-server --start-cmd /opt/local/etc/LaunchDaemons/org.macports.postgresql84-server/postgresql84-server.wrapper start ; --stop-cmd /opt/local/etc/LaunchDaemons/org.macports.postgresql84-server/postgresql84-server.wrapper stop ; --restart-cmd /opt/local/etc/LaunchDaemons/org.macports.postgresql84-server/postgresql84-server.wrapper restart ; --pid=none
500 372 1 0 11:42AM ?? 0:00.17 /opt/local/lib/postgresql84/bin/postgres -D /opt/local/var/db/postgresql84/defaultdb
500 766 372 0 11:43AM ?? 0:00.37 postgres: writer process
500 767 372 0 11:43AM ?? 0:00.24 postgres: wal writer process
500 768 372 0 11:43AM ?? 0:00.16 postgres: autovacuum launcher process
500 769 372 0 11:43AM ?? 0:00.08 postgres: stats collector process
501 4497 1016 0 12:36PM ttys000 0:00.00 grep postgres
我已经在 http://pastebin.com/Gj5TpP62 上发布了postgresql84-server.wrapper的内容.
I've posted the contents of postgresql84-server.wrapper at http://pastebin.com/Gj5TpP62.
我尝试运行port load postgresql184-server，但收到错误Error: Port postgresql184-server not found.
I tried to run port load postgresql184-server but received an error Error: Port postgresql184-server not found.
我仍然很困惑如何解决此问题，并感谢任何傻瓜"指针.
I'm still very confused how to fix this, and appreciate any "for dummies" pointers.
谢谢！
EDIT2
这个问题在我对守护进程有一些问题之后开始.我的本地Rails应用程序崩溃，并出现找不到daemondo gem"的错误.然后，我经历了一系列捆绑更新，gem更新，端口更新和brew更新，以尝试找到问题.
This issue began after I had some problems with daemondo. My local Rails apps were crashing with an application error along the lines of "daemondo gem can not be found". I then went through a series of bundle updates, gem updates, port updates and brew updates to try and find the issue.
此错误可能是守护程序出现的问题吗?
Could this error be an issue with daemondo?
推荐答案
这确实看起来像是文件权限错误. Unix域套接字是文件，并且与其他端口一样具有用户权限.似乎试图访问数据库的OSX用户没有访问套接字文件的文件许可权.为了确认这一点，我已经在Ubuntu和psql上进行了一些测试，以尝试生成相同的错误(包括在下面).
This really looks like a file permissions error. Unix domain sockets are files and have user permissions just like any other. It looks as though the OSX user attempting to access the database does not have file permissions to access the socket file. To confirm this I've done some tests on Ubuntu and psql to try to generate the same error (included below).
您需要检查套接字文件及其目录/var和/var/pgsql_socket的权限.您的Rails应用(OSX用户)必须对这些目录具有执行(x)权限(最好授予所有人)，并且套接字应具有完整权限(wrx).您可以使用ls -lAd <file>进行检查，如果其中任何一个是符号链接，则需要检查链接指向的文件或目录.
You need to check the permissions on the socket file and its directories /var and /var/pgsql_socket. Your Rails app (OSX user) must have execute (x) permissions on these directories (preferably grant everyone permissions) and the socket should have full permissions (wrx). You can use ls -lAd <file> to check these, and if any of them are a symlink you need to check the file or dir the link points to.
您可以自行更改dir的权限，但是套接字由postgresql.conf中的postgres配置.可以在pg_hba.conf所在的目录中找到该目录(您必须找出哪个目录).设置权限后，您将需要重新启动postgresql.
You can change the permissions on the dir for youself, but the socket is configured by postgres in postgresql.conf. This can be found in the same directory as pg_hba.conf (You'll have to figure out which one). Once you've set the permissions you will need to restart postgresql.
# postgresql.conf should contain...
unix_socket_directory = '/var/run/postgresql' # dont worry if yours is different
#unix_socket_group = '' # default is fine here
#unix_socket_permissions = 0777 # check this one and uncomment if necessary.
我已经在Google上进行了快速搜索，您可能希望调查一下它是否相关.
这很可能导致find您的配置文件的任何尝试失败.
I've done a quick search on google which you may wish to look into to see if it is relavent.
This might well result in any attempt to find your config file failing.
http://www.postgresqlformac.com/server/howto_edit_postgresql_confi.html
错误消息:
在pg_hba.conf中找不到用户
User not found in pg_hba.conf
psql: FATAL: no pg_hba.conf entry for host "[local]", user "couling", database "main", SSL off
用户密码验证失败
psql: FATAL: password authentication failed for user "couling"
缺少unix套接字文件:
Missing unix socket file:
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
Unix套接字已存在，但服务器未在监听它.
Unix socket exists, but server not listening to it.
psql: could not connect to server: Connection refused
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
unix套接字文件上的坏文件权限:
psql: could not connect to server: Permission denied
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
这篇关于无法连接到本地PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
