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,
