Moving Tablespaces in PostgreSQL
How to Move PostgreSQL Tablespaces: A Step-by-Step Guide
Prerequisites:
Tablespaces are designed to allow Postgres clusters to be spread across multiple storage devices. Create tablespace creates a symbolic link in the pg_tblspc directory in the cluster’s data directory pointing to the newly-created tablespace directory.
Unfortunately, though there is a command to move tables and indexes between tablespaces, there is no command to move tablespaces to different directories. However, since Postgres 9.2, the process of moving tablespaces is quite simple:
Record the oid of the tablespace you want to move
Shut down the Postgres cluster
Move the tablespace directory, either within the same file system or to a different file system
Update the oid symbolic link that represents the moved tablespace to the new tablespace directory location
Restart the server
Here’s an example of moving a tablespace:
Step-by-Step Process to Move a Tablespace
$ mkdir /home/postgres/disk1
Connect to PostgreSQL
Start a PostgreSQL session and create a tablespace for demonstration:
$ psql test CREATE TABLESPACE TBS1 LOCATION '/home/postgres/disk1'; CREATE TABLE test_table (x int) TABLESPACE TBS1; INSERT INTO test_table VALUES (1); SELECT oid, * FROM pg_tablespace;
oid | spcname | spcowner | spcacl | spcoptions
——-+————-+———-+——–+————
1663 | pg_default | 10 | |
1664 | pg_global | 10 | |
16385 | TBS1 | 10 | |
Get the OID of the Tablespace
SELECT pg_tablespace_location(16385);
pg_tablespace_location
————————-
/home/postgres/disk1
\q
Shut Down the PostgreSQL Server
$ pg_ctl stop
Move the Tablespace Directory
$ mv /home/postgres/disk1 /home/postgres/disk2/
Update the Symbolic Link
$ cd $PGDATA/pg_tblspc/ $ ls -l
lrwxrwxrwx 1 postgres postgres 23 Sep 5 22:20 16385 -> /home/postgres/disk1
$ ln -fs /home/postgres/disk2 16385 $ ls -l
lrwxrwxrwx 1 root root 24 Sep 5 22:25 16385 -> /home/postgres/disk2
Restart the PostgreSQL Server
$ pg_ctl start
$ psql test
SELECT * FROM test_table;
x
—
1
Verify the Move
SELECT pg_tablespace_location(16385);
You should see the new path reflecting the move
pg_tablespace_location
/home/postgres/disk2