I just checked that our application data is lying on the default tablespace it seems and I need to migrate the data to a different user-defined tablespace:
edb=# select schemaname,tablename,tableowner,tablespace from pg_tables where schemaname like 'eoc' limit 20;
schemaname | tablename | tableowner | tablespace
eoc | cw_counter | eoc |
eoc | cw_counter_definition | eoc |
eoc | cwpworklistnotes | eoc |
eoc | cwpworklistnotesarchive | eoc |
eoc | cwcart | eoc |
eoc | cwglobalprocess | eoc |
eoc | cwpactivity0 | eoc |
edb=# SELECT spcname FROM pg_tablespace;
edb=# show default_tablespace ;
What is the best possible method available in Postgres for this migration.
We should be losing any piece of data since its PROD.
Depending on your uptime requirements, you could simply perform ALTER TABLE <table_name> SET TABLESPACE <new_tablespace_name>, but that would lock the table for as long as it takes to move the data from the old tablespace to the new tablespace.
If you are looking to minimize downtime and have no data loss, one option (aside from setting up a third-party replication tool and performing a switchover to the subscriber node after moving data to the new tablespace) is to seed a copy of the table in the new tablespace, then take a short downtime to synchronize the data between the new and old versions (using a script that you would have to write and test).
I want to move all the tables of that schema to a new tablespace.
How can that be possible.??
Also it's a PROD server hence can you also tell the restoration mechanism if the activity fails.
You can try this.
Instead of moving all tables to a new tablespace, you can move the entire cluster to the mount point where do you have enough space or where you would like to create the tablespace with minimal down time. Take a back up before doing any thing.
If the tables not so large, you can do as Richard mentioned above.
If it will be on the same mount point, why do you still need another tablespace ? Usually tablespace if the partition or volume on which the cluster was initialized runs out of space and cannot be extended, a tablespace can be created on a different partition and used until the system can be reconfigured.