Hello! In this article we will discuss about Postgresql alter tablespace.

In our previous article we talked about PostgreSQL Create Tablespace. After creating a tablespace in PostgreSQL you may need to alter it after a while.

What you can change via altering a tablespace:

• Rename a tablespace
• Change owner of a tablespace
• Set tablespace options
• Reset tablespace options

Let’s create a tablespace first to run examples:

Altering a Tablespace in PostgreSQL: Renaming

The most common alter operation for a tablespace is renaming it.

Renaming a tablespace has a very simple syntax:

ALTER TABLESPACE tablespace_name RENAME TO new_tablespace_name;

Let’s apply this command to our sample.

As you can see we updated name of a tablespace with a single line of SQL command. Let’s check details of this tablespace.

Altering a Tablespace Owner in PostgreSQL

You can change owner of a tablespace in PostgreSQL. Let’s start with listing owners of tablespaces:

SELECT pr.rolname, pt.spcname FROM pg_tablespace pt, pg_roles pr WHERE pt.spcowner=pr.oid;

You may want to save command above for later.

Let’s create a role to PostgreSQL alter tablespace.

postgres=# CREATE ROLE yigit;
CREATE ROLE

So we can move our tablespace ownership to a new user:

postgres=# ALTER TABLESPACE mynewts OWNER TO yigit;
ALTER TABLESPACE

Check our tablespaces owner’s again:

postgres=# SELECT pr.rolname, pt.spcname FROM pg_tablespace pt, pg_roles pr WHERE pt.spcowner=pr.oid;
 rolname  |  spcname
----------+------------
 postgres | pg_default
 postgres | pg_global
 yigit    | mynewts
(3 rows)

We changed owner of a tablespace to PostgreSQL alter tablespace.

Altering a Tablespace Setting an Option

In this article we will update seq_page_cost of a tablespace.

You can update an option using SET keyword and between paranthesis you can mention the options and values.

postgres=# ALTER TABLESPACE mynewts SET (seq_page_cost=20.0);
ALTER TABLESPACE

See how PostgreSQL stores this tablespace option:

postgres=# SELECT * FROM pg_tablespace;
  oid  |  spcname   | spcowner | spcacl |      spcoptions
-------+------------+----------+--------+----------------------
  1663 | pg_default |       10 |        |
  1664 | pg_global  |       10 |        |
 16440 | mynewts    |    16441 |        | {seq_page_cost=20.0}
(3 rows)

In pg_tablespace catalog view, spcoptions column stores the data of the parameter.

Resetting Tablespace Options

We will reset seq_page_cost option of a tablespace in this chapter.

Like SET, we mention name of the option after RESET.

postgres=# ALTER TABLESPACE mynewts RESET (seq_page_cost);
ALTER TABLESPACE

Check the catalog view for new value of this parameter:

postgres=# SELECT * FROM pg_tablespace;
  oid  |  spcname   | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        |
  1664 | pg_global  |       10 |        |
 16440 | mynewts    |    16441 |        |
(3 rows)

PostgreSQL removes the tablespace option after you alter the tablespace.

Summary

In this article, we explored the process of altering tablespaces in PostgreSQL. We discussed how to rename a tablespace, change its owner, set tablespace options, and reset tablespace options. We also provided examples and SQL commands for each operation. You can also refer to official documentation for Postgres Alter tablespace statements.

If you found this article helpful or have any questions, feel free to leave a comment below. Don’t forget to subscribe for more PostgreSQL tips and tutorials!