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
