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 Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

Migrating From Oracle to PostgreSQL using ora2pg open source tools