Re: pg_dump and pg_dumpall in real life

* 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.

I'd think this would also be a new pg_dump option along the lines of
'include global dependencies' or similar. Reading the older threads, I
also agree that a '--create' version of pg_dump should include the
various SET commands for the database to be configured the same as the
one being dump'd. The next part seems simple- let's get someone to do
it.. :)

> * 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..

> * For serious backup management of large and complicated databases,
> pg_dump with the custom output + pg_restore is the only feasible solution.

Sure; is there a question here? I don't think that means we're going to
change the default, though there is a whole other thread on that
subject.

> What do you think about the subject? Does it sound like a reasonable
> proposition? What do we need to implement some of these changes?

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..

Thanks,

Stephen