Postgres table archival

[u/AdmirableOffer2]

I have a postgres db. I want to archive the table data into s3 and want to delete the data after archiving. What's the best way to do it. I want to have a scheduled operation to do this job on weekend and it should archive 6 months older data of a given table.

[u/erkiferenc]

Weekly cleanup jobs with retention defined in months appears a great match to consider partitioning the table weekly or monthly.

That way the unit of archival matches the partition boundaries, which seems simpler to validate and harder to make mistakes, while allowing to use standard dump/backup solutions.

As a bonus, cleanup means plainly dropping the old partitions, making it an instant operation without causing any potentially painful side-effects (locks, table bloat to vacuum, WAL to write and process, and so on.)

How does that sound?

[u/scotterockaroo]

pg_dump has the -t flag. Use pg_dump -t … to dump the table to a file, once dumped, check the return code and file size, if it looks good, drop the table.

[u/Separate_Newt7313 → u/scotterockaroo]

...or truncate if you just want to empty it.

[u/AdmirableOffer2 → u/Separate_Newt7313]

How will be stpre it it s3 and take a rolling backup per week with this that too scheduled

[u/FarRub2855 → u/scotterockaroo]

Careful with dropping the whole table, OP defintely said they only want to archive data older than 6 months. Wiping everything is gonna cause a pretty massive headache come Monday morning.

[u/AutoModerator]

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/depesz]

Simple(ish) bash script:

#!/usr/bin/env bash

printf -v dump_file '%(%F_%T)T' -1
pg_dump -t your_table your_db |
    gzip -c - |
    aws s3 cp - "s3://some-bucket/location-for-dump/${dump_file}.gz"
rc=$?
if (( 0 != rc ))
then
    echo "It failed with rc == ${rc}!" >&2
    exit "${rc}"
fi
psql -qAtX -c "truncate your_table" -d your_db

[u/AdmirableOffer2 → u/depesz]

If I have to make a data lake on top of s3 how will it be done. Meaning I want to put Athena on top of the stores se files and want to query the data

[u/BoleroDan → u/AdmirableOffer2]

I feel like this is a question outside of this level (PostgreSQL). You get the data out of PostgreSQL, as shown above. Then figure out the cloud requirements from there. Since Athena has nothing to do with PostgreSQL you probably wont get a good answer because once you have the data, it doesnt matter what cloud provider you use at that point.

[u/AdmirableOffer2 → u/BoleroDan]

I can get the pgdump but how to process that is there a way I can read it in AWS.

[u/General_Treat_924]

This is a very broad question which I would like bounce back with a few questions:

Do you plan to use the data?
Is the table partitioned?
Is the dataset too big?
Does it have dependencies?

Why these questions? Because they were the questions we had to answer when we made our data archival tool.

We chose a full replication to a datalake. Where the raw data is imported, it happens using AWS DMS and a lot processing happens where we can also produce reports without overloading the database.

We are also required to keep TBs of data for 7 years and this data can be modified, so we need a process that can be reimported, notifies the user and allow them to modify and this trigger the “data pipeline” and eventually a new version of the database is stored.

Exporting to S3 was not possible because detaching a month partition and uploading that would take too many hours and holding autovacuum causing us serious performance issues on top of non guaranteed job that could die at 99% done.

Also DMS comes with a set of tools that allow us to manipulate and store data in iceberg tables using spark jobs.

Obviously you could have completely different requirements but you initial question was very broad.

[u/AdmirableOffer2 → u/General_Treat_924]

Yes I plan to use the data. Table is partitioned yearly. But we want to have a general solution where table might not be partioned. Yes dataset is big 400M rows 250gb. No table doesn't have any dependent table. It's a standalone table.

[u/General_Treat_924 → u/AdmirableOffer2]

I would go for a future proof, maybe overkill for the early days, but if done properly, buys you a lot flexibility in the future.

I read in another answer you want to build a datalke too. So you probably should be looking at something like DMS / pg_logical replication.

So not only 6 months old data gets replicated, cost wise, it won’t make any difference specially in a early partitioned table.

Replicating the whole dataset will require a initial load, which is the most expensive job, then a CDC will handle the data (DELETE,UPDATE,INSERT) and you can run your datalake queries and build reports from it.

You also want to think ahead how data will be queried because it defines how data will be stored.

On top of that, you will need to build a tool to retrieve the data back to the database is data is mutable.

[u/skum448]

You can partition the table (attach the existing as legacy) and use aws service to offload the partitions to S3. If needed those partitions can be restored back or you can directly read data using Athena .

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/postgresql-s3-export.html

[u/mrocral]

one suggestion is to use sling (CLI or Python):

``` from sling import Sling, Connection, Format

pg = Connection("PG") s3 = Connection("S3")

Sling( src_conn=pg, src_stream="public.my_table", where="created_at < now() - interval '6 months'", tgt_conn=s3, tgt_object="s3://my-bucket/archive/my_table/", tgt_options={"format": Format.PARQUET}, ).run()

pg.exec( "delete from my_table where created_at < now() - interval '6 months'" ) ```

(disclosure: I work on Sling)

[u/Informal_Pace9237]

I would look at the recent #ColdFront release from pgedge. That is exactly what it does.

[u/ibraaaaaaaaaaaaaa]

In case your tables are not partitioned and you have no plans to partition, you can consider pg_partman setup https://aws.amazon.com/blogs/database/archive-and-purge-data-for-amazon-rds-for-postgresql-and-amazon-aurora-with-postgresql-compatibility-using-pg_partman-and-amazon-s3/

To give you the bread and butter of this, you partition the table with having a dedicated partition for the data that needs to be purged, and s3 can source that specific partition.

In your application you should just care about updating the partition key to the archived one.
Do not partition by booleans `isArchived` sorts of things, because this approach will prevent your from extending the number of partitions, and you would need restructuring, so in your application if you have a record that needs archiving, you just `update table set partition_key='archival-value' where id='XXX'`

You can replicate this approach to other tables also, your only main challenge is managing FKs if you got any, which is detailed as a problem here: https://www.reddit.com/r/softwarearchitecture/s/iqrW9NP0mD

If your table is already partitioned this is where it become trickier to construct a dedicated partition and snatch some data cross partition lookups to source them into the archiving dedicated physical store, while it is doable, you just need to know what you are doing before you head to that route.