Re: Automated database backups and authentication

If you have a few users and many databases, and the user that created the
database will be the one to access it, it might work for you to do a query
on pg_database to get a list of databases and their creators, then do a
pg_dump of each database while passing the password (echo <password>
redirection or expect script) for each database depending on the user. (I
something similar in MySQL, though not the password pasing, and it looks
like it can be done in pSQL also).

Alternatively, it seems you might run pg_dumpall as the superuser and after
dumping all the databases, chown the dump files?

Murthy

"Out of the mouths of babes ..."

>-----Original Message-----
>From: Darren McClelland [mailto:darren(at)zonarsystems(dot)com]
>Sent: Tuesday, August 06, 2002 14:15
>To: pgsql-admin(at)postgresql(dot)org
>Subject: [ADMIN] Automated database backups and authentication
>
>
>Hello,
>
>I would like to backup a set of databases from a cron job. It
>looks like
>pg_dumpall will do the trick. The only problem is that there
>doesn't seem to
>be any way to pass the database password to pg_dumpall in a
>non interactive
>manner. I'd like to use some kind of access control on the
>databases, but it
>would need to be able to function non interactively.
>
>Ideally whatever I do would be able to function across a
>network, so that one
>database server could backup to a second - kind of a poor
>man's replication.
>
>I haven't found any real answers in any of the archives, or
>docs. Has anyone
>here done something like this or have any thoughts?
>
>Thanks,
>Darren McClelland
>
>
>---------------------------(end of
>broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>