Re: pg_dump and pg_dumpall in real life (proposal)

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

On 11/11/2013 11:20 PM, Josh Berkus wrote:
> 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)
>>
[.........]
>
>> 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
>

Well, I am willing to take a chance on the first suggestion if nobody
else has the time or energy.

I have never sent a patch or have worked with the postgres code, but I
think it can be done without a lot of work with some reuse of the code
used in pg_dumpall.

This is a proposal based on the feedback we have received:

* pg_dump will also deliver information about "ALTER DATABASE ... SET"
data for a given database when the option '--create' is used.

* pg_dump will deliver information about ROLES used and "ALTER ROLE
... SET" data for a given database when a new option i.e.
"--roles-global" is used.

* pg_restore will restore ROLE information when used with a new option
i.e. "--roles-global" and "ALTER DATABASE ... SET" information when
used with the '--create' option.

* We need to do something with how pg_restore will handle ROLES
information because some security concerns when restoring roles that
already exists on the target server.

Some of the suggestions are:

a) Implement and use CREATE ROLE IF NOT EXISTS and just throw a
warning or handle the "role already exists" error message gracefully.

b) Use a new option i.e. "--reuse-roles-in-conflict" to behave like
suggestion a). If this option is not used, pg_restore will stop with a
fatal error when a role already exist.

c) Use a new option i.e. "--on-role-error-stop" to stop with a fatal
error when a role already exist. If this option is not used pg_restore
will behave like suggestion a).

d) Use a new option i.e. "--rename-roles-in-conflict" to rename the
roles that already exists. If this option is not used, pg_restore will
stop with a fatal error when a role already exist.

I think I prefer b) to continue with the postgres spirit of security
by default. d) is too complicated for me due to lack of knowledge of
the postgres code.

Comments?
regards,
- --
Rafael Martinez Guerrero
Center for Information Technology
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.14 (GNU/Linux)

iEYEARECAAYFAlKB8w0ACgkQBhuKQurGihSq7QCfZdzreRGNRx0vUzXPjYqzNOIP
LqgAoJnfeCYjsfEUmsYvvp3DSL959IRL
=8Ynv
-----END PGP SIGNATURE-----