Re: pg_dump and pg_dumpall in real life

On 11/11/2013 06:24 AM, Stephen Frost wrote:
> * Rafael Martinez (r.m.guerrero@usit.uio.no) wrote:
>> * We need a pg_dump solution that can generate in one step all the
>> necessary pieces of information needed when restoring or cloning a
>> database. (schema, data, privileges, users and alter database/role data)
> 
> This sounds pretty reasonable and should be possible to implement- but
> realize that, on the restore side, you might end up with multiple
> attempts to create the same objects.  Consider a role that's depended
> upon by objects in two databases- it would be included in the dump of
> both of those databases and if you restored both of those into the same
> cluster, one of the CREATE ROLE statements would fail.

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.

>> * It would be great to be able to tell pg_restore that user1 in the
>> dump will became user2 in the restored/cloned database. The same for
>> the name of the database.
> 
> This is a lot uglier, unfortunately.  We've seen this multiple times
> before- there's not a good way to provide such a mapping as a command
> line option.  There may also be issues with the dependency resolution..

This sounds like one of the reasons we still *have* text-mode dumps.
For stuff like this.

> As for 'what we need', I'd think someone with the time and energy to
> write the patch and work with the community to implement it..

+1

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