Re: pg_dump and pg_dumpall in real life

On 11/11/2013 06:24 AM, Stephen Frost wrote:
> * Rafael Martinez (r(dot)m(dot)guerrero(at)usit(dot)uio(dot)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