Postgresql Database on external HD

My laptop has a small hard drive. I want to load a big data set. I have moved the entire /usr/local/var/postgres to the external hd, and symlinked it back. Now I'm getting this error,

psql: could not connect to server: Socket operation on non-socket
    Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

I have now found the problem. Changing the default data directory causes Postgres to refuse connections to the server. The online Manual is a little [sic!] unspecific about "config files in the same directory"…

The solution I used (with psql) is to:

CREATE TABLESPACE tablespacename OWNER username LOCATION '/Path/to/Folder'

then,

CREATE DATABASE databasename OWNER username TABLESPACE tablespacename

and finally

psql databasename < /Path/to/databasedump.sql

NOTE to future visitors of this question; please see the warning below from Craig Ringer regarding how to solve this problem properly.

Do NOT create a tablespace on a removable external drive, especially if you have other data in that PostgreSQL install you care about.

If the external drive is missing then PostgreSQL will fail to start, and be extremely difficult to repair.

I've submitted a patch to add more prominent warnings about this in the latest version's manual.

You had the right idea the first time - move the data directory in its entirety. You just didn't follow through with the full process. The PostgreSQL manual can't cover it all, because it's very dependent on details of the operating system, version, etc.

Since you symlinked the datadir you don't need to change your launchd configuration to point to the new datadir. I'd say your problem was probably filesystem permissions. What you should do in a situation like that is open up Console.app and check the system logs for messages relating to PostgreSQL startup. You should also check PostgreSQL's own log files. It can also be helpful to:

sudo -u postgres ls /path/to/data/directory/

to make sure PostgreSQL has the rights to "see" what's in the data directory in the new location.

StackExchange Database Administrators Q#62025, answer score: 13