0

Is it possible to dis-connect and re-connect a POSTGRES tablespace and all the associated objects within that tablespace?

I have a Postgres database with two tablespaces, one on a high-speed SSD drive (I've named this FASTSPACE) , and the other on a slower, traditional magnetic HDD (named SLOWSPACE). The slower tablespace is reserved for large volumes of historic data which is rarely accessed.

Is it possible to temporarily disconnect SLOWSPACE, with the intention of reconnecting it at a later date? the DROP TABLESPACE documentation can only be used once all database objects within it have been dropped.

I'm aware that I can backup all the tables in SLOWSPACE, then delete them, and then DROP the tablespace, however this will take time (there are several Terabytes of data). If I then need the archived data again I'll have create a new version of the SLOWSPACE tablespace from blank, then re-create all the objects from the backups. Again, this will take time.

Is there any way of temporarily disconnecting SLOWSPACE from the database - whilst still leaving the rest of the database up and running?

Update - happy to accept Franks Heikens two letter answer - 'no'

ConanTheGerbil
  • 677
  • 8
  • 21
  • Short answer: No. What I do in situations like this, is an export to CSV and connect to these CSV files using a foreign data wrapper reading these CSV-files. Then it looks like a table, but it's just a file on some (slow) storage outside the database. – Frank Heikens Feb 06 '22 at 14:57
  • For the reasons why you can't do this see [Tablespaces](https://www.postgresql.org/docs/current/manage-ag-tablespaces.html) and look for the **Warning** block. – Adrian Klaver Feb 06 '22 at 16:32

0 Answers0