Subject: Seeking Suggestions for Best Practices: Archiving and Migrating Historical Data in PostgreSQL

From: Motog Plus <[email-masked]>
Date: Fri, 30 May 2025 13:21:25 +0530

Hi Team,

We are currently planning a data archival initiative for our production
PostgreSQL databases and would appreciate suggestions or insights from the
community regarding best practices and proven approaches.

**Scenario:**
- We have a few large tables (several hundred million rows) where we want
to archive historical data (e.g., older than 1 year).
- The archived data should be moved to a separate PostgreSQL database (on a
same or different server).
- Our goals are: efficient data movement, minimal downtime, and safe
deletion from the source after successful archival.

- PostgreSQL version: 15.12
- Both source and target databases are PostgreSQL.

We explored using `COPY TO` and `COPY FROM` with CSV files, uploaded to a
SharePoint or similar storage system. However, our infrastructure team
raised concerns around the computational load of large CSV processing and
potential security implications with file transfers.

We’d like to understand:
- What approaches have worked well for you in practice?
- Are there specific tools or strategies you’d recommend for ongoing
archival?
- Any performance or consistency issues we should watch out for?

Your insights or any relevant documentation/pointers would be immensely
helpful.

Thanks in advance for your guidance!

Best regards,
Ramzy

---

From: Andy Hartman <[email-masked]>
Date: Fri, 30 May 2025 06:29:54 -0400

I have the same situation and am very curious about a long term solution
people are using.

On Fri, May 30, 2025 at 3:51 AM Motog Plus <[email-masked]> wrote:

> - The archived data should be moved to a separate PostgreSQL database (on
> a same or different server).

---

From: Holger Jakobs <[email-masked]>
Date: Fri, 30 May 2025 12:44:04 +0200

Copying directly using pg_dump and psql seems to be feasible. The connection to the database is usually encrypted, so no security concerns. 

And the copying of the data uses the fastest method. Plus, no intermediate files are necessary.

Deleting old records has to be done separately.

-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49 178 9759012

---

From: Ilya Kosmodemiansky <[email-masked]>
Date: Fri, 30 May 2025 12:56:36 +0200

Hi Ramzy,

> - What approaches have worked well for you in practice?

It really depends on your requirements. What volumes of data you store
and intend to archive? Are your tables partitioned, or is partitioning
at least an option for you? Would it be a rear operation (really once
a year) or you need to do it regularly

Best regards,
Ilya Kosmodemiansky
CEO, Founder

Data Egret GmbH
Your remote PostgreSQL DBA team
T.: +49 6821 919 3297
[email-masked]

---

From: Joe Tailleur <[email-masked]>
Date: Fri, 30 May 2025 07:39:53 -0600

Using table partitioning works well for me.  I detach and move the
partition to an archive schema; which I can then backup and restore into a
separate database, and once that is complete, remove the table from the
archive schema on the live database.

Another thing I have done is to set up foreign tables so I can move records
across databases.

Joe.

---

From: Scott Ribe <[email-masked]>
Date: Fri, 30 May 2025 07:45:41 -0600

> On May 30, 2025, at 7:39 AM, Joe Tailleur <[email-masked]> wrote:
> 
> Using table partitioning works well for me.  I detach and move the partition to an archive schema; which I can then backup and restore into a separate database, and once that is complete, remove the table from the archive schema on the live database.

What's the purpose of the archive schema?

---

From: Joe Tailleur <[email-masked]>
Date: Fri, 30 May 2025 07:50:47 -0600

It allows me to easily backup the schema (regardless of the number of
tables).  I can move several tables into that schema, and back up the
schema.

Really just to help me organize my own processes.

Joe.

---

From: Scott Ribe <[email-masked]>
Date: Fri, 30 May 2025 07:56:31 -0600

> On May 30, 2025, at 7:50 AM, Joe Tailleur <[email-masked]> wrote:
> 
> It allows me to easily backup the schema (regardless of the number of tables).  I can move several tables into that schema, and back up the schema.  

Ah--my mind was stuck on one-at-a-time thinking.

---

From: Ron Johnson <[email-masked]>
Date: Fri, 30 May 2025 11:29:34 -0400

> - What approaches have worked well for you in practice?

This is how I migrated 6TB of data from an Oracle database to Postgresql,
and then implemented quarterly archiving of the PG database:
- COPY FROM (SELECT * FROM live_table WHERE date_fld in
some_manageable_date_range) TO STDOUT.
- Compress
- scp
- COPY TO archive_table.
- Index
- DELETE FROM live_table WHERE date_fld in some_manageable_date_range
(This I only did in the PG archive process

(Naturally, the Oracle migration used Oracle-specific commands.)

> archival?

I write generic bash loops to which you pass an array that contains the
table name, PK, date column and date range.

Given a list of tables, it did the COPY FROM, lz4 and scp.  Once that
finished successfully, another script dropped archive indices on the
current table, COPY TO and CREATE INDEX statements.  A third script did the
deletes.

This works even when the live database tables are all connected via FK.
You just need to carefully order the tables in your script.

> - Any performance or consistency issues we should watch out for?

My rules for scripting are "bite-sized pieces" and "check those return
codes!".

> Your insights or any relevant documentation/pointers would be immensely

Index support uber alles.  When deleting from a table which relies on a
foreign key link to a table which _does_ have a date field, don't hesitate
to join on that table.

And DELETE of bite-sized chunks is faster than people give it credit for.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Andy Hartman <[email-masked]>
Date: Fri, 30 May 2025 12:14:52 -0400

what was the duration start to finish of the migration of the 6tb of data.
then what do you use for a quick backup after archived PG data

Thanks.

---

From: Ron Johnson <[email-masked]>
Date: Fri, 30 May 2025 14:10:25 -0400

Hmm... that was a few years ago, back when v12 was new.  It took about  a
month (mainly because they didn't want me running exports during "office
hours").

There were 120 INSERT & SELECT (no UPDATE or DELETE) tables, so I was able
to add indices on date columns, create by-month views.  (We migrated the
dozen or so *relatively* small UPDATE tables on cut-over day.  On that same
day, I migrated the current month and the previous month's data in those
120 tables.

I made separate cron jobs to:
- export views from Oracle into COPY-style tab-separated flat files,
- lz4-compress views that had finished exporting, and
- scp files that were finished compressing, to an AWS EC2 VM.

These jobs pipelined, so there was always a job exporting, always a job
ready to compress tsv files, and another job ready to scp the lz4 files.
When there was nothing for a step to do, the job would sleep for a couple
of minutes, then check if there was more work to do.

On the AWS EC2 VM, a different cron job waited for files to finish
transferring, then loaded them into the correct table. Just like with the
source host jobs, the "load" job would sleep a bit and then check for more
work. I manually applied Indices.

The AWS RDS PG12 database was about 4TB.  Snapshots were handled by AWS.
If this had been one of my on-prem systems, I'd have used pgbackrest.
(pgbackrest is impressively fast: takes good advantage of PG's 1GB file
max, and globs "small" files into one big file.)

On Fri, May 30, 2025 at 12:15 PM Andy Hartman <[email-masked]>
wrote:

>>> - We have a few large tables (several hundred million rows) where we
>>> want to archive historical data (e.g., older than 1 year).
>>> - The archived data should be moved to a separate PostgreSQL database
>>> (on a same or different server).
>>> We explored using `COPY TO` and `COPY FROM` with CSV files, uploaded to
>>> a SharePoint or similar storage system. However, our infrastructure team

---

From: Andy Hartman <[email-masked]>
Date: Fri, 30 May 2025 14:39:35 -0400

What would you use for backup if PG hosted on Windows

On Fri, May 30, 2025 at 2:10 PM Ron Johnson <[email-masked]> wrote:

> dozen or so *relatively* small UPDATE tables on cut-over day.  On that
> same day, I migrated the current month and the previous month's data in
> those 120 tables.
>
>> what was the duration start to finish of the migration of the 6tb of
>> data. then what do you use for a quick backup after archived PG data
>>
>> Thanks.
>>
>>> This is how I migrated 6TB of data from an Oracle database to
>>> Postgresql, and then implemented quarterly archiving of the PG database:

---

From: Ron Johnson <[email-masked]>
Date: Fri, 30 May 2025 15:31:01 -0400

That's an unanswerable question, as I would not use Windows.  😁

Seriously though, since it's an image-heavy database full of PDF and TIFF
files, I'd do what I did on Linux when needing to migrate/upgrade a 6TB
(including indices) db from PG 9.6 to PG 14, and took four hours:
pg_dump -Z1 --jobs=16

On Fri, May 30, 2025 at 2:39 PM Andy Hartman <[email-masked]>
wrote:

>> There were 120 INSERT & SELECT (no UPDATE or DELETE) tables, so I was
>> able to add indices on date columns, create by-month views.  (We migrated
>> the dozen or so *relatively* small UPDATE tables on cut-over day.  On
>> that same day, I migrated the current month and the previous month's data
>> in those 120 tables.
>>
>>>>> We are currently planning a data archival initiative for our
>>>>> production PostgreSQL databases and would appreciate suggestions or
>>>>> insights from the community regarding best practices and proven approaches.
>>>>>
>>>>> **Scenario:**
>>>>> We explored using `COPY TO` and `COPY FROM` with CSV files, uploaded
>>>>> to a SharePoint or similar storage system. However, our infrastructure team
>>>> This works even when the live database tables are all connected via
>>>> FK.  You just need to carefully order the tables in your script.
>>>>
>>>>
>>>>> Your insights or any relevant documentation/pointers would be
>>>>> immensely helpful.
>>>>>
>>>>

---

From: Motog Plus <[email-masked]>
Date: Mon, 2 Jun 2025 18:25:32 +0530

Hi Everyone,

Thank you all for the helpful suggestions, insights, and follow-up
questions. I truly appreciate the time and effort you’ve taken to share
your experiences and recommendations.

To answer one of the common questions: **yes, we are using partitioned
tables**, primarily based on a timestamp column. This setup is already
helping us manage and isolate historical data more effectively.

The input from this community has been incredibly valuable in helping us
shape our archival approach. We’re currently evaluating a few options based
on your feedback and will proceed with a solution that best balances
efficiency, reliability, and security.

We may reach out again with more specific questions or for further
suggestions once we finalize the approach and start implementation.

Thanks again for your support!

Best regards,
Ramzy

On Sat, May 31, 2025, 01:01 Ron Johnson <[email-masked]> wrote:

>>> Hmm... that was a few years ago, back when v12 was new.  It took about
>>> a month (mainly because they didn't want me running exports during "office
>>> hours").
>>>
>>> The AWS RDS PG12 database was about 4TB.  Snapshots were handled by
>>> AWS.  If this had been one of my on-prem systems, I'd have used
>>> pgbackrest.  (pgbackrest is impressively fast: takes good advantage of PG's
>>> 1GB file max, and globs "small" files into one big file.)
>>>
>>>>> I write generic bash loops to which you pass an array that contains
>>>>> the table name, PK, date column and date range.
>>>>>
>>>>> Index support uber alles.  When deleting from a table which relies on
>>>>> a foreign key link to a table which _does_ have a date field, don't
>>>>> hesitate to join on that table.
>>>>>