1

I am using a workstation that has 3 different hard drives. Some time ago I got a large database to reverse engineer that would not fit in the system drive. Therefore I created a new table-space in one of the the other hard drives, that at the time was mounted on the /media/Work1 folder.

Everything was working fine, until today at startup the system decided to mount the drive on the /media/Work folder. Naturally, Postgres can no longer access the databases in that table-space.

How can I change the location of a table-space in Postgres? I am aware of the pg_tablespace_location function, but not of somthing to modify it.

  • 1
    Just some small clarifications needed. PostgreSQL will possibly only be able to access data on mounted drives. I am unaware that PostgreSQL can access unmounted drives. That said you might be even talking about mount points in which case you should add some details on how your mount points related to the installed drives. Or why not just simply unmount /media/Work and instead mount back /media/Work1. Or am I missing something totally obvious? Please edit your question and add as much details as possible. – hot2use Mar 5 '18 at 9:21
  • A tablespace is essentially only a symlink from inside the data directory to the actual tablespace directory. When Postgres is stopped you should be able to simply change the symlink to the new mount point without Postgres noticing that – a_horse_with_no_name Mar 5 '18 at 9:41
3

The tablespace location is defined by a symbolic link in the pg_tblspc directory (this is why, by the way, you can only use custom tablespaces on systems that have symlinks, as stated in the documentation):

~/9.5/main/pg_tblspc$ ls -l
total 0
lrwxrwxrwx 1 postgres postgres 13 Mar  5 10:33 132568 -> /tablespace2/

As user postgres, you can change the location this symlink points to:

postgres@local:~/9.5/main/pg_tblspc$ ln -snf /tablespace/ 132568

This will fix your tablespace location. As a precaution, I'd do this only when the database is not running.

However, you have to be sure why the location changed in the first place and that it won't change back or to anything else.

  • I never stop being amazed at how cleverly simple some things are in Postgres. – Luís de Sousa Mar 5 '18 at 12:45

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.