Moving all tables to a different tablespace in PostgreSQL

I first created a directory on my computer as

mkdir /Volumes/Vertex4/pg_data
sudo chown postgres:postgres /Volumes/Vertex4/pg_data
psql -d crsp -c "CREATE TABLESPACE ssd OWNER igow LOCATION '/Volumes/Vertex4/pg_data'";

If I wanted to move my database into this new tablespace:

psql -d postgres -c "ALTER DATABASE crsp SET TABLESPACE ssd";

But if I just want to move the stuff I use and manage:

SELECT ' ALTER TABLE '||schemaname||'.'||tablename||' SET TABLESPACE ssd;' 
FROM pg_tables 
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');

and saved it as move_to_ssd.sql (“ssd” is the name of a namespace I set up for storing my data).

I then ran the following on the command line:

psql -d crsp < move_to_ssd.sql | grep ALTER | psql -d crsp

This uses SQL to create new SQL, then filters the relevant lines (these have the word “ALTER” in them), then pass these to psql.

Done!

Advertisements
This entry was posted in PostgreSQL and tagged . Bookmark the permalink.

3 Responses to Moving all tables to a different tablespace in PostgreSQL

  1. That’s a great script. When moving tables, we might want to move the indexes as well:

    SELECT ' ALTER INDEX '||schemaname||'.'||indexname||' SET TABLESPACE ssd;'
    FROM pg_indexes
    WHERE schemaname NOT IN ('pg_catalog', 'information_schema');

  2. Pingback: Postgres | carlos flávio barreto ferreira de souza

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s