Re: pg_dumpall and pg_dumps

Simone Tellini <tellini(at)areabusiness(dot)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