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