By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,835 Members | 2,305 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,835 IT Pros & Developers. It's quick & easy.

Recommended Procedure for Archiving Table Data

P: n/a
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
Nov 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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?
Create a temporary table from a query definition...
BEGIN;
create temp table archive_table as select * from foo where date between ....
delete from foo where date between ...
copy foo to '/tmp/archive.copy'
commit;

You probably want to at least do some basic checks on the data like row
counts
but that will archive out the data.

You could also just create an archival schema that you could push stuff to.

Sincerely,

Joshua D. Drake

Thanks,
Sally


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.