public inbox for [email protected]  

help / color / mirror / Atom feed

From: Motog Plus <[email protected]>
To: Pgsql-admin <[email protected]>
Subject: Seeking Suggestions for Best Practices: Archiving and Migrating Historical Data in PostgreSQL
Date: Fri, 30 May 2025 13:21:25 +0530
Message-ID: <CAL5GnivMgBgRdY9YTLmAQKQa=TQVTRwghiGovK6Q6XxScdGOzg@mail.gmail.com> (raw)

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Seeking Suggestions for Best Practices: Archiving and Migrating Historical Data in PostgreSQL
  In-Reply-To: <CAL5GnivMgBgRdY9YTLmAQKQa=TQVTRwghiGovK6Q6XxScdGOzg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox