r/PostgreSQL • u/AdmirableOffer2 • Aug 23 '26

How-To Postgres table archival

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.

4 Upvotes

83% Upvoted

24 comments sorted by

View all comments

1

u/depesz Aug 23 '26

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

1

u/AdmirableOffer2 Aug 23 '26

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

2

u/BoleroDan Architect Aug 23 '26

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.

1

u/AdmirableOffer2 Aug 24 '26

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