Move Default Tablespace

Overview

The procedure for moving the default tablespace for a database is different from other tablespaces. The actual movement is done through the ALTER DATABASE rather than through the ALTER TABLE command.

Set Correct Port for PostgreSQL 16 Server

I ran the following command to ensure that the correct PostgreSQL server is selected:

export PGPORT=5432

All of the following commands are run as the postgres user.

Display Current Configuration

Note: I ran the following commands to display the current configuration for the books database and tablespace:

psql --command='\l+ books'
psql --command='\db+ *books*'

The results are:

                                                                     List of databases
 Name  |  Owner  | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules | Access privileges |  Size   | Tablespace | Description 
-------+---------+----------+-----------------+-------------+-------------+------------+-----------+-------------------+---------+------------+-------------
 books | douglas | UTF8     | libc            | en_AU.UTF-8 | en_AU.UTF-8 |            |           |                   | 8284 kB | books      | 
(1 row)

                                            List of tablespaces
 Name  | Owner |               Location               | Access privileges | Options |  Size   | Description 
-------+-------+--------------------------------------+-------------------+---------+---------+-------------
 books | books | /var/lib/postgresql/user_tablespaces |                   |         | 8296 kB | 
(1 row)

Important things to note are:

• The default tablespace for the books database is called books.
• There is 12 KB more in the books tablespace (8296 KB) than is reported for the books database (8284 KB). I have no explanation for this discrepency.
• The books tablespace is located in the /var/lib/postgresql/user_tablespaces directory.

Procedure

There are four (4) steps involved:

1. Rename Old Tablespace
2. Create New Tablespace
3. Change Default Tablespace
4. Drop Old Tablespace

Rename Old Tablespace

I ran the following commands to rename the books tablespace to old_books and display the resulting configuration:

psql --command='ALTER TABLESPACE books RENAME TO old_books'
psql --command='\l+ books'
psql --command='\db+ *books*'

The results are:

ALTER TABLESPACE
                                                                     List of databases
 Name  |  Owner  | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules | Access privileges |  Size   | Tablespace | Description 
-------+---------+----------+-----------------+-------------+-------------+------------+-----------+-------------------+---------+------------+-------------
 books | douglas | UTF8     | libc            | en_AU.UTF-8 | en_AU.UTF-8 |            |           |                   | 8284 kB | old_books  | 
(1 row)

                                              List of tablespaces
   Name    | Owner |               Location               | Access privileges | Options |  Size   | Description 
-----------+-------+--------------------------------------+-------------------+---------+---------+-------------
 old_books | books | /var/lib/postgresql/user_tablespaces |                   |         | 8296 kB | 
(1 row)

Create New Tablespace

I ran the following commands to create the directory for the new tablespace, books; create the new tablespace; and display the resulting configuration:

mkdir -p /var/lib/postgresql/user_ts/books
psql --command="CREATE TABLESPACE books OWNER books LOCATION '/var/lib/postgresql/user_ts/books'"

The results are:

CREATE TABLESPACE

Change Default Tablespace

I ran the following command to change the default tablespace for the books databases from old_books to books, and display the resulting configuration:

psql --command='ALTER DATABASE books SET TABLESPACE books'
psql --command='\db+ books'

The results are:

ALTER DATABASE
                                              List of tablespaces
   Name    | Owner |               Location               | Access privileges | Options |  Size   | Description 
-----------+-------+--------------------------------------+-------------------+---------+---------+-------------
 books     | books | /var/lib/postgresql/user_ts/books    |                   |         | 8296 kB | 
 old_books | books | /var/lib/postgresql/user_tablespaces |                   |         | 0 bytes | 
(2 rows)

Drop Old Tablespace

I ran the following commands to drop the old tablespace, old_books, and display the resulting configuration:

psql --command='DROP TABLESPACE old_books'
psql --command='\l+ books'
psql --command='\db+ *books*'

The results are:

DROP TABLESPACE
                                                                     List of databases
 Name  |  Owner  | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules | Access privileges |  Size   | Tablespace | Description 
-------+---------+----------+-----------------+-------------+-------------+------------+-----------+-------------------+---------+------------+-------------
 books | douglas | UTF8     | libc            | en_AU.UTF-8 | en_AU.UTF-8 |            |           |                   | 8284 kB | books      | 
(1 row)

                                           List of tablespaces
 Name  | Owner |             Location              | Access privileges | Options |  Size   | Description 
-------+-------+-----------------------------------+-------------------+---------+---------+-------------
 books | books | /var/lib/postgresql/user_ts/books |                   |         | 8296 kB | 
(1 row)

It is important to note that the resulting configuration matches the original configuration, except for the location which points to the new directory, /var/lib/postgresql/user_ts/books.