On Mon, Jan 22, 2024 at 10:12 PM Siraj G <tosira...@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,
