PostgreSQL - How to change pg_default default tablespace

PostgreSQL - How to change pg_default default tablespace

Colleagues hello to all.

In today's article, we'll talk about how to change the default tablespace from pg_default to your own in PostgreSQL.

At the moment when you create a table, PostgreSQL itself implicitly substitutes an additional  TABLESPACE pg_default parameter, but if you want tables to be created in a different tablespace, you can do this by changing the  default_tablespace configuration parameter.

The default_tablespace variable sets the tablespace to be used by default, in which the database objects (tables and indexes) will be created. By default, the variable has an empty string value, which means that all database objects will be created in the  pg_default tablespace. Even if you specify some value in the variable and it turns out that such a tablespace does not exist, then there will be no error, and all database objects will be created in the pg_default tablespace.

Another very important nuance is that this variable is not used for temporary tables.

 

Article content:

  1. View the value of default_tablespace variable.
  2. Setting a new value for the variable default_tablespace.
  3. Restarting the database.
  4. Check.
  5. Summary

 

1. Viewing the value of the default_tablespace variable.

Before we change anything, let's first find out what values ​​the default_tablespace variable has, for this we will use a sql query.

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

postgresql change default tablespace

As you can see, the default values ​​are 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 config file postgresql.conf and find the variable default_tablespace.

postgresql change default tablespace

After that, we found the variable in this way, then we change the values ​​​​to our tablespace, in my case I will change to inzhener_1.

postgresql change default tablespace


 

3. Restart database.

After setting the variable values, you must restart the database in order for the variable values ​​to take effect.

$. pg_ctl restart


 

4. Check.

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

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

postgresql change default tablespace


 

5. Results.

After all that we have done, now if you create new objects in the database, they will not be created in the pg_default tablespace, but in the one you specified in the default_tablespace variable.


 

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

 

SIMILAR ARTICLES

PostgreSQL ERROR: requested wal segment has already been removed
PostgreSQL ERROR: requested wal segment has already been removed
POSTGRESQL
PostgreSQL: How to enable data checksums - pg_checksums
PostgreSQL: How to enable data checksums - pg_checksums
POSTGRESQL
Postgresql - User List
Postgresql - User List
POSTGRESQL