Re: Backup certain months old data

On Mon, Jan 22, 2024 at 10:12 PM Siraj G <tosiraj(dot)g(at)gmail(dot)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,