无法连接到本地 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.
谢谢！
编辑2
这个问题是在我在使用 daemondo 时遇到一些问题后开始的.我的本地 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.
这个错误可能是 daemondo 的问题吗?
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 来检查这些，如果其中任何一个是符号链接，您需要检查链接指向的文件或目录.
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.
您可以自己更改目录的权限，但是套接字是由 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.
<小时>
我在谷歌上进行了快速搜索，您可能希望查看它是否相关.这很可能导致 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屋！
