MIME-Version: 1.0
References: 
 <CAL5GnivMgBgRdY9YTLmAQKQa=TQVTRwghiGovK6Q6XxScdGOzg@mail.gmail.com>
In-Reply-To: 
 <CAL5GnivMgBgRdY9YTLmAQKQa=TQVTRwghiGovK6Q6XxScdGOzg@mail.gmail.com>
From: Joe Tailleur <joe.tailleur@gmail.com>
Date: Fri, 30 May 2025 07:39:53 -0600
Message-ID: 
 <CAPZdUVoT6VMQY4UeUhO_z4t8J6Re9qpUX3fuo+xH-Qc4xzbbkw@mail.gmail.com>
Subject: Re: Seeking Suggestions for Best Practices: Archiving and Migrating
 Historical Data in PostgreSQL
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000003f5ddc06365a8d22"
Archived-At: 
 <https://www.postgresql.org/message-id/CAPZdUVoT6VMQY4UeUhO_z4t8J6Re9qpUX3fuo%2BxH-Qc4xzbbkw%40mail.gmail.com>
Precedence: bulk

--0000000000003f5ddc06365a8d22
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=E2=80=AFAM Motog Plus <mplus7535@gmail.com> wr=
ote:

> Hi Team,
>
> We are currently planning a data archival initiative for our production
> PostgreSQL databases and would appreciate suggestions or insights from th=
e
> 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=E2=80=99d like to understand:
> - What approaches have worked well for you in practice?
> - Are there specific tools or strategies you=E2=80=99d recommend for ongo=
ing
> 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
>

--0000000000003f5ddc06365a8d22
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Using table partitioning works well for me.=C2=A0 I detach=
=C2=A0and 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.<div><br></div><div>Ano=
ther thing I have done is to set up foreign tables so I can move records ac=
ross databases.=C2=A0=C2=A0</div><div><br></div><div><br></div><div>Joe.</d=
iv><div><br></div></div><br><div class=3D"gmail_quote gmail_quote_container=
"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, May 30, 2025 at 1:51=E2=80=
=AFAM Motog Plus &lt;<a href=3D"mailto:mplus7535@gmail.com">mplus7535@gmail=
.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"mar=
gin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1=
ex"><div dir=3D"auto">Hi Team,<div dir=3D"auto"><br></div><div dir=3D"auto"=
>We are currently planning a data archival initiative for our production Po=
stgreSQL databases and would appreciate suggestions or insights from the co=
mmunity regarding best practices and proven approaches.</div><div dir=3D"au=
to"><br></div><div dir=3D"auto">**Scenario:**</div><div dir=3D"auto">- We h=
ave a few large tables (several hundred million rows) where we want to arch=
ive historical data (e.g., older than 1 year).</div><div dir=3D"auto">- The=
 archived data should be moved to a separate PostgreSQL database (on a same=
 or different server).</div><div dir=3D"auto">- Our goals are: efficient da=
ta movement, minimal downtime, and safe deletion from the source after succ=
essful archival.</div><div dir=3D"auto"><br></div><div dir=3D"auto">- Postg=
reSQL version: 15.12</div><div dir=3D"auto">- Both source and target databa=
ses are PostgreSQL.</div><div dir=3D"auto"><br></div><div dir=3D"auto">We e=
xplored using `COPY TO` and `COPY FROM` with CSV files, uploaded to a Share=
Point or similar storage system. However, our infrastructure team raised co=
ncerns around the computational load of large CSV processing and potential =
security implications with file transfers.</div><div dir=3D"auto"><br></div=
><div dir=3D"auto">We=E2=80=99d like to understand:</div><div dir=3D"auto">=
- What approaches have worked well for you in practice?</div><div dir=3D"au=
to">- Are there specific tools or strategies you=E2=80=99d recommend for on=
going archival?</div><div dir=3D"auto">- Any performance or consistency iss=
ues we should watch out for?</div><div dir=3D"auto"><br></div><div dir=3D"a=
uto">Your insights or any relevant documentation/pointers would be immensel=
y helpful.</div><div dir=3D"auto"><br></div><div dir=3D"auto">Thanks in adv=
ance for your guidance!</div><div dir=3D"auto"><br></div><div dir=3D"auto">=
Best regards,=C2=A0=C2=A0</div><div dir=3D"auto">Ramzy</div></div>
</blockquote></div>

--0000000000003f5ddc06365a8d22--