X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id AB7E5D1D2C6 for ; Fri, 5 Mar 2004 21:37:46 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 75276-10 for ; Fri, 5 Mar 2004 17:37:47 -0400 (AST) Received: from bramble.mmrd.com (unknown [65.217.53.66]) by svr1.postgresql.org (Postfix) with ESMTP id 5F58BD1D29D for ; Fri, 5 Mar 2004 17:37:44 -0400 (AST) Received: from thorn.mmrd.com (thorn.mmrd.com [172.25.10.100]) by bramble.mmrd.com (8.12.8/8.12.8) with ESMTP id i25L9hcM031255 for ; Fri, 5 Mar 2004 16:09:43 -0500 Received: from gnvex001.mmrd.com (gnvex001.mmrd.com [192.168.3.55]) by thorn.mmrd.com (8.11.6/8.11.6) with ESMTP id i25Lbkl29020 for ; Fri, 5 Mar 2004 16:37:46 -0500 Received: from intergylinux (intergylinux.mmrd.com [172.25.4.92]) by gnvex001.mmrd.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2657.72) id FVMAW16M; Fri, 5 Mar 2004 16:37:43 -0500 From: Chris Ruprecht Organization: CDRBill To: pgsql-admin@postgresql.org Subject: How to do fast, reliable backups? Date: Fri, 5 Mar 2004 16:37:42 -0500 User-Agent: KMail/1.5.1 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200403051637.42457.chris@ruprecht.org> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/72 X-Sequence-Number: 12641 Hi all, I am wondering how you guys back up your databases. Say, I have a 20 GB database, data and indexes. If I run pg_dump on this, it backs up the schema and the data. When I have to restore this, I whould have to run this through psql which would then re-build the indexes after it has inserted the records. That's not really a feasable option as it takes too long. If I back up the pgdata directory, I will get inconsistent data, as somebody might update data in 2 tables while my backup is running, one change in a table I have already backed up, one in a table I have not. The solution would be to shut the database server down, do the backup and then start everything back up. But that's not really an option in a 24/7 environment. What I'd like to see is a transaction aware backup which backs up indexes as well as data. Any ideas? Best regards, Chris