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:
- View the value of default_tablespace variable.
- Setting a new value for the variable default_tablespace.
- Restarting the database.
- Check.
- 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';
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.
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.
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';
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.
