How to Migrate a Tablespace in PostgreSQL: Complete Guide

When working with large amounts of data in PostgreSQL, there is often a need to optimize disk space usage and improve system performance. One of the tools that helps with this is tablespace. Transferring a tablespace to PostgreSQL allows you to more flexibly distribute data across disks, free up space on the main disk, or organize data storage on a faster drive.

In this article, we will discuss how to properly migrate a tablespace to PostgreSQL, what steps need to be taken, and what to pay attention to in order to minimize risks and avoid database downtime.

The article on the page is based on my many years of experience with this technology and is presented to you as a short instruction, but if you want to get acquainted with it in more detail, then I recommend that you go to the official documentation on PostgreSQL.

 

In this example, we will migrate tablespaces named inzhener_1 and inzhener_2 from the directory /u01/pg_tablespace to /u02/pg_tablespace.

sql> SELECT oid, spcname, pg_tablespace_location(oid) FROM pg_tablespace;

The query above shows us the absolute path to the directories where all tablespaces are located.

 

Contents of the article:

1. Creating directories.
2. We assign the owner of the directories.
3. Stopping the database.
4. Let's look at the links.
5. Migrate tablespaces.
6. We remove old links.
7. We create new links.
8. Launching the database.
9. Checking the result.

 

1. Creating directories.

The first thing we will do is create new directories for the tablespaces we will be migrating. To create directories, we will use a simple Linux command mkdir. The command will create empty directories.

$. mkdir -p /u02/pg_tablespace/inzhener_1

$. mkdir -p /u02/pg_tablespace/inzhener_2

---

 

2. We assign the owner of the directories.

After we have created the directories, the next step is to make sure that the owner of these directories is the postgres user.

$. chown -R postgres:postgres /u02/pg_tablespace/inzhener_1

$. chown -R postgres:postgres /u02/pg_tablespace/inzhener_2

---

 

3. Stopping the database.

Before migrating tablespaces, it is important to stop the database.

$. pg_ctl stop

---

 

4. Let's look at the links.

All links in the PostgreSQL database that are related to tablespaces are located in the $PGDATA/pg_tblspc directory, in my case the full path is /app/postgresql/15/pg_dbcluster_1/data/pg_tblspc.

$. ls -l /app/postgresql/15/pg_dbcluster_1/data/pg_tblspc

We need to remember or write down links 16405 and 16406 and the paths to the directories somewhere, because after moving the tablespaces we will reassign them with the same names. THIS IS IMPORTANT!

---

 

5. Migrate tablespaces.

Now it's time to move our tablespaces to our new directories. To do this we'll use the Linux command - cp.

$. cp -r /u01/pg_tablespace/inzhener_1/* /u02/pg_tablespace/inzhener_1

$. cp -r /u01/pg_tablespace/inzhener_2/* /u02/pg_tablespace/inzhener_2

---

 

6. We remove old links.

Now we need to remove the old links.

$. rm 16405

$. rm 16406

---

 

7. We create new links.

Now, instead of the old links, we need to create new ones pointing to the new directories that we created and moved the tablespaces there.

$. ln -s /u02/pg_tablespace/inzhener_1 16405

$. ln -s /u02/pg_tablespace/inzhener_2 16406

Check.

$. ls -l /app/postgresql/15/pg_dbcluster_1/data/pg_tblspc

---

 

8. Let's launch the database.

$. pg_ctl start

---

 

9. Checking the result.

Once we have done everything, we connect to the database and run a query to view all tablespaces.

sql> SELECT oid, spcname, pg_tablespace_location(oid) FROM pg_tablespace;

As we can see in the screenshot, as a result the directory from /u01 has successfully changed to /u02, which means we did everything correctly.

---

 

In conclusion, colleagues, today we successfully completed such an operation as moving table spaces to another directory, and after we have done everything and checked it, now we can delete the old directory with table spaces.