Thread: Recommended Procedure for Archiving Table Data

Sally Ruggero wrote:
> 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?

This isn't really a pgadmin question, should be pgsql-general or so.
You have two options:
- change the table name after dumping using sed to the desired name
- when restoring, do restore to the new (intermediate) table, and insert 
into the old from there; I'd prefer this solution.

Regards,
Andreas