PostgreSQL - ALTER TABLESPACE
In PostgreSQL, a tablespace is a storage location where database objects like tables and indexes are stored. Managing tablespaces effectively is crucial for optimizing performance and resource allocation in your database environment. PostgreSQL provides the ALTER TABLESPACE statement, allowing you to rename a tablespace, change its ownership, or adjust its settings.
Here, we’ll explore the syntax, usage, and examples of the ALTER TABLESPACE statement in PostgreSQL to help you better manage your database storage.
ALTER TABLESPACE Statement in PostgreSQL
The ALTER TABLESPACE statement is used to modify the properties of an existing tablespace in PostgreSQL. It can perform the following actions:
- Rename a tablespace.
- Change the owner of a tablespace.
- Adjust configuration parameters for a tablespace.
Syntax:
ALTER TABLESPACE tablespace_name action;
The 'action' can be one of the following:
- To rename the tablespace, the ALTER TABLESPACE RENAME TO statement can be used as shown below:
ALTER TABLESPACE tablespace_name RENAME TO new_name;
- To change the owner of the tablespace, one can use the ALTER TABLESPACE OWNER TO statement:
ALTER TABLESPACE tablespace_name OWNER TO new_owner;
- The following statement changes the parameters for a tablespace:
ALTER TABLESPACE tablespace_name SET parameter_name = value;
Note: Only superusers or tablespace owners can execute the ALTER TABLESPACE statement.
PostgreSQL ALTER TABLESPACE Statement Examples
Let us take a look at some of the examples of ALTER TABLESPACE Statement in PostgreSQL to better understand the concept.
Example 1: Renaming a Tablespace
The following statement renames the tablespace 'ts_primary' to 'ts_secondary':
ALTER TABLESPACE ts_primary RENAME TO ts_secondary;
Verify it using the below command:
\db+
Output:
Example 2: Changing the Owner of a Tablespace
The following statement changes the owner of the 'ts_secondary' from 'Postgres' to 'raju':
ALTER TABLESPACE ts_secondary OWNER TO raju;
Verify the above using the below command:
\db+
Output:
Important Points About PostgreSQL ALTER TABLESPACE
- Only superusers or the current owner of the tablespace can execute the
ALTER TABLESPACE
command.- The ability to rename or change the owner of a tablespace provides flexibility in managing database storage during migrations, reorganizations, or ownership transfers.
- Setting parameters for tablespaces allows you to control specific settings like maintenance thresholds or autovacuum behavior.