Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 360D5633305 for ; Sat, 20 Feb 2010 13:44:46 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 35839-09 for ; Sat, 20 Feb 2010 17:44:34 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id 55901632F21 for ; Sat, 20 Feb 2010 13:44:35 -0400 (AST) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Sat, 20 Feb 2010 11:44:33 -0600 Message-Id: <4B7FCB1B020000250002F40E@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Sat, 20 Feb 2010 11:44:27 -0600 From: "Kevin Grittner" To: , Subject: Re: Backups Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.464 tagged_above=-10 required=5 tests=AWL=0.135, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201002/153 X-Sequence-Number: 33756 Nilesh Govindarajan wrote: > I want a script to backup all databases table by table in different > .sql files. My first question would be whether they all need to be from the same point in time? Is it a requirement that if they were all restored they would provide a database with referential integrity, etc.? Or is it OK if each table represents a different moment in time? Either way, you probably want pg_dump. If you don't care about having these separate files to collectively provide a consistent image of a moment in time, or you can prevent any modifications while you are running a set of backups, you could use pg_dump with the -t option. Otherwise you probably need to use pg_dump (or pg_dumpall) and parse the output to split it into the required files. Or you might be able to do pg_dump to the custom format and extract each table into a separate file. > Also since it is a script, there has to be some role having read > access to all tables on all databases. How to grant that ? If you don't want to use the database superuser and don't already have some other role with the appropriate rights, you will have to grant them. It's hard to provide details without more information about what's already there. -Kevin