Re: Backing up and deleting a database.

In response to "Andrew Maclean" <andrew(dot)amaclean(at)gmail(dot)com>:

> We have a database that grows in size quite quickly. Of course we
> backup nightly and keep a weeks worth of data
>
> However we need to keep a few months data online, but the rest can be
> archived as it will be unlikley that it will be used again.
>
> As I see it we can:
> 1) Run a query to drop/delete old data, the downside here is that we lose it.
> 2) Stop the database (this is important because clients are writing to
> it), back it up, delete it and recreate the database. Has anyone done
> this? Do they have a script for this?

I'm confused. If you can't back up the data because clients are writing
to it, then it must be interesting to those clients, so why are you able
to delete it?

Would a script that does the following work:
1) BEGIN; CREATE TABLE stage_archive AS (SELECT * FROM ??? WHERE [some
where clause to identify old records]); DELETE FROM ??? WHERE [same
where clause]; COMMIT;
2) COPY stage_archive TO 'some_file.sql'
3) Back up or otherwise archive some_file.sql
4) DROP TABLE stage_archive;

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran(at)collaborativefusion(dot)com
Phone: 412-422-3463x4023