Обсуждение: Backing up and deleting a database.
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 htis? I would appreciate any comments about what approaches have been used that work. Thanks for any info. Andrew -- ___________________________________________ Andrew J. P. Maclean Centre for Autonomous Systems The Rose Street Building J04 The University of Sydney 2006 NSW AUSTRALIA Ph: +61 2 9351 3283 Fax: +61 2 9351 7474 URL: http://www.acfr.usyd.edu.au/ ___________________________________________
Hi, On Tue, Jul 15, 2008 at 2:52 PM, Andrew Maclean <andrew.amaclean@gmail.com> wrote: > 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 htis? It sounds like table partitioning could be useful in your situation, depending on what your data looks like, and how you want to query it. Its worth your taking the time to read: http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html. If you're basically inserting a series of observations or something to a large table this could be useful - you can use it to increase the amount of data you can easily manage, and to automate something like a rolling 2-month window of online data. A script could be put together to periodically dump out the oldest partition, drop it, create a new partition, and maintain the associated triggers. Charles Duffy
In response to "Andrew Maclean" <andrew.amaclean@gmail.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@collaborativefusion.com Phone: 412-422-3463x4023