I need advice on archiving data from our production
database.
Each night I would like to save and remove
a day's data from two weeks ago, from all the tables. I know how to delete
the data--though our schema does not specify cascaded deletes. However, I can't
figure out how to save the desired data. I'd like to save it in SQL
insert statement format, but I can't see how to get the data out. If I use psql
to create a temp table with the old data in it, then the table disappears when I
exit psql and I can't dump it with pg_dump. If I make a new table and put the
data in it, then when I dump it with pg_dump, all the insert statements have the
new table name rather than the original table name. I'm sure there's a good
solution for this standard problem. Does anyone have a suggestion?
Thanks,
Sally