cancel
Showing results for 
Search instead for 
Did you mean: 

Move data to a different tablespace in PRODUCTION environment

Highlighted
Adventurer

Move data to a different tablespace in PRODUCTION environment

Hi Team,

 

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;
spcname
------------
pg_default
pg_global
(2 rows)

edb=# show default_tablespace ;
default_tablespace
--------------------

(1 row)

 

What is the best possible method available in Postgres for this migration.

We should be losing any piece of data since its PROD.

 

Thanks,

Sandeep

9 REPLIES
Moderator

Re: Move data to a different tablespace in PRODUCTION environment

Hi Sandeep,

 

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).

 

HTH,

--Richard

Adventurer

Re: Move data to a different tablespace in PRODUCTION environment

Hi,

 

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.

 

Thanks,

Sandeep

Adventurer

Re: Move data to a different tablespace in PRODUCTION environment

Any updates from anyone?

Moderator

Re: Move data to a different tablespace in PRODUCTION environment

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.

Adventurer

Re: Move data to a different tablespace in PRODUCTION environment

It will be on the same mount point.

Any other way to do so.. Downtime is affordable right now

Moderator

Re: Move data to a different tablespace in PRODUCTION environment

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.

Adventurer

Re: Move data to a different tablespace in PRODUCTION environment

This is because right now the data is on default tablespace. And this needs to be moved on a different USER defined tablespace.
Moderator

Re: Move data to a different tablespace in PRODUCTION environment

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.

 

Thanks,

Abbas.

Level 3 Traveller

Re: Move data to a different tablespace in PRODUCTION environment

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