Re: Prune or Purge data stored on Postgres 14.13

On 2024-10-11 20:43 +0200, Kaushal Shriyan wrote:
> I am running PostgreSQL 14.13 on RHEL 8.10 OS. Is there a way to Prune or
> Purge data stored on PostgreSQL 14.13 ?
>
> For example I have Analytics data stored in PostgreSQL 14.13 server for
> last 1 year (1st September 2023 till date)
>
> Is there a way to prune analytics data from Analytics Data stored on
> PostgreSQL 14.13 Database server starting from 1st September 2023 till
> February 29, 2024 and archive it to a tape drive or network file storage?
> (Six months analytics data). This is to make sure we do not run into an out
> of disk/storage space situation. Once we carry out the prune operation, we
> only have 6 months of data in the PG Database server and the older data
> beyond six months will be on tape drive or network file storage.

Use tablespaces[1] to store data on external storage. Is the table
already partitioned? If yes, than you can use ALTER TABLE to move the
partitions that end before 2024-03-01 to a tablespace on external
storage.

If the table is not partitioned[2] then you should think about doing
that because it simplifies the pruning/archiving process if it's going
to be a regular task. But existing tables cannot be partitioned. You'd
have to create a new table and attach partitions. But the existing
table can also be attached as one partition and you can wait 6 more
months when you probably want to purge data again. Let's say you create
those partitions now (October 2024), then the current data will go into
a partition P until 2024-11-01 and newer data will go into the next
partition. After 2025-05-01 you can move partition P to another
tablespace. This of course depends on how much disk space you can still
afford in that time.

But this could also work without partitioning if the archived data is
not required to be available alongside the live data. Every 6 months
you could create a new archive table with a tablespace on external
storage and insert the rows of the last 6 months into that new archive
table. Then delete those rows from the live table and VACUUM it. The
live table will still use the same amount of disk space (unless you use
VACUUM FULL which will, however, use extra disk space while writing a
new copy of the table), but new rows will occupy the disk space that was
previously occupied by the now-deleted rows and reclaimed by VACUUM.

Hope that helps.

[1] https://www.postgresql.org/docs/14/manage-ag-tablespaces.html
[2] https://www.postgresql.org/docs/14/ddl-partitioning.html

--
Erik