Move a PostgreSQL database to a different tablespace

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);
 
?>

10 thoughts on “Move a PostgreSQL database to a different tablespace”

  1. Ingenious! Reminds me of those tricks with a “DESCRIBE tablename” or “SHOW COLUMNS FROM tablename” queries I’m running to automate some stuff in my DB layer.

    I feel a blogpost coming up :)

  2. Thx Chris, hadn’t seen that yet.

    I’d love to see that implemented into PostgreSQL. It’s behaviour you would expect it to do.

  3. You can avoid writing a php script by keeping it all in SQL.

    psql-> select ‘ALTER INDEX ‘||indexname||’ SET TABLESPACE aim_index_data ;’ from pg_catalog.pg_indexes where schemaname = ‘public’ order by tablename;

    Will generate the sql needed to update the logs.

    So the whole thing is:

    psql->\o index_tablespace_update.sql

    psql-> select ‘ALTER INDEX ‘||indexname||’ SET TABLESPACE aim_index_data ;’ from pg_catalog.pg_indexes where schemaname = ‘public’ order by tablename;

    then from the command line psql -d yourdb < index_tablespace_update.sql

  4. ERROR: permission denied: “pg_aggregate” is a system catalog
    what’s the problem, help me please.

  5. Executing statements immediately appears to work with a PostgreSQL 8.4 database using the above code as a template for the Python script below,

    #! /usr/bin/python3

    import postgresql

    dbName = "DB"
    tablespaceName = "SPACE"

    db = postgresql.open("pq://USER@HOST/SERVICE")

    with db:
    db.settings[ "client_min_messages" ] = "NOTICE"
    db.execute( "alter database %s set default_tablespace = %s" % ( dbName, tablespaceName ) )
    for table in db.prepare( "select * from pg_tables order by tablename" ):
    schemaName = table[ "schemaname" ]
    tableName = table[ "tablename" ]
    db.execute( "alter table %s.%s set tablespace %s" % ( schemaName, tableName, tablespaceName ) )
    for index in db.prepare( "select * from pg_indexes where schemaname = '%s' and tablename = '%s' order by indexname"
    % ( schemaName, tableName ) ):
    db.execute( "alter index %s.%s set tablespace %s" % ( schemaName, index[ "indexname" ], tablespaceName ) )

  6. Err, the above failed to move pg_catalog.* tables so I added a filter before db.execute( “alter table ..” ),


    if schemaName == "public":

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>