Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 3C1E99FB222 for ; Thu, 26 Oct 2006 03:38:19 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 17373-07 for ; Thu, 26 Oct 2006 03:38:16 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp3.adl2.internode.on.net (smtp3.adl2.internode.on.net [203.16.214.203]) by postgresql.org (Postfix) with ESMTP id A5EBF9FA332 for ; Thu, 26 Oct 2006 03:38:10 -0300 (ADT) Received: from [192.168.8.200] (ppp247-71.static.internode.on.net [203.122.247.71]) by smtp3.adl2.internode.on.net (8.13.6/8.13.5) with ESMTP id k9Q6c5AF087213; Thu, 26 Oct 2006 16:08:06 +0930 (CST) (envelope-from pgsql@007Marketing.com) Message-ID: <454057CD.5030401@007Marketing.com> Date: Thu, 26 Oct 2006 16:08:05 +0930 From: Shane Ambler User-Agent: Thunderbird 1.5.0.7 (Macintosh/20060909) MIME-Version: 1.0 To: Duncan McDonald CC: pgsql-admin@postgresql.org Subject: Re: Backing Up Partial Databases References: <018701c6f8ac$0ad93bf0$2102a8c0@Firefly> In-Reply-To: <018701c6f8ac$0ad93bf0$2102a8c0@Firefly> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.971 tagged_above=0 required=5 tests=AWL, BIZ_TLD, SPF_HELO_PASS X-Spam-Level: * X-Archive-Number: 200610/172 X-Sequence-Number: 23475 Duncan McDonald wrote: > Hi All, > > I was wondering whether there was a way to back up partial sets of data > as INSERT statements? Pg_dump seems only to handle whole databases or > tables. > > I have two identical databases (primary and backup) and I need to > transfer a small portion of missing data from one to the other. Is there > an easier way to do this rather than SELECT-ing the interval then > parsing the ouput to look like INSERT statements? > I can think of a couple of ways - COPY (SELECT * FROM myPrimaryTable WHERE id<200) TO '/tmp/myData'; you can then COPY myBackupTable FROM '/tmp/myData'; Another option is to create a table to hold the selected rows then SELECT * INTO myTempTable FROM myPrimaryTable WHERE id<200; then use pg_dump --table=myTempTable > /tmp/myData the --inserts option of pg_dump will export as INSERT's if that is the way you want to do it. You will need to do a find and replace on the INSERT's to change the table name though. -- Shane Ambler pgSQL@007Marketing.com Get Sheeky @ http://Sheeky.Biz