Moving Database objects or all tables to a different tablespace in PostgreSQL

Create a directory

mkdir /home/Postdata/pg_data
sudo chown postgres:postgres /home/Postdata/pg_data
psql -d crsp -c "CREATE TABLESPACE tab1 OWNER u1 LOCATION '/home/Postdata/pg_data'";

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

psql -d postgres -c "ALTER DATABASE db1 SET TABLESPACE tab1";

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

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

and saved it as tab1.sql

I then ran the following on the command line:

psql -d db1 < tab1.sql | grep ALTER | psql -d db1

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

Note: Downtime is required for alter table moving. For “online” moving one can though use tools like pg_repack or pg_squeeze,Affected tables are locked while being relocated