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.
It will be on the same mount point.
Any other way to do so.. Downtime is affordable right now
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.
It doesn't make any difference if the data is on default tablespace or USER defined tablespace as they are both reside on the same partition of the disk. Hence you can keep it as it is. However you can create a new tablespace to store the new table's data, to avoid filling of default tablespace.
If you still insist to move which costs downtime, you have to use some third party tools as Richard said.
A database's data is collectively stored in the datafiles that constitute each tablespace of the database. help for example, the simplest Oracle and sap database would have one tablespace and one datafile. Another database can have three tablespaces, each consisting of two datafiles in better ways