public inbox for [email protected]  

help / color / mirror / Atom feed

From: Holger Jakobs <[email protected]>
To: [email protected]
Subject: Re: Seeking Suggestions for Best Practices: Archiving and Migrating Historical Data in PostgreSQL
Date: Fri, 30 May 2025 12:44:04 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAEZv3cobvxRphFDpyRtwtudc0gWnrxv4Qg85iotQV7Gn08dU9Q@mail.gmail.com>
References: <CAL5GnivMgBgRdY9YTLmAQKQa=TQVTRwghiGovK6Q6XxScdGOzg@mail.gmail.com>
	<CAEZv3cobvxRphFDpyRtwtudc0gWnrxv4Qg85iotQV7Gn08dU9Q@mail.gmail.com>

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.


Am 30. Mai 2025 12:29:54 MESZ schrieb Andy Hartman <[email protected]>:
>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 protected]> wrote:
>
>> 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
>>

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

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: <[email protected]>

* 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