MIME-Version: 1.0
From: Motog Plus <mplus7535@gmail.com>
Date: Fri, 30 May 2025 13:21:25 +0530
Message-ID: 
 <CAL5GnivMgBgRdY9YTLmAQKQa=TQVTRwghiGovK6Q6XxScdGOzg@mail.gmail.com>
Subject: 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="00000000000006a744063655af4e"
Archived-At: 
 <https://www.postgresql.org/message-id/CAL5GnivMgBgRdY9YTLmAQKQa%3DTQVTRwghiGovK6Q6XxScdGOzg%40mail.gmail.com>
Precedence: bulk

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

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

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

<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 Postgr=
eSQL databases and would appreciate suggestions or insights from the commun=
ity regarding best practices and proven approaches.</div><div dir=3D"auto">=
<br></div><div dir=3D"auto">**Scenario:**</div><div dir=3D"auto">- We have =
a few large tables (several hundred million rows) where we want to archive =
historical data (e.g., older than 1 year).</div><div dir=3D"auto">- The arc=
hived data should be moved to a separate PostgreSQL database (on a same or =
different server).</div><div dir=3D"auto">- Our goals are: efficient data m=
ovement, minimal downtime, and safe deletion from the source after successf=
ul archival.</div><div dir=3D"auto"><br></div><div dir=3D"auto">- PostgreSQ=
L 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 explo=
red using `COPY TO` and `COPY FROM` with CSV files, uploaded to a SharePoin=
t or similar storage system. However, our infrastructure team raised concer=
ns around the computational load of large CSV processing and potential secu=
rity implications with file transfers.</div><div dir=3D"auto"><br></div><di=
v dir=3D"auto">We=E2=80=99d like to understand:</div><div dir=3D"auto">- Wh=
at 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 ongoin=
g archival?</div><div dir=3D"auto">- Any performance or consistency issues =
we should watch out for?</div><div dir=3D"auto"><br></div><div dir=3D"auto"=
>Your insights or any relevant documentation/pointers would be immensely he=
lpful.</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">Ramzy</div></div>

--00000000000006a744063655af4e--