From: | Andreas Pflug <pgadmin(at)pse-consulting(dot)de> |
---|---|
To: | Sally Ruggero <sally(dot)ruggero(at)northelectriccompany(dot)com> |
Cc: | pgadmin-support(at)postgresql(dot)org |
Subject: | Re: Recommended Procedure for Archiving Table Data |
Date: | 2004-10-29 17:22:54 |
Message-ID: | 41827C6E.9070607@pse-consulting.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgadmin-support |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Keith Worthington | 2004-10-29 18:46:02 | graphic query builder |
Previous Message | Andreas Pflug | 2004-10-29 16:27:49 | Re: pgAdmin III v1.2 Beta-3: Problem with "alter |