postgresql 更新/升级brew后如何修复postgres

how to fix postgres after updating/upgrading brew

提问by Homan

I upgraded to mavericks and had some trouble installing/compiling new gems so I reinstalled xcode and did a brew update and upgrade. Gems work now, and even postgres continued to work for a while until a recent reboot. Now postgres seems to be having issues.

我升级到小牛并且在安装/编译新 gems 时遇到了一些问题，所以我重新安装了 xcode 并进行了 brew 更新和升级。Gems 现在可以工作了，甚至 postgres 也继续工作了一段时间，直到最近重新启动。现在 postgres 似乎有问题。

postgres:

postgres does not know where to find the server configuration file.
You must specify the --config-file or -D invocation option or set the PGDATA environment variable.


brew info postgres:

postgresql: stable 9.3.2 (bottled)
http://www.postgresql.org/
Conflicts with: postgres-xc
/usr/local/Cellar/postgresql/9.2.4 (2842 files, 39M)
  Built from source
/usr/local/Cellar/postgresql/9.3.2 (2924 files, 39M) *
  Poured from bottle

postgres -D /usr/local/var/postgres:

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

What should I do now to get my database working again?

我现在应该怎么做才能让我的数据库再次工作？

回答by sent1nel

$ brew tap homebrew/versions
$ brew install postgresql92
$ brew switch postgresql 9.2.8 # might need to check this one, could be newer by the time you read this
$ pg_dumpall > ninedottwo-dump
$ brew uninstall postgresql92
$ brew switch postgresql 9.3.4 # again, check version
$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start
$ createdb # i got an error about my database not existing, so i had to run this
$ psql < ninedottwo-dump
$ bundle exec rails s

Thanks to Peter for getting me started in the right direction.

感谢彼得让我朝着正确的方向开始。

edit: this'll teach me to upgrade everything all at once...

编辑：这会教我一次升级所有东西......

回答by Peter Eisentraut

You need to get a copy of PostgreSQL 9.2 installed somehow, to be able to access the existing data directory.

您需要以某种方式安装 PostgreSQL 9.2 的副本，以便能够访问现有的数据目录。

Options for installing 9.2 via Homebrew include:

通过 Homebrew 安装 9.2 的选项包括：

• Get a checkout of Homebrew from before the postgresqlformula was upgraded to 9.3.
• Install postgresql92from homebrew/versionstap.
• Install postgresql-9.2from petere/postgresqltap (disclosure: that's my project).
• Install from source.

• 在postgresql公式升级到 9.3之前检查 Homebrew 。
• postgresql92从homebrew/versions水龙头安装。
• postgresql-9.2从petere/postgresql水龙头安装（披露：那是我的项目）。
• 从源安装。

Then you ought to upgrade that to 9.3 using either pg_dumpor pg_upgrade.

然后您应该使用pg_dump或将其升级到 9.3 pg_upgrade。

pg_upgradecomes for this very purpose, and using it is straightforward. Just make sure you're specifying the right paths for the old data and binaries ( -d and -b ) and the right paths to the new data and binaries (-D and -B):

pg_upgrade正是出于这个目的，使用它很简单。只需确保为旧数据和二进制文件（ -d 和 -b ）指定正确的路径，并为新数据和二进制文件（-D 和 -B）指定正确的路径：

pg_upgrade \
-d /usr/local/var/postgres/9.2/ -D /usr/local/var/postgres/9.3/ \
-b /usr/local/Cellar/postgresql/9.2.4/bin/ -B /usr/local/Cellar/postgresql/9.3.2/bin/

If everything goes fine, you can then cleanup old versions with brew cleanup postgresql, and since a new version of PostgreSQL means new versions of the native libraries that are used by the Ruby pg gem, you may want to recompile it:

如果一切顺利，您可以使用 清除旧版本brew cleanup postgresql，并且由于新版本的 PostgreSQL 意味着 Ruby pg gem 使用的本机库的新版本，您可能需要重新编译它：

gem uninstall pg
ARCHFLAGS="-arch x86_64" gem install pg

回答by Dan Williams

For 9.2 to 9.5, on El Capitan, I ran into a couple problems which might help others.

对于 9.2 到 9.5，在 El Capitan 上，我遇到了一些可能对其他人有帮助的问题。

It seems now the name of the package used in the switch statement has changed to include the version number (postgresql92 vs postgresql). So, I needed to have the added step of unlinking the current version of postgres:

现在似乎 switch 语句中使用的包的名称已更改为包含版本号（postgresql92 与 postgresql）。因此，我需要添加取消链接当前版本的 postgres 的步骤：

brew unlink postgresql
brew link postgresql92
brew switch postgresql92 9.2.13 # match version

In order to perform the dump I needed to start the postgres server,

为了执行转储，我需要启动 postgres 服务器，

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

But then I ran into the dreaded:

但后来我遇到了可怕的：

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

I check out the logs and found this:

我查看了日志，发现了这个：

could not open directory "pg_tblspc": No such file or directory

According to this answerby Donovan, Yosemite and El Capitan remove some needed directories. So I also needed to add those directories back in using this awesome command by Nate

根据此答案由多诺万，优胜美地埃尔卡皮坦删除一些必需的目录。所以我还需要使用Nate 的这个很棒的命令重新添加这些目录

mkdir /usr/local/var/postgres/{pg_tblspc,pg_twophase,pg_stat,pg_stat_tmp,pg_replslot,pg_snapshots}/

So the full updated version is:

所以完整的更新版本是：

brew tap homebrew/versions
brew install postgresql92
brew unlink postgresql
brew switch postgresql92 9.2.13 # might need to check this one, could be newer by the time you read this
mkdir /usr/local/var/postgres/{pg_tblspc,pg_twophase,pg_stat,pg_stat_tmp,pg_replslot,pg_snapshots}/
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start
pg_dumpall > ninedottwo-dump
pg_ctl -D /usr/local/var/postgres stop
brew unlink postgresql92
brew uninstall postgresql92
brew switch postgresql 9.5.2 # again, check version
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start
createdb # i got an error about my database not existing, so i had to run this
psql < ninedottwo-dump
bundle install # need to make sure you have pg gem for 9.5
bundle exec rails s

All credit goes to sentinel, Nate, and Donovan. Thanks so much!

所有功劳都归功于哨兵、内特和多诺万。非常感谢！