Re: pg_dump and pg_dumpall in real life

On 11/11/2013 03:06 PM, David Johnston wrote:
> Josh Berkus wrote
>> Well, then we just need pg_restore to handle the "role already exists"
>> error message gracefully. That's all. Or a "CREATE ROLE IF NOT EXISTS"
>> statement, and use that for roles.
>
> My only qualm here is if the exists check is based off of role name only.
> If database "A" and database "B" came from different clusters but both have
> a role "david" the actual identity of "david" is (could be) different
> because the source cluster.
>
> The risk of such occurring is a high-security situation is likely to be
> small but some kind of "--ignore-different-cluster-same-role" flag may be
> worthwhile such that pg_restore will error unless that flag is set (i.e.,
> high security by default). The error itself should be rare enough most
> people wouldn't even notice it is there but seeing such an error (with a
> hint provided as well) would be easily able to disable and continue on with
> the restore.

I'd do the opposite: let's optimize for the most common case, not the
least common one. So we'd do --role-errors, which would throw a fatal
error on duplicate roles, instead of just posting a WARNING.

Again, this is all rather academic, unless you know someone who's eager
to dig into pg_dump/pg_restore.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com