Re: 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