PostgreSQL - How to migrate a tablespace

Colleagues hello to all.

In today's article, we will talk about how you can move tablespaces to another partition on the server in PostgreSQL.

Tablespaces may need to be moved to other locations for several reasons. The first reason is that you may run out of disk space, and it turns out that the current partition where you have the table space cannot be expanded using Linux tools and you will have to temporarily and permanently move the table space to another partition. The second reason is connected with the optimization of the business processes of the database itself. For example, you connected a fast SSD disk to the server, and you need to transfer the tablespace to this disk, which contains tables that need fast read and write access.

In today's example, we'll move tablespaces named inzhener_1 and inzhener_2 from /u01/pg_tablespace to /u02/pg_tablespace.

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

With this query, we will find out where our tablespaces are.

Article content:

1. Create directories.
2. Assign the owner of directories.
3. Stop the database.
4. Looking at links.
5. Move tablespaces.
6. Remove old links.
7. Create new links.
8. Launching the database.
9. Check.
10. Summary

1. Create directories.

The first thing to start with is to create the necessary directories for tablespaces using the Linux operating system.

$. mkdir -p /u02/pg_tablespace/inzhener_1

$. mkdir -p /u02/pg_tablespace/inzhener_2

2. We assign the owner of directories.

After we have created the directories, the next we need to make sure to assign the owner of these directories to the postgres user.

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

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

3. Stop the database.

Be sure to stop the database before migrating tablespaces.

$. pg_ctl stop

4. See links.

All references in the PostgreSQL database that are associated with tablespaces are in the $PGDATA/pg_tblspc directory, in my case the full path /app/postgresql/15/pg_dbcluster_1/data/pg_tblspc.

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

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

5. Moving tablespaces.

Now it's time to move our tablespaces to our new directories. In order to do this, we will 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. Removing old links.

Now we need to remove the old links.

$. rm 16405

$. rm 16406

7. Creating 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. Starting the database.

$. pg_ctl start

9. Check.

After we're done, connect to the database and run a query to view all tablespaces.

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

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

10. Results.

As a result of our colleague, today we successfully completed such an operation as moving tablespaces to another directory, and after we have done everything and checked, now you can delete the old directory with tablespaces.

Thank you all, I hope that my article helped you in some way.