Обсуждение: Backup certain months old data
Hello!
I would like to know how we can backup certain months old data from PgSQL and then delete it. The intent is to backup everything that is older than 2 quarters to a blob storage and delete it, to improve performance and reduce billing.
Regards
Siraj
On Mon, Jan 22, 2024 at 10:12 PM Siraj G <tosiraj.g@gmail.com> wrote:
Hello!I would like to know how we can backup certain months old data from PgSQL
Use the COPY command.
and then delete it.
That would depend on how many records, how big the records are, and if there's index support on the "date" field.
The intent is to backup everything that is older than 2 quarters to a blob storage and delete it, to improve performance and reduce billing.
I had to do something similar for my previous employer.
1. Used COPY to dump the old data.
2. CREATE INDEX i_foo_sd1 ON foo (some_date);
3. DELETE FROM foo WHERE some_date BETWEEN x AND y; When there wasn't a lot of data, it was the whole month. When there was a lot of data, I looped through it one day at a time..
4. DROP INDEX i_foo_sd1;
It was a bash script that reads a text file, where each row is a tab-delimited record with table name and column,
On 1/22/24 19:11, Siraj G wrote: > Hello! > > I would like to know how we can backup certain months old data from > PgSQL and then delete it. The intent is to backup everything that is > older than 2 quarters to a blob storage and delete it, to improve > performance and reduce billing. 1) Postgres does not track the insert/update times of data, so unless you have fields that track that you will not be able to do that. 2) If you do have a way of telling the times for all the records, are you sure that removing the data on a time basis will be clean enough operation that it will not leave the data in a compromised state? 3) Have you considered partitioning? See here: https://www.postgresql.org/docs/current/ddl-partitioning.html > > Regards > Siraj -- Adrian Klaver adrian.klaver@aklaver.com