Re: pg_dumpall and pg_dumps

Simone Tellini <tellini@areabusiness.it> writes:
> while you're at it, there are 2 other problems I've always experienced
> with pg_dump/pg_dumpall:

>  1. it fails to restore databases owned by users who aren't allowed to
>     create databases: the dump contains a "CREATE USER x NOCREATEDB"
>     line at the top, then tries to \connect x and CREATE DATABASE y,
>     which obviously fails.

There is a plan to fix this, I believe.  Someone's working on an OWNER
clause for CREATE DATABASE; once that exists, we'll make pg_dumpall
issue all the CREATE DATABASE commands as superuser.

>  2. you can't specify username and password on the command line: this
>     makes it impossible to backup automatically all the databases from a
>     cron script if you use password authentication, for instance.

I see no good way around that, except to not use password
authentication.  (Even if it worked, it'd be a bad idea to embed the
password in the backup script.)

This is more practical than it used to be given the availability of
ident-style auth for Unix-socket connections (on many platforms) in 7.2.
Even if you don't have a platform with support for it, ident auth on
localhost TCP connections isn't an unreasonable way to go.

            regards, tom lane