• 25 Oct, 2024
• 0 Comments
• 1 Min Read

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