Pg dump improvements
From PostgreSQL wiki
Jump to navigationJump to searchBackground
Currently only pg_dumpall emits database and roles attributes, such as CREATE ROLE, ALTER ROLE, ALTER DATABASE SET and ALTER ROLE SET commands.
We would like to have 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). A discussion has been started again via pgsql-hackers@postgresql.org and this page attempts to summarize what has been discussed there.
Proposal
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 to handle this 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-roles-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.
