3

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.

  • Because this is self-answered in the question and you're not actually a member anymore. And this isn't good practice. And you answered your question by dumping the database and loading it normally -- perhaps we should just delette this question? – Evan Carroll Jan 8 at 23:45
  • @EvanCarroll Fair points. It has other, good answers, however, and I can imagine others trying to do this so I'm VtLO. – Michael Green Jan 9 at 1:58
  • I'm cool with keeping it if you want, I'm just not sure what he's asking and I'm not sure how others are to answer it or find it. Does he want to create a new tablespace and load a fresh dump? Or does he want to recover from a bad move? Or does he want to understand why a tablespace shouldn't be hosted on a volatile storage device? Or, even better -- the downsides of USB-SATA bridge (which isn't even mentioned here but USB bridges typically drop very useful SATA codes that the kernel transparently works around as best it can) – Evan Carroll Jan 9 at 2:00
  • I would say at this point, feel free to rewrite the question entirely to best salvage the answers. – Evan Carroll Jan 9 at 2:01
11

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.

  • Just one more question, in case you incidentally already have experienced this. The external HD is formated in exFAT and therefore eliminates all access restrictions. The logfiles unfortunately provide no hint at all… – m_br Mar 29 '14 at 3:02
  • 1
    PostgreSQL will not run reliably on FAT filesystems. It tries to detect them and refuse to run, but I don't think it can for tablespaces. Do not run PostgreSQL on a FAT file system, especially on a removable drive, it's a great way to make sure you lose your data. Shrink the exFAT partition and put a sensible filesystem on the freed space. (It's not just PostgreSQL; any database that relies on things like reliable write ordering, metadata consistency, etc is at risk of losing data on a FAT filesystem). – Craig Ringer Mar 29 '14 at 3:16
  • Not sure I understand the risk if you're already putting the data on an external? – Statwonk Jun 29 '14 at 0:14
  • @Statwonk If you lose the main DB but retain the external, it's no use to you: you need the main DB to read the table on the external. If you lose the external but keep the main DB, you've lost the data on the external and it may well make data on the main DB difficult to access/recover. So it weakens both parts. – Craig Ringer Jun 30 '14 at 3:07
  • Permissions cascade, in other words you will also need to check all folders on the dirpath (unix.stackexchange.com/questions/13858/…), You can use namei -l {path} to print the cascading dirpath permissions in linux, but in Mac you would have to check the dirpath permissions some other way. – jonnyjandles Sep 8 '18 at 0:40
1

You could create an extra cluster. I only tested it on Linux but it should work on other OS as well.

As it is a linux system the usb device uses the ext4 file system.

I created a fstab entry to make sure it will always be mounted in the same way.

UUID=xxxxx... /media/postgres ext4 rw,errors=remount-ro,noauto,users    0 2

Mount the external device.

mkdir /media/postgres
mount /media/postgres

The mounted device/partition has to be empty.

Check for existing clusters on your postgresql installation

pg_lsclusters

If you want your cluster to have the standardised port 5432, you have to dump the existing cluster with that port. Otherwise your cluster will get the next free port (5432, 5433, 5434...).

Now you create your external cluster.

sudo pg_createcluster 9.4 extern -d /media/external_drive/data/ -l /media/external_drive/pg94.log --start --start-conf=manual

Make sure the user postgres has access to this path including the 'x' flag. Otherwise you recieve an error like this:

could not access directory "/media/external_drive/data/": Permission denied
Error: initdb failed

If you like to have another cluster on your main drive just type

sudo pg_createcluster 9.4 main

Now the new cluster should appear.

pg_lsclusters

Now you can use the ports to change between the clusters. No port means -p 5432

sudo -u postgres createuser --superuser <sudo-user>
sudo -u postgres createuser --superuser <sudo-user> -p5433

Now you can create a database on the command line or using a gui (e.g. pgadmin3). Connect with localhost and the port you have chosen.

Restore your data. You do not need to specify the port if you use 5432.

psql -p 5433 my_db < /path/to/bexample.sql  # sql-dump
pg_restore -p 5433 -d my_db /path/to/example.dump  # custom-dump

To use your cluster after reboot just plug in the usb device, mount it and start you cluster.

mount /media/postgres
pg_ctlcluster 9.4 extern start
pg_ctlcluster 9.4 extern status  # check the status
1

I sense the solutions are quite too complex for a person who is starting to learn postgres. have you considered the following:

  1. using a linux host system install a containerization system ie. docker
  2. connect you HD and add a mount to that drive to use all the space
  3. use a Dockerfile to define a container using postgres and the bind volume connected to the mount in linux you created. postgres containers already defined postgres by version container

  4. once the container is running connect to the container's bash

docker exec -ti CONTAINER_NAME bash

  1. perform a pg_restore or psql command to restore the ~50GB dump file

This alternative will benefit you as you will learn postgres without much hassle and you will achieve the same.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.