Question 266675 in Ubuntu Question posted in
The official Ubuntu documentation can be found here.

Ubuntu – In PostgreSQL, how to move data already stored on SSD to HDD?

I’m storing time-series data of some products to my DB server.

  • Ubuntu 20.04 LTS
  • PostgreSQL 15.x
  • SSD: 5TB with RAID 5 (OS and Postgres installed)
  • HDD: 6TB with RAID 5 (my target of data migration)

As data comes in, it is stored in the SSD.
At this time, the amount of data that comes in is quite large and becomes quite burdensome to keep as time passes, or as there are more products.
Due to the nature of time-series data, old data(more than a month old by my standards) become less valuable and less sought after over time.

So I want to transfer old data to HDD as it is.
enter image description here
As I caputured, the actual time-series data is stored in sqlt_data_1_2023_XX table, and as you can see, the table is automatically created as the month changes.

Here’s what I want or wonder:
Can the table where the data is stored be moved from one disk to another while keeping the logical structure intact?
The reason why we want the logical structure(schema, as I caputred) to be the same is that even if the location where the data is stored changes, the 3rd-party application(web monitoring) must refer to the time series data.

Please comment if my explanation is ambiguous or if you need additional information for a solution.
Thank you in advance.

Since I am a beginner in PostgreSQL, I saw keywords such as ‘table partitioning’ and ‘tablespace’ when I searched in advance.
But before I study and understand this term, I want to make sure my requirements are implementable first.

Tags:

2

Answers


  1. 0

    Making a Duplicate Installation of Postgres

    Step 1. Install Postgresql on the Destination computer using the same installation/setup file.

    Step 2. Backup the Whole Database (not Table(s)) from the Source Disk using pg4Admin.

    Step 4. copy the backed up file to desitination Dist and Restore the backed up file using pg4Admin.

    Step 5. Install Navicat for Postgres for transfering tables in future as it is very eary to use in place of pg4Admin

    Hope this helps you.

    Login or Signup to reply.
  2. 0

    This is a use case for tablespaces. Create a tablespace on the spinning disk, and one partition/table gets too old, use

    ALTER TABLE ... SET TABLESPACE ...

    to move it to the slow tablespace. The only restriction is that the table is not accessible while it is being moved.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search