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.

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


Popular posts from this blog

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

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory

PostgreSQL Database startup / shutdown /restart

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?