April 20, 2024
Summary: In this tutorial, you will learn how to move a tablespace in PostgreSQL.
Table of Contents
Introduction
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 PostgreSQL 9.2, the process of moving tablespaces is quite simple:
- Record the oid of the tablespace you want to move
- Shut down the PostgreSQL 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
Example
Here’s an example of moving a tablespace.
First, we need to create a empty directory for the tablespace:
$ mkdir /data/pgsql/test_tblspc
Second, connect to the database as a super user, and create a tablespace based on the directory:
CREATE TABLESPACE test_tblspc LOCATION '/data/pgsql/test_tblspc';
CREATE TABLE test_table (x int) TABLESPACE test_tblspc;
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 | test_tblspc | 10 | |
SELECT pg_tablespace_location(16385);
pg_tablespace_location
-------------------------
/data/pgsql/test_tblspc
Third, shut down the PostgreSQL cluster, and move the tablespace directory:
$ pg_ctl stop
$ mv /data/pgsql/test_tblspc /data/pgsql/test2_tblspc/
$ cd $PGDATA/pg_tblspc/
$ ls -l
lrwxrwxrwx 1 postgres postgres 23 Sep 5 22:20 16385 -> /data/pgsql/test_tblspc
$ ln -fs /data/pgsql/test2_tblspc 16385
$ ls -l
lrwxrwxrwx 1 root root 24 Sep 5 22:25 16385 -> /data/pgsql/test2_tblspc
$ pg_ctl start
Finally, we can verify the table data, and confirm the new tablespace location:
SELECT * FROM test_table;
x
---
1
SELECT pg_tablespace_location(16385);
pg_tablespace_location
--------------------------
/data/pgsql/test2_tblspc