Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 424E72E0055 for ; Wed, 7 May 2008 17:09:59 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 64287-06-2 for ; Wed, 7 May 2008 17:09:46 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from yw-out-2324.google.com (yw-out-2324.google.com [74.125.46.29]) by developer.postgresql.org (Postfix) with ESMTP id 24DB42E0047 for ; Wed, 7 May 2008 17:09:48 -0300 (ADT) Received: by yw-out-2324.google.com with SMTP id 3so260832ywj.73 for ; Wed, 07 May 2008 13:09:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=hcMELPS/3DLcpjJXsb3R8B2+zEt7MlpXEfilPc0ioQs=; b=p5sI/Z7Kjq2Cbjw9lIaP6BYUawlzn3pQN5ddXSvs2A3E10MNPUdSVbgoW7zOEyMQ0xy5BI/ugDCiZctYMETCnXFxS0uYcI1ggK9YDgcyatdJo3L9jCG4693KmK263qJIOaYf+YmKgccF5NdP5XsMfPWyPod7biTiySSR6jyJq6Q= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=x0kq7ZBlWrG+ROE76erE1rQMiQB8k8Gnr4vXhdkci8iQZnPL8vpg3QC5XG4LcmH3PD/FLUYz3KvzJO72VMxFFjpUBqp7mowGsi4wyx7P492lkACK0VLBnPKxCAPVLVKuT83CFLYhhMkUect0n+4PnNvxiaOCnaXYBr1oojkRfrI= Received: by 10.150.11.1 with SMTP id 1mr2589218ybk.5.1210190985778; Wed, 07 May 2008 13:09:45 -0700 (PDT) Received: by 10.150.138.11 with HTTP; Wed, 7 May 2008 13:09:45 -0700 (PDT) Message-ID: Date: Wed, 7 May 2008 16:09:45 -0400 From: "David Wilson" To: "John Smith" Subject: Re: Backup/Restore of single table in multi TB database Cc: pgsql-general@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200805/238 X-Sequence-Number: 132637 On Wed, May 7, 2008 at 4:02 PM, John Smith wrote: > Does anyone see a problem with this approach (e.g., correctness, > performance, etc.)? Or is there perhaps an alternative approach using > some other postgresql mechanism that I'm not aware of? Did you already look at and reject pg_dump for some reason? You can restrict it to specific tables to dump, and it can work concurrently with a running system. Your database is large, but how large are the individual tables you're interested in backing up? pg_dump will be slower than a file copy, but may be sufficient for your purpose and will have guaranteed correctness. I'm fairly certain that you have to be very careful about doing simple file copies while the system is running, as the files may end up out of sync based on when each individual one is copied. I haven't done it myself, but I do know that there are a lot of caveats that someone with more experience doing that type of backup can hopefully point you to. -- - David T. Wilson david.t.wilson@gmail.com