Moving Database objects or all tables to a different tablespace in PostgreSQL
Create a directory
I then ran the following on the command line:
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
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.sqlI 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
Comments
Post a Comment