PostgreSQL Tutorial: Moving a Tablespace

Summary: In this tutorial, you will learn how to move a tablespace in PostgreSQL.

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:

1. Record the oid of the tablespace you want to move
2. Shut down the PostgreSQL cluster
3. Move the tablespace directory, either within the same file system or to a different file system
4. Update the oid symbolic link that represents the moved tablespace to the new tablespace directory location
5. 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