Re: Backups

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