Re: pg_dump and pg_dumpall in real life

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 11/11/2013 09:59 PM, Rafael Martinez 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)

... and if some users/roles already exist, but have different
meanings? Or some roles exist and some don't?

I'm very strongly against adding and using CREATE ROLE IF NOT EXISTS.
pg_restore should handle this case-by-case, forcing the user to
specify explicitly role-by-role that they want a given role in the
existing DB re-used if it exists, or want a new one created with a new
name in case of a clash.

A --rename-all-conflicting-roles and --reuse-all-conflicting-roles
option could be added for the all-or-none options. IMO if neither is
specified, the existence of any role name conflict should be a restore
error.

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

Agreed that this would be useful. Needs to deal with the case where
the users should be separated but they should remain a member of some
common role, though - eg "olduser" becomes "newuser" but the dumped
"olduser" was member of role "users" and "newuser" should also be
member of "users", not some renamed role.

- -- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.15 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQEcBAEBAgAGBQJSgcfKAAoJELBXNkqjr+S2XWIH/2c1Mcd4ldVTAPw/jAln4gNM
YH8SRPlsGU0fqfbYoKg/1y0K/Wdjdlac9bjjGzYpODryaXGopf1i+pWaphF2kJTM
LeMRVgEFEW7u2Dr6FXajQTQCiXLnA8C16NmmgIdqZZgYCsOwCorG+gFNfI8fZyft
okCQpYcljGXzlc218DI6/o4OZBBSdLh8diTzF8+xywoXJZopdAwfHDPPpAvizPye
rcUUkq1svArq78HakSuI8HoCy3ZHuiCf8mQEUPcLhFrwgh+bkrs29W7YAdD75gr4
yp32XeyOY5npXHaG9mHghs7anbUnwywJVEzpwKAf0SyPe7zunw8fdtx2NSF70no=
=dusS
-----END PGP SIGNATURE-----