MIME-Version: 1.0
References: 
 <CAL5GnivMgBgRdY9YTLmAQKQa=TQVTRwghiGovK6Q6XxScdGOzg@mail.gmail.com>
In-Reply-To: 
 <CAL5GnivMgBgRdY9YTLmAQKQa=TQVTRwghiGovK6Q6XxScdGOzg@mail.gmail.com>
From: Andy Hartman <hartman60home@gmail.com>
Date: Fri, 30 May 2025 06:29:54 -0400
Message-ID: 
 <CAEZv3cobvxRphFDpyRtwtudc0gWnrxv4Qg85iotQV7Gn08dU9Q@mail.gmail.com>
Subject: Re: Seeking Suggestions for Best Practices: Archiving and Migrating
 Historical Data in PostgreSQL
To: Motog Plus <mplus7535@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000c6dbe9063657e5f1"
Archived-At: 
 <https://www.postgresql.org/message-id/CAEZv3cobvxRphFDpyRtwtudc0gWnrxv4Qg85iotQV7Gn08dU9Q%40mail.gmail.com>
Precedence: bulk

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

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=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
>

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

<div dir=3D"ltr">I have the same situation=C2=A0and am very curious about a=
 long term solution people are using.</div><br><div class=3D"gmail_quote gm=
ail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, May 30, =
2025 at 3:51=E2=80=AFAM Motog Plus &lt;<a href=3D"mailto:mplus7535@gmail.co=
m">mplus7535@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_q=
uote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,2=
04);padding-left:1ex"><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 PostgreSQL databases and would appreciate suggestions or in=
sights from the community regarding best practices and proven approaches.</=
div><div dir=3D"auto"><br></div><div dir=3D"auto">**Scenario:**</div><div d=
ir=3D"auto">- We have a few large tables (several hundred million rows) whe=
re we want to archive historical data (e.g., older than 1 year).</div><div =
dir=3D"auto">- The archived data should be moved to a separate PostgreSQL d=
atabase (on a same or different server).</div><div dir=3D"auto">- Our goals=
 are: efficient data movement, minimal downtime, and safe deletion from the=
 source after successful archival.</div><div dir=3D"auto"><br></div><div di=
r=3D"auto">- PostgreSQL version: 15.12</div><div dir=3D"auto">- Both source=
 and target databases are PostgreSQL.</div><div dir=3D"auto"><br></div><div=
 dir=3D"auto">We explored using `COPY TO` and `COPY FROM` with CSV files, u=
ploaded to a SharePoint or similar storage system. However, our infrastruct=
ure team raised concerns around the computational load of large CSV process=
ing 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"auto">- Are there specific tools or strategies you=E2=80=
=99d recommend for ongoing archival?</div><div dir=3D"auto">- Any performan=
ce or consistency issues we should watch out for?</div><div dir=3D"auto"><b=
r></div><div dir=3D"auto">Your insights or any relevant documentation/point=
ers would be immensely helpful.</div><div dir=3D"auto"><br></div><div dir=
=3D"auto">Thanks in advance 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">Ram=
zy</div></div>
</blockquote></div>

--000000000000c6dbe9063657e5f1--