Overview
Relations in the default tablespace for a database do not have the tablespace name displayed in the pg_tables view. This is different from normal tablespaces. Changing the default tablespace for a database also moves the associated relations. However dropping the original default tablespace and renaming the new default one to the original name undoes all of the movement and restores the original directory. The solution is to rename the tablespace then change the default tablespace.
Status
RESOLVED
Workaround
None found
Resolution
Created procedure in Move Default Tablespace.
Diagnosis
Summary
The following are a detailed diary as I investigated how to move the tablespace books to another directory.
Find Number of Tables in BOOKS Tablespace
To see how many tables there are in that tablespace, I ran the following command:
psql --command="SELECT count(*) FROM pg_tables WHERE tablespace = 'books'"
The results were surprising:
count
-------
0
(1 row)
First Attempt to Drop Tablespace
Based on the PostgreSQL 16 server reporting that there are no tables in the tablespace books, I decided to drop that tablespace as follows:
psql --command="DROP TABLESPACE books"
But I got the following error message:
ERROR: tablespace "books" is not empty
I reran the command with an additional parameter to get more details as follows:
psql --set=VERBOSITY=verbose --command="DROP TABLESPACE books"
But the results were not as I expected:
ERROR: 55000: tablespace "books" is not empty LOCATION: DropTableSpace, tablespace.c:528
Create NEW_BOOKS Tablespace
Given that I do not know what tables are in the books tablespace, I will proceed to move the tables to a new tablespace called new_books which will be renamed to books once the old tablespace has been dropped. I used the following commands to create the new tablespace:
mkdir -p /var/lib/postgresql/user_ts/books psql --command="CREATE TABLESPACE new_books OWNER books LOCATION '/var/lib/postgresql/user_ts/books'"
Note: In PostgreSQL, a tablespace is mapped to a directory. Tablespaces cannot share the same directory. In the above command, the sub-directory name, books, refers to the eventual tablespace name, not the curent one.
The response is:
CREATE TABLESPACE
The contents of the tablespace directory immediately after creation are:
/var/lib/postgresql/user_ts/books: total 0
I used the following command to list all tablespaces that I am interested in:
psql --command="\db+ *books*"
There are two (2) tablespaces as expected:
List of tablespaces Name | Owner | Location | Access privileges | Options | Size | Description -----------+---------+-----------------------------------+-------------------+---------+---------+------------- books | douglas | /var/lib/postgresql/14/main | | | 9181 kB | new_books | books | /var/lib/postgresql/user_ts/books | | | 0 bytes | (2 rows)
The books tablespace continues to grow!
Move All Tables in BOOKS Tablespace to NEW_BOOKS
psql --command="ALTER TABLE ALL IN TABLESPACE books SET TABLESPACE new_books"
This failed with the following message for the default database, postgres:
NOTICE: no matching relations in tablespace "books" found ALTER TABLE
However, if I specify the correct database, books, as follows:
psql --dbname books --command="ALTER TABLE ALL IN TABLESPACE books SET TABLESPACE new_books"
the command succeeds as shown below:
ALTER TABLE
When I check the number of tables that exist in the new_books tabkespace with the following command:
psql --dbname books --command="SELECT count(*) FROM pg_tables WHERE tablespace = 'new_books'"
I find that there are eleven (11) tables in the new_books tablespace.
Second Attempt to Drop Tablespace
Now that I have moved all tables out of the tablespace books, I tried to drop that tablespace as follows:
psql --command="DROP TABLESPACE books"
But I got the following error message:
ERROR: tablespace "books" is not empty
I tried to move any remaining tables out of the books tablespace for the books database:
psql --dbname=books --command="ALTER TABLE ALL IN TABLESPACE books SET TABLESPACE new_books"
But the command fails with the following message:
NOTICE: no matching relations in tablespace "books" found ALTER TABLE
Examine BOOKS Database
I looked at the definition of the books database using the following command:
psql --command="\l+ books"
The result is:
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 | | | | 9169 kB | books | (1 row)
It would appear that the default tablespace for the books database is books.
First Attempt to Change Default Tablespace for BOOKS Database
I used the following command to change the default tablespace for the books database to new_books:
psql --command="ALTER DATABASE books SET TABLESPACE new_books"
The result is:
ERROR: some relations of database "books" are already in tablespace "new_books" HINT: You must move them back to the database's default tablespace before using this command.
Move Tables Back to BOOKS Tablespace
I used the following command to move all tables from the new_books tablespace to the books one for the books database:
psql --dbname=books --command="ALTER TABLE ALL IN TABLESPACE new_books SET TABLESPACE books"
The result is:
ALTER TABLE
Second Attempt to Change Default Tablespace for BOOKS Database
I used the following command to change the default tablespace for the books database to new_books:
psql --command="ALTER DATABASE books SET TABLESPACE new_books"
The result is:
ALTER DATABASE
Now when I check for tables in the books and new_books tablespaces using the following command:
psql --command="SELECT tablespace,count(*) FROM pg_tables WHERE tablespace IN ('books','new_books') GROUP BY tablespace"
The result is:
tablespace | count ------------+------- (0 rows)
Third Attempt to Move Tables to NEW_BOOKS Tablespace
Now that I have successfully changed the default tablespace for the books database to new_books, I moved all tables in the books tablespace to new_books using the following command:
psql --dbname=books --command="ALTER TABLE ALL IN TABLESPACE books SET TABLESPACE new_books"
The result is:
NOTICE: no matching relations in tablespace "books" found ALTER TABLE
I checked the default tablespace for the books database using the following command:
psql --command='\l+ books'
The expected value of new_books is confirmed by:
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 | | | | 9185 kB | new_books |
(1 row)
I checked the sizes of the books and new_books tablespaces using the following command:
psql --command='\db+ *books*'
The result is:
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
-----------+---------+-----------------------------------+-------------------+---------+---------+-------------
books | douglas | /var/lib/postgresql/14/main | | | 0 bytes |
new_books | books | /var/lib/postgresql/user_ts/books | | | 9197 kB |
(2 rows)
It would appear that the change to the default tablespace for the database moved all of the tables to the new tablespace.
Second Attempt to Drop BOOKS Tablespace
I used the following command to drop the books tablespace using the following command:
psql --command='DROP TABLESPACE books'
The result is:
DROP TABLESPACE
Rename NEW_BOOKS Tablespace
I used the following command to rename the new_books tablespace to books:
psql --command='ALTER TABLESPACE new_books RENAME TO books'
The result is:
ALTER TABLESPACE
The default tablespace for the books database is renamed as well:
psql --command='\l+ books'
The result is:
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 | | | | 9185 kB | books | (1 row)
BOOKS Tablespace is Restored in Original Directory
When I check the books tablespace, I find it is back to its original directory:
psql --command='\db+ *books*'
The following output shows the original directory:
List of tablespaces Name | Owner | Location | Access privileges | Options | Size | Description -------+-------+--------------------------------------+-------------------+---------+---------+------------- books | books | /var/lib/postgresql/user_tablespaces | | | 8296 kB | (1 row)