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

Re: Recommended Procedure for Archiving Table Data

From: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
To: Sally Ruggero <sally(dot)ruggero(at)northelectriccompany(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Recommended Procedure for Archiving Table Data
Date: 2004-10-29 20:21:00
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
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...
create temp table archive_table as select * from foo where date between ....
delete from foo where date between ...
copy foo to '/tmp/archive.copy'

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

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


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(at)commandprompt(dot)com -
PostgreSQL Replicator -- production quality replication for PostgreSQL

Attachment: jd.vcf
Description: text/x-vcard (285 bytes)

In response to

pgsql-general by date

Next:From: Ian PilcherDate: 2004-10-29 20:56:46
Subject: Re: UTF-8 -> ISO8859-1 conversion problem
Previous:From: Chris BrowneDate: 2004-10-29 19:47:22
Subject: Re: Reasoning behind process instead of thread based

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