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

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

  • Eep, it really, really shouldn't! When you say "latest version", please show the exact version number. Also, have you put any tablespaces on external storage? where's the PostgreSQL data directory located? – Craig Ringer Sep 22 '14 at 9:01
  • Also, pg_tblspc is a directory. The only way I can see this directory and just this directory randomly vanishing is filesystem corruption or a particularly badly behaved virus scanner or file sync tool. – Craig Ringer Sep 22 '14 at 11:53
  • I don't have any virus scanner. I don't know what tablespaces is, so I don't think I put it on external storage. – Blaszard Sep 22 '14 at 12:28
  • Hm. All I can tell you is that something's badly wrong. pg_tblspc does not just disappear on any system I've ever encountered, nor can I imagine a sane reason it would. It's going to be very hard to say what makes your system different without a lot more detail. – Craig Ringer Sep 22 '14 at 12:29
  • 2
    Were you able to find a solution for this @Gardecolo? I'm having the same issue after upgrading to Yosemite. – Donovan Sep 23 '14 at 17:26

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 /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):

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/postgres now starts the server normally and, at least for me, without any data loss.


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_tblspc,pg_twophase,pg_stat,pg_stat_tmp,pg_replslot,pg_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.

  • 53
    Just a suggestion for a more concise command: mkdir -p /usr/local/var/postgres/{pg_tblspc,pg_twophase,pg_stat_tmp}/ and touch /usr/local/var/postgres/{pg_tblspc,pg_twophase,pg_stat_tmp}/.keep – Nate Oct 17 '14 at 13:45
  • 25
    Those .keep files actually cause me some grief in the server logs: could not open temporary-files directory "pg_tblspc/.keep/PG_9.3_201306121/pgsql_tmp": Not a directory – funwhilelost Oct 20 '14 at 20:19
  • 13
    I was also missing pg_snapshots and pg_stat directories. – Jon Stevens Oct 20 '14 at 21:21
  • 8
    I also had to create one extra directory 'pg_replslot'. Except that it works fine. Thanks! – Lucas Jan 6 '15 at 16:52
  • 6
    experienced same as @Lucas for bottled postgres 9.4.0. I had to mkdir /usr/local/var/postgres/{pg_tblspc,pg_twophase,pg_stat_tmp,pg_replslot} – Patrick Farrell Feb 3 '15 at 19:21

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.

  • 3
    Needed to add mkdir -p /usr/local/var/postgres/pg_logical/{snapshots,mappings} – peter_v Mar 9 '15 at 15:06

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 fast to kill the PostgreSQL server but no luck. I also tried going after the process directly with kill PID but as soon as I did that a PostgreSQL process re-appeared with a different PID.

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

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

After that I was able to start PostgreSQL normally.

  • My plist was named slightly differently: launchctl unload ${HOME}/Library/LaunchAgents/homebrew.mxcl.postgresql.plist but basically that was also the same problem for me, and the same solution. – onekiloparsec Nov 3 '14 at 15:43

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 .plist file that starts PostgreSQL, you can find the data directory there:

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

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


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

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}

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

NOTE: This approach will delete existing databases

$ rm -r /usr/local/var/postgres
$ initdb -D /usr/local/var/postgres
  • 19
    Obviously deleting existing databases is not a minor exception here. This a bit like saying "I couldn't find /var/tmp so I reinstalled the operating system." – Adam Donahue Oct 20 '14 at 19:44
  • 4
    Oh, man, this is "cleaner" than anything I can think of :) Just hope some random copy-paster from the interwebz doesn't shoot this directly into their console without looking at it :) – Halil Özgür Oct 25 '14 at 13:31
  • 2
    Sorry for the down vote Greg but I recommend rewording your solution to make it explicit that this approach should only be used in development or if the user can afford to wipe their DB. – hraynaud Nov 7 '14 at 11:54
  • 1
    Why is this downvoted so much? On a dev server this is the right way. – Jordon Bedwell Aug 10 '18 at 13:59
  • @JordonBedwell even on a dev server is bad idea, unless you are playing with a single app using db on your computer.. It's like "I can't start my favorite code editor, let's reinstall the OS" – Andre Figueiredo Oct 2 at 20:52

Your Answer

