Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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

Comments

Popular posts from this blog

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

PostgreSQL pgBadger

How to Return a Result Set from a PostgreSQL Stored Procedure

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

What are the Easy Tips To Increase AdSense CPC