PostgreSQL – How to Move a Tablespace

postgresqlpostgresql-9.3

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.

Best Answer

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)
  1. Stop postgres
$ pg_ctl -D $PGDATA stop
  1. Move directory from old one to new one
$ mv /old/dir /new/dir

or

$ cp -r /old/dir /new/dir
$ rm -rf /old/dir
  1. Change tablespace's link to new directory
$ cd $PGDATA/pg_tblspc
$ rm 24580
$ ln -s /new/dir 24580
  1. 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)
Related Question