How to Change pg_default to Another Tablespace in PostgreSQL: Step by Step Guide

In PostgreSQL, a tablespace is a mechanism for managing the physical placement of data on disk. By default, database data is placed in the pg_default tablespace, but in some situations you may want to change it, for example to optimize performance or manage disk space.

In this article, we will see how to change the default tablespace from pg_default step by step.

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.

Contents of the article:

1. View the value of the default_tablespace variable.
2. Setting a new value for the default_tablespace variable.
3. Restarting the database.
4. Checking the result.

1. View the value of the default_tablespace variable.

Before we change anything, let's first find out what value the default_tablespace variable has, for this we will use an SQL query.

sql> SELECT name, setting FROM pg_settings WHERE name = 'default_tablespace';

As we can see, the default value is an empty string.

2. Setting a new value for the default_tablespace variable.

The default_tablespace variable is static, and therefore the values ​​need to be changed in the postgresql.conf configuration file. Open the postgresql.conf configuration file and find the default_tablespace variable.

Change the values ​​to your tablespace, in my case I will change to inzhener_1.

3. Restarting the database.

After you have set the variable values, you must restart the database for the variable values ​​to be applied.

~$ pg_ctl restart

4. Checking the result.

After restarting the database, we need to check whether our variable value has been applied. To do this, we will execute the same query as in the first paragraph.

Now if you create new objects in the database, they will be created not in the pg_default tablespace, but in the one you specified in the default_tablespace variable.