Did you ever need to move a PostgreSQL database from one tablespace to another? Well I had, last week. It is so much work to do it all by hand, especially when you have 350+ tables with an average of 2 indexes per table. This had to be automated.

For those of you who don’t know what tablespaces are, I took the definition from the PostgreSQL website :

Tablespaces in PostgreSQL allow database administrators to define locations in the file system where the files representing database objects can be stored.

And for those of you who want to know why tablespaces are useful, here are 2 use cases (again from the PostgreSQL website) :

By using tablespaces, an administrator can control the disk layout of a PostgreSQL installation. This is useful in at least two ways. First, if the partition or volume on which the cluster was initialized runs out of space and cannot be extended, a tablespace can be created on a different partition and used until the system can be reconfigured.

Second, tablespaces allow an administrator to use knowledge of the usage pattern of database objects to optimize performance. For example, an index which is very heavily used can be placed on a very fast, highly available disk, such as an expensive solid state device. At the same time a table storing archived data which is rarely used or not performance critical could be stored on a less expensive, slower disk system.

Lots of Manual Work

Moving a complete database to a different tablespace involves 3 steps :

1. You have to tell PostgreSQL to create new tables and indexes for the database in the new tablespace. This can be done with :

   1	ALTER DATABASE "[database_name]" SET default_tablespace = [new_tablespace];

2. Then you have to move each table in that database to the new tablespace with :

   1	ALTER TABLE "[table_name]" SET TABLESPACE "[new_tablespace]";

   This line of SQL code is needed for each table in the database.

3. And that’s not all, because the indexes also have to be moved. That’s done with :

   1	ALTER INDEX "[index_name]" SET TABLESPACE "[new_tablespace]";

   This line of SQL code is needed for each index in the database.

Automate

I’m quite lazy by nature, so I wanted to automate this job. The plan was to make a PHP script that generates the correct SQL code. First thing to find out is the information needed to build the SQL code.

Get Tables and Indexes from Database

To move all tables from a PostgreSQL database to a different tablespace, you need to know which tables are in the PostgreSQL database. This can be done with the following query :

1

SELECT * FROM "pg_tables";

And to move all indexes from a PostgreSQL database to a different tablespace, you need to know which indexes are in the PostgreSQL database. This can be done with the following query :

1

SELECT * FROM "pg_indexes";

Mix It with PHP

I used PHP because it’s the language I know best. But with the SQL code from above, you can easily build your own script in a different language.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

<?php
 
/**
 * Configuration
 */
$host = '[change-me]';            // The host on which the database resides.
$user = '[change-me]';            // The username to access the database.
$pass = '[change-me]';            // The password to access the database.
$db = '[change-me]';            // The database to move.
$tablespace = '[change-me]';    // The tablespace to move the database to.
 
/**
 * Application
 */
$dbh = new PDO("pgsql:host=$host;dbname=$db", $user, $pass);
 
// Create SQL code to put new tables and indexes in the new tablespace.
$output = "ALTER DATABASE $db SET default_tablespace = $tablespace;" . PHP_EOL;
$output .= PHP_EOL;
 
// Select all tables from the database.
$tableQuery = "SELECT * FROM pg_tables
                ORDER BY tablename;";
 
foreach ($dbh->query($tableQuery) as $table) {
    $schemaName = $table['schemaname'];
    $tableName = $table['tablename'];
 
    // Create SQL code to move the table to the new tablespace.
    $output .= "ALTER TABLE \"$schemaName\".\"$tableName\" SET TABLESPACE \"$tablespace\";" . PHP_EOL;
 
    // Select all indexes from the table.
    $indexQuery = "SELECT * FROM pg_indexes
                    WHERE schemaname = '$schemaName'
                    AND tablename = '$tableName'
                    ORDER BY indexname;";
 
    foreach ($dbh->query($indexQuery) as $index) {
        $indexName = $index['indexname'];
 
        // Create SQL code to move the index to the new tablespace.
        $output .= "ALTER INDEX \"$schemaName\".\"$indexName\" SET TABLESPACE \"$tablespace\";" . PHP_EOL;
    }
}
 
// Write the resulting SQL code to a file.
$filename = 'migrate_' . $host . '_' . $db . '_to_' . $tablespace . '.sql';
file_put_contents('output/' . $filename, $output);
 
?>