Skip site navigation (1) Skip section navigation (2)

Recommended Procedure for Archiving Table Data

From: "Sally Ruggero" <sally(dot)ruggero(at)northelectriccompany(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Recommended Procedure for Archiving Table Data
Date: 2004-10-29 19:42:10
Message-ID: 00f801c4bdef$61f81c20$3500030a@Sally (view raw or flat)
Thread:
Lists: pgsql-general
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

Responses

pgsql-general by date

Next:From: Chris BrowneDate: 2004-10-29 19:43:10
Subject: Re: The reasoning behind having several features outside of source?
Previous:From: BenDate: 2004-10-29 19:24:35
Subject: column updates

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group