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

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.

On Fri, May 30, 2025 at 1:51 AM Motog Plus <mplus7535(at)gmail(dot)com> 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
>