Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 0901F9FB2BD for ; Wed, 11 Apr 2007 22:12:36 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 82925-07 for ; Wed, 11 Apr 2007 22:12:22 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from vms044pub.verizon.net (vms044pub.verizon.net [206.46.252.44]) by postgresql.org (Postfix) with ESMTP id 1206C9FA2EF for ; Wed, 11 Apr 2007 22:12:28 -0300 (ADT) Received: from new-host.home ([71.166.59.121]) by vms044.mailsrvcs.net (Sun Java System Messaging Server 6.2-6.01 (built Apr 3 2006)) with ESMTPA id <0JGD00IY320QKQI1@vms044.mailsrvcs.net> for pgsql-admin@postgresql.org; Wed, 11 Apr 2007 20:12:26 -0500 (CDT) Date: Wed, 11 Apr 2007 21:12:02 -0400 From: Robert Treat Subject: Re: Restoring single database from an export dump of a cluster In-reply-to: To: pgsql-admin@postgresql.org Cc: "Bhella, Paramjeet" Message-id: <200704112112.03167.xzilla@users.sourceforge.net> MIME-version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-transfer-encoding: 7bit Content-disposition: inline References: User-Agent: KMail/1.9.1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/109 X-Sequence-Number: 25173 On Thursday 05 April 2007 17:13, Bhella, Paramjeet wrote: > Hi > > In our production environment we use pg_dumpall to backup all the > databases of a cluster as there are 200+ databases in a cluster. Some > times there is a situation where we have to restore a single database > from a cluster. According to the postgres docs there is a pg_restore > utiltity which can be used to restore individual tables, schemas, > databases etc. But pg_restore requires the pg_dump to be in archive > format. With pg_dump we can use option -Fc to make the dump in non-plain > text format but I cannot use -Fc option with pg_dumpall. Is there a way > to import a single database from an export dump of a cluster? Your help > or guidance will be highly appreciated. > Currently there is no way to restore a single database from a pg_dumpall short of heavy parsing of the dumpall script. What I usually recommend to folks (and do with our servers @ omniti) is to have a script that does a pg_dumpall -g for the global objects and then individual pg_dump -Fc's for each database in the cluster. -- Robert Treat Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL