Message-ID: <4B80938E.1060504@itech7.com>
Date: Sun, 21 Feb 2010 07:29:42 +0530
From: Nilesh Govindarajan <lists@itech7.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.7) Gecko/20100120 Shredder/3.0.1
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: pgsql-admin@postgresql.org
Subject: Re: Backups
References: <4B7FCB1B020000250002F40E@gw.wicourts.gov>
In-Reply-To: <4B7FCB1B020000250002F40E@gw.wicourts.gov>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 02/20/2010 11:14 PM, Kevin Grittner wrote:
> 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

That is what exactly. I can prevent the changes to the database when the 
backup is going on, but how do I grant the permissions ? I don't want to 
key in the password for the superuser in the script.

-- 
Nilesh Govindarajan
Site & Server Adminstrator
www.itech7.com