Glen Berseth

I am an assistant professor at the University de Montreal and Mila. My research explores how to use deep learning and reinforcement learning to develop generalist robots.

Postgresql Transfer Database to New Tablespace with Python


This is a quick script I wrote in python to generate the sql file needed to transfer one database to another tablespace.



 #!/usr/bin/python

# will generate the sql file needed to alter the tablespace for a database.

import psycopg2
import psycopg2.extras
import sys

#
# Configuration
#
host = '';            # The host on which the database resides.
user = '';            # The username to access the database.
password = '';            # The password to access the database.
db = '';            # The database to move.
tablespace = '';    # The tablespace to move the database to.
output = ""

#
# Application
#
conn = psycopg2.connect(database=db, user=user, password=password)
conn.set_isolation_level(0) 
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

# Create SQL code to put new tables and indexes in the new tablespace.
output = output + "ALTER DATABASE " + db + " SET default_tablespace = " + tablespace + ";\n"

# Select all tables from the database.
tableQuery = "SELECT * FROM pg_tables where tableowner='" + user + "' ORDER BY tablename"

cur.execute(tableQuery)

db_tables = cur.fetchall()
# print db_tables
for table in db_tables:
    schemaName = table['schemaname']
    tableName = table['tablename']

    # Create SQL code to move the table to the new tablespace.
    output = output + "ALTER TABLE "+ schemaName +"." + tableName + " SET TABLESPACE " + tablespace + ";\n";
    # print output

    # Select all indexes from the table.
    indexQuery = "SELECT * FROM pg_indexes WHERE schemaname = '" + schemaName + "' AND tablename = '" + tableName + "' ORDER BY indexname"
    print indexQuery
    cur.execute(indexQuery)
    print cur.statusmessage
    db_indexes = cur.fetchall()
    # print db_indexes
    for index in db_indexes:
        print dict(index)
        indexName = index['indexname']

        # Create SQL code to move the index to the new tablespace.
        output = output + "ALTER INDEX " + schemaName + "." + indexName + " SET TABLESPACE " + tablespace+ ";\n";
   


# Write the resulting SQL code to a file.
filename = 'migrate_' + host + '' + db + '_to' + tablespace + '.sql'
sqlfile = open('filename, 'w')
sqlfile.write(output)

 



Best of luck.


References:
  1. http://blog.lodeblomme.be/2008/03/15/move-a-postgresql-database-to-a-different-tablespace/
  2. http://www.postgresql.org/docs/9.1/static/sql-createtablespace.html