Ruby-on-rails 无法连接到本地 PostgreSQL

Can not connect to local PostgreSQL

提问by Andy Harvey

I've managed to bork my local development environment.

我已经成功地破坏了我的本地开发环境。

All my local Rails apps are now giving the error:

我所有的本地 Rails 应用程序现在都出现错误：

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.

我不知道是什么原因造成的。

While searching for a solution I've updated all bundled gems, updated system gems, updated MacPorts. No joy.

在寻找解决方案时，我更新了所有捆绑的 gem、更新的系统 gem、更新的 MacPorts。没有喜悦。

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.

其他人在从 OSX Leopard 升级到 Lion 时报告了这个问题，因为混淆了应该使用哪个版本的 Postgres（即，OSX 版本或 MacPorts 版本）。我已经运行 Lion 几个月了，所以现在应该发生这种情况似乎很奇怪。

I'm reluctant to mess around too much without first understanding what the problem is. How can I debug this methodically?

我不愿意在没有首先了解问题所在的情况下乱七八糟。我怎样才能有条不紊地调试这个？

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?

如何确定系统上有多少个 PostgreSQL 版本、正在访问哪个版本以及它位于何处？如果使用了错误的 PostgreSQL，我该如何解决这个问题？

Sorry for the noob questions. I'm still learning how this works! Thanks for any pointers.

对不起，菜鸟问题。我还在学习这是如何工作的！感谢您的指点。

EDIT

编辑

Some updates based on suggestions and comments below.

基于以下建议和评论的一些更新。

I tried to run pg_lsclusterswhich returned a command not founderror.

我试图运行pg_lsclusters它返回一个command not found错误。

I then tried to local my pg_hba.conf file and found these three sample files:

然后我尝试本地我的 pg_hba.conf 文件并找到这三个示例文件：

/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

So I assume 3 versions of PSQL are installed? Macports, OSX default and ???.

所以我假设安装了 3 个版本的 PSQL？Macports、OSX 默认和???。

I then did a search for the launchctl startup script ps -ef | grep postgreswhich returned

然后我搜索了ps -ef | grep postgres返回的launchctl启动脚本

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

I've posted the contents of postgresql84-server.wrapper at http://pastebin.com/Gj5TpP62.

我已经在http://pastebin.com/Gj5TpP62 上发布了 postgresql84-server.wrapper 的内容。

I tried to run port load postgresql184-serverbut received an error Error: Port postgresql184-server not found.

我试图运行port load postgresql184-server但收到一个错误Error: Port postgresql184-server not found。

I'm still very confused how to fix this, and appreciate any "for dummies" pointers.

我仍然很困惑如何解决这个问题，并感谢任何“傻瓜”指针。

Thanks!

谢谢！

EDIT2

编辑2

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 遇到一些问题之后开始的。我的本地 Rails 应用程序崩溃，并出现“找不到守护进程 gem”的应用程序错误。然后我经历了一系列包更新、gem 更新、端口更新和 brew 更新来尝试找到问题。

Could this error be an issue with daemondo?

这个错误可能是 daemondo 的问题吗？

采纳答案by Philip Couling

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).

这真的看起来像一个文件权限错误。Unix 域套接字是文件，并且与其他任何套接字一样具有用户权限。似乎尝试访问数据库的 OSX 用户没有访问套接字文件的文件权限。为了确认这一点，我在 Ubuntu 和 psql 上做了一些测试以尝试生成相同的错误（包括在下面）。

You need to check the permissions on the socket file and its directories /varand /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.

您需要检查套接字文件及其目录/var和/var/pgsql_socket. 您的 Rails 应用程序（OSX 用户）必须对这些目录具有执行 (x) 权限（最好授予所有人权限）并且套接字应具有完全权限 (wrx)。您可以使用ls -lAd <file>来检查这些，如果其中任何一个是符号链接，您需要检查链接指向的文件或目录。

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. 这可以在与pg_hba.conf（您必须弄清楚是哪个）相同的目录中找到。设置权限后，您将需要重新启动 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.

---

---

EDIT:

编辑：

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 findyour config file failing.

我已经在谷歌上进行了快速搜索，您可能希望查看它是否相关。这很可能会导致对find您的配置文件的任何尝试失败。

http://www.postgresqlformac.com/server/howto_edit_postgresql_confi.html

http://www.postgresqlformac.com/server/howto_edit_postgresql_confi.html

---

---

Error messages:

错误信息：

User not found in pg_hba.conf

在 pg_hba.conf 中找不到用户

psql: FATAL:  no pg_hba.conf entry for host "[local]", user "couling", database "main", SSL off

User failed password auth:

用户密码验证失败：

psql: FATAL:  password authentication failed for user "couling"

Missing unix socket file:

缺少Unix套接字文件：

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 socket exists, but server not listening to it.

Unix 套接字存在，但服务器不侦听它。

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"?

Bad file permissions on unix socket file:

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"?

回答by wildplasser

My gut feeling is that this is (again) a mac/OSX-thing: the front end and the back end assume a different location for the unix-domain socket (which functions as a rendezvouspoint).

我的直觉是，这（再次）是 mac/OSX 的东西：前端和后端为 unix 域套接字（用作集合点）假定不同的位置。

Checklist:

清单：

• Is postgres running: ps aux | grep postgres | grep -v grepshould do the trick
• Where is the socket located: find / -name .s.PGSQL.5432 -ls(the socket used to be in /tmp; you could start looking there)
• even if you locate the (unix-domain) socket, the client could use a different location. (this happens if you mix distributions, or of you have a distribution installed someplace and have another (eg from source) installation elsewhere), with client and server using different rendez-vousaddresses.

• postgres 是否正在运行：ps aux | grep postgres | grep -v grep应该可以解决问题
• 套接字位于何处：（find / -name .s.PGSQL.5432 -ls该套接字曾经在 /tmp 中；您可以开始查找那里）
• 即使您找到（unix 域）套接字，客户端也可以使用不同的位置。（如果您混合发行版，或者您在某个地方安装了一个发行版并在其他地方安装了另一个（例如从源代码）安装，就会发生这种情况），客户端和服务器使用不同的集合地址。

If postgres is running, and the socket actually exists, you could use:

如果 postgres 正在运行，并且套接字确实存在，则可以使用：

• psql -h /the/directory/where/the/socket/was/found mydbname

• psql -h /the/directory/where/the/socket/was/found mydbname

(which attempts to connect to the unix-domain socket)

（它尝试连接到 unix 域套接字）

; you should now get the psql prompt: try \dand then \qto quit. You could also try:

; 您现在应该得到 psql 提示：尝试\d然后\q退出。你也可以试试：

• psql -h localhost mydbname.

• psql -h localhost mydbname.

(which attempts to connect to localhost (127.0.0.1)

（它尝试连接到本地主机（127.0.0.1）

If these attempts fail because of insufficient authorisation, you could alter pg_hba.conf (and SIGHUP or restart) In this case: also check the logs.

如果这些尝试由于授权不足而失败，您可以更改 pg_hba.conf（和 SIGHUP 或重新启动）在这种情况下：还要检查日志。

A similar question: Can't get Postgres started

一个类似的问题：无法启动 Postgres

Note: If you can get to the psql prompt, the quick fix to this problem is just to change your config/database.yml, add:

注意：如果您可以进入 psql 提示符，快速解决此问题的方法就是更改您的config/database.yml，添加：

host: localhost

host: localhost

or you could try adding:

或者您可以尝试添加：

host: /the/directory/where/the/socket/was/found

host: /the/directory/where/the/socket/was/found

In my case, host: /tmp

就我而言， host: /tmp

回答by Tom Harrison

Try uninstalling the pg gem (gem uninstall pg) then reinstalling -- if you use bundler, then bundle install, else gem install pg. Also, make sure path picks up the right version: Lion has a version of posgresql (prior versions didn't) and it may be in the path before your locally installed version (e.g. MacPorts, homebrew).

尝试卸载 pg gem ( gem uninstall pg) 然后重新安装 - 如果您使用 bundler，则bundle install，否则gem install pg。另外，确保路径选择正确的版本：Lion 有一个 posgresql 版本（以前的版本没有），它可能在本地安装版本（例如 MacPorts、自制软件）之前的路径中。

In my case: homebrew install of postgresql, updated postgresql, rails, etc. and then got this error. Uninstalling and reinstalling the pg gem did it for me.

就我而言：自制安装 postgresql，更新 postgresql、rails 等，然后出现此错误。卸载并重新安装 pg gem 为我做到了。

回答by Ben Walding

The location of the socket file is baked into the gem at compile time. Thus you need to rebuild your pg gem.

套接字文件的位置在编译时被烘焙到 gem 中。因此你需要重建你的 pg gem。

gem pristine pg
# or
bundle exec gem pristine pg

This should resolve that particular issue.

这应该可以解决该特定问题。

回答by Neal

If you're getting a similar error:

如果您收到类似的错误：

psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

This might do the trick (it did for me):

这可能会奏效（它对我有用）：

initdb /usr/local/var/postgres -E utf8

The directory specified should be different if you're not using OSX/Brew.

如果您不使用 OSX/Brew，则指定的目录应该不同。

Note: This is not the exact error message seen above, but this thread is the first result for that error message.

注意：这不是上面看到的确切错误消息，但此线程是该错误消息的第一个结果。

回答by FireDragon

what resolved this error for me was deleting a file called postmaster.pid in the postgres directory. please see my question/answer using the following link for step by step instructions. my issue was not related to file permissions:

为我解决此错误的是删除 postgres 目录中名为 postmaster.pid 的文件。请使用以下链接查看我的问题/答案以获取分步说明。我的问题与文件权限无关：

psql: could not connect to server: No such file or directory (Mac OS X)

psql：无法连接到服务器：没有这样的文件或目录（Mac OS X）

the people answering this question dropped a lot of game though, thanks for that! i upvoted all i could

回答这个问题的人虽然放弃了很多游戏，但谢谢！我尽我所能投票

回答by Frans

This is how I solved that error message, based partly on wildplasser's answer.

这就是我解决该错误消息的方法，部分基于 Wildplasser 的回答。

find / -name .s.PGSQL.5432 -ls 2> /dev/null
  => ... /tmp/.s.PGSQL.5432

So, there's my socket or whatever, but the client looks for it at:

所以，有我的套接字或其他什么，但客户端在以下位置寻找它：

/var/run/postgresql/.s.PGSQL.5432

So quite simply make a symbolic link to the /tmp/.s.PGSQL.5432:

所以很简单地建立一个符号链接到/tmp/.s.PGSQL.5432：

sudo ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

Hope this helps to anyone. The seems kind of wrong, but hey, it works!

希望这对任何人都有帮助。这似乎有点错误，但是嘿，它有效！

回答by Mark Swardstrom

I started getting this after upgrading to a new postgres - I didn't realize I had hold data files.

升级到新的 postgres 后我开始得到这个 - 我没有意识到我拥有数据文件。

First I tried to start the postgres server:

首先，我尝试启动 postgres 服务器：

postgres -D /usr/local/var/postgres

which is how I saw this error

这就是我看到这个错误的方式

FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.0, which is not compatible with this version 9.3.5.

So then I found this answer on SO - related to an incompatibility error: https://serverfault.com/questions/342626/how-do-i-upgrade-postgresl-database-incompatibility-error

然后我在 SO 上找到了这个答案 - 与不兼容错误有关：https: //serverfault.com/questions/342626/how-do-i-upgrade-postgresl-database-incompatibility-error

This is what fixed it

这就是修复它的原因

mv /usr/local/var/postgres /usr/local/var/postgres.old
initdb -D /usr/local/var/postgres

回答by purchas

Just confirming I had a similar issue on PSQL and Django,

只是确认我在 PSQL 和 Django 上有类似的问题，

Looked like because my psql server was not shut down correctly and the postmaster.pid file was still present (should be deleted on proper shutdown automatically) in my postgres folder.

看起来是因为我的 psql 服务器没有正确关闭并且 postmaster.pid 文件仍然存在（应该在正确关闭时自动删除）在我的 postgres 文件夹中。

Deleted this and all good

删除了这一切都很好

回答by respondcreate

I was getting this same error (it turns out it was an error with postmaster.pid. Here's how I got postgres up and running again (credit to Ricardo Burillo for the fix):

我遇到了同样的错误（事实证明这是一个错误postmaster.pid。这是我重新启动并运行 postgres 的方法（归功于 Ricardo Burillo 的修复）：

$ rm /usr/local/var/postgres/postmaster.pid 
$ pg_resetxlog -f /usr/local/var/postgres