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

VMWARE WORKSTATION 3,4,5,6,7,8,9,10,11,12,14,15...etc LICENSE KEYS COLLECTION

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

Oracle to Postgresql migration

PostgreSQL Enterprise Manager - PEM Monitoring Tools

7 Steps to configure BDR replication in postgresql