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

Question

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.

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.

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

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

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:

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.

Answer 1

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.

To fix this you simply recreate the missing directories:

mkdir -p /usr/local/var/postgres/pg_commit_ts mkdir -p /usr/local/var/postgres/pg_dynshmem mkdir -p /usr/local/var/postgres/pg_logical/mappings mkdir -p /usr/local/var/postgres/pg_logical/snapshots mkdir -p /usr/local/var/postgres/pg_replslot mkdir -p /usr/local/var/postgres/pg_serial mkdir -p /usr/local/var/postgres/pg_snapshots mkdir -p /usr/local/var/postgres/pg_stat mkdir -p /usr/local/var/postgres/pg_stat_tmp mkdir -p /usr/local/var/postgres/pg_tblspc mkdir -p /usr/local/var/postgres/pg_twophase 

Or, more concisely (thanks to Nate):

mkdir -p /usr/local/var/postgres/{{pg_commit_ts,pg_dynshmem,pg_replslot,pg_serial,pg_snapshots,pg_stat,pg_stat_tmp,pg_tblspc,pg_twophase},pg_logical/{mappings,snapshots}} 

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

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.

touch /usr/local/var/postgres/{{pg_commit_ts,pg_dynshmem,pg_replslot,pg_serial,pg_snapshots,pg_stat,pg_stat_tmp,pg_tblspc,pg_twophase},pg_logical/{mappings,snapshots}}/.keep 

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

Answer 2

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:

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

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