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