postgresql 安装最新版本的 OS X（Yosemite 或 El Capitan）后缺少“pg_tblspc”

`pg_tblspc` missing after installation of latest version of OS X (Yosemite or El Capitan)

提问by Blaszard

I use postgres from homebrew in my OS X, but when I reboot my system, sometimes the postgres doesn't start after the reboot, and so I manually tried to start it with postgres -D /usr/local/var/postgres, but then the error occurred with the following message: FATAL: could not open directory "pg_tblspc": No such file or directory.

我在 OS X 中使用自制软件中的 postgres，但是当我重新启动系统时，有时重新启动后 postgres 不会启动，因此我手动尝试使用 启动它postgres -D /usr/local/var/postgres，但随后出现错误并显示以下消息：FATAL: could not open directory "pg_tblspc": No such file or directory.

The last time it occurred, I couldn't get it to the original state, so I decided to uninstall the whole postgres system and then re-installed it and created users, tables, datasets, etc... It was so disgusting, but it frequently occurs on my system, say once in a few months.

上次出现的时候，无法恢复到原来的状态，于是决定卸载整个postgres系统，然后重新安装，创建用户、表、数据集等……好恶心，但是它经常出现在我的系统上，比如几个月一次。

So why does it lose the pg_tblspcfile frequently? And is there anything that I can do to avoid the loss of the file?

那么为什么会pg_tblspc频繁丢失文件呢？有什么办法可以避免文件丢失吗？

I haven't upgraded my homebrew and postgres to the latest version (i.e. I've been using the same version). Also, all the things that I did on the postgres database is delete the table and populate the new data every day. I haven't changed the user, password, etc...

我还没有将我的自制软件和 postgres 升级到最新版本（即我一直在使用相同的版本）。此外，我在 postgres 数据库上所做的所有事情都是每天删除表并填充新数据。我没有更改用户，密码等...

EDIT (mbannert): I felt the need to add this, since the thread is the top hit on google for this issue and for many the symptom is different. Homebrewers likely will encounter this error message:

编辑（mbannert）：我觉得有必要添加这个，因为这个线程是谷歌上这个问题的热门话题，而且对于许多人来说，症状是不同的。Homebrewers 可能会遇到此错误消息：

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

So, if you just experienced this after the Yosemite upgrade you now you're covered for now reading this thread.

因此，如果您在优胜美地升级后刚刚经历过这种情况，那么您现在可以阅读此线程。

回答by Donovan

Solved... in part.

解决了......部分。

Apparently, Installing the latest versions of OS X (e.g. Yosemite or El Capitan) removes some directories in /usr/local/var/postgres.

显然，安装最新版本的 OS X（例如 Yosemite 或 El Capitan）会删除/usr/local/var/postgres.

To fix this you simply recreate the missing directories:

要解决此问题，您只需重新创建丢失的目录：

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

Or, more concisely (thanks to Nate):

或者，更简洁（感谢Nate）：

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

Rerunning pg_ctl start -D /usr/local/var/postgresnow starts the server normally and, at least for me, without any data loss.

pg_ctl start -D /usr/local/var/postgres现在重新运行可以正常启动服务器，至少对我来说，没有任何数据丢失。

UPDATE

更新

On my system, some of those directories are empty even when Postgres is running. Maybe, as part of some "cleaning" operation, Yosemite removes any empty directories? In any case, I went ahead and created a '.keep' file in each directory to prevent future deletion.

在我的系统上，即使 Postgres 正在运行，其中一些目录也是空的。也许，作为某些“清理”操作的一部分，优胜美地会删除任何空目录？无论如何，我继续在每个目录中创建了一个“.keep”文件，以防止将来被删除。

touch /usr/local/var/postgres/{pg_tblspc,pg_twophase,pg_stat,pg_stat_tmp,pg_replslot,pg_snapshots}/.keep

Note: Creating the .keepfile in those directories will create some noise in your logfile, but doesn't appear to negatively affect anything else.

注意：.keep在这些目录中创建文件会在您的日志文件中产生一些干扰，但似乎不会对其他任何内容产生负面影响。

回答by tony_k

Donavan's answer is spot on, I just wanted to add that as i did different things with the database (e.g. rake db:test), it went looking for different directories that haven't been mentioned above and would choke when they weren't present, in my case pg_logical/mappings, so you may want to setup a terminal running:

Donavan的回答是正确的，我只是想补充一点，因为我对数据库做了不同的事情（例如rake db:test），它会寻找上面没有提到的不同目录，当它们不存在时会窒息，在我的情况pg_logical/mappings，所以你可能想设置一个终端运行：

tail -f /usr/local/var/postgres/server.log

and watch it for missing folders while you go thru your typical database activities.

并在您执行典型的数据库活动时观察它是否丢失了文件夹。

回答by MCP

This is slightly off-topic but worth noting here as part of the PostgreSQL Yosemite recovery process. I had the same issue as above AND I had an issue with PostgreSQL "seemingly" running in the background so even after adding directories I couldn't restart. I tried using pg_ctl stop -m fastto kill the PostgreSQL server but no luck. I also tried going after the process directly with kill PIDbut as soon as I did that a PostgreSQL process re-appeared with a different PID.

这有点偏离主题，但作为 PostgreSQL Yosemite 恢复过程的一部分，这里值得注意。我遇到了与上面相同的问题，并且我在后台“看似”运行 PostgreSQL 时遇到了问题，因此即使添加目录后我也无法重新启动。我尝试使用pg_ctl stop -m fast杀死 PostgreSQL 服务器，但没有成功。我还尝试直接使用该进程进行处理，kill PID但是一旦我这样做，PostgreSQL 进程就会以不同的 PID 重新出现。

The key ended up being a .plistfile that Homebrew had loaded... The fix for me ended up being:

密钥最终成为.plistHomebrew 加载的文件......我的修复最终是：

launchctl unload /Users/me/Library/LaunchAgents/homebrew.mxcl.postgresql92.plist

After that I was able to start PostgreSQL normally.

之后我就可以正常启动PostgreSQL了。

回答by tee

The missing directories need to be present in your PostgreSQL data directory. The default data directory is /usr/local/var/postgres/. If you have set up a different data directory, you need to re-create the missing directories there. If you modified the homebrew-recommended .plistfile that starts PostgreSQL, you can find the data directory there:

丢失的目录需要存在于您的 PostgreSQL 数据目录中。默认数据目录是/usr/local/var/postgres/. 如果您设置了不同的数据目录，则需要在那里重新创建丢失的目录。如果你修改了.plist启动 PostgreSQL的 homebrew-recommended文件，你可以在那里找到数据目录：

cat ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

(it's the -Doption you started postgres with:)

（这是-D您开始使用 postgres的选项：）

  <key>ProgramArguments</key>
  <array>
    <string>/usr/local/bin/postgres</string>
    <string>-D</string>
    <string>/usr/local/pgsql/data</string>

In the example above, you'd create the missing directories in /usr/local/pgsql/data, like so:

在上面的示例中，您将在 中创建缺少的目录/usr/local/pgsql/data，如下所示：

cd /usr/local/pgsql/data
mkdir {pg_tblspc,pg_twophase,pg_stat,pg_stat_tmp,pg_replslot,pg_snapshots,pg_logical}
mkdir pg_logical/{snapshots,mappings}

回答by Greg

Creating the missing directories certainly works but I fixed it by reinititializing postgres db, this is a cleaner approach to avoid future problems.

创建丢失的目录当然有效，但我通过重新初始化 postgres db 来修复它，这是一种避免未来问题的更清晰的方法。

NOTE: This approach will delete existing databases

注意：此方法将删除现有数据库

$ rm -r /usr/local/var/postgres
$ initdb -D /usr/local/var/postgres