11

Is there a way to physically move a PostgreSQL 9.3 tablespace from /old/dir to /new/dir? I'd like to just mv the directory and tell PostgreSQL that the tablespace is now located at /new/dir. Looks like ALTER TABLESPACE only lets you rename.

I'd like to avoid creating a new tablespace and moving the database to it. I assume that would be a slow copy process that requires a lot of time and disk space. I also want to avoid symlinking /old/dir to /new/dir.

  • move /old/dir to veryold/dir , mount new/dir to old/dir , copy data from ver_old dir to old/dir ? – simplexio Feb 16 '16 at 9:51
10

I assume that your tablespace name is tblspc.

(1) Get Oid of your tablespace

test=# SELECT oid,spcname FROM pg_tablespace WHERE spcname = 'tblspc';
  oid  | spcname 
-------+---------
 24580 | tblspc
(1 row)

(2) Stop postgres

$ pg_ctl -D $PGDATA stop

(3) Move directory from old one to new one

$ mv /old/dir /new/dir

or

$ cp -r /old/dir /new/dir
$ rm -rf /old/dir

(4) Change tablespace's link to new directory

$ cd $PGDATA/pg_tblspc
$ rm 24580
$ ln -s /new/dir 24580

(5) Start postgres

$ pg_ctl -D $PGDATA start

Then, you can see the tablespace's directory has changed.

test=# SELECT pg_tablespace_location(24580);
 pg_tablespace_location
------------------------
 /new/dir
(1 row)

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.