Pg dump improvements

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
Line 3: Line 3:
 
Currently only pg_dumpall emits database and roles attributes, such as CREATE ROLE, ALTER ROLE, ALTER DATABASE SET and ALTER ROLE SET commands.
 
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 and a proposal has been started via pgsql-hackers@postgresql.org and this page attempts to summarize what has been discussed.
+
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 and a proposal has been started via pgsql-hackers@postgresql.org and this page attempts to summarize what has been discussed there.
  
  

Revision as of 15:41, 12 November 2013

Background

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 and a proposal has been started via pgsql-hackers@postgresql.org and this page attempts to summarize what has been discussed there.


Proposal

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

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

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

Incomplete itemWe 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:

  • Implement and use CREATE ROLE IF NOT EXISTS and just throw a warning or handle the "role already exists" error message gracefully.
  • 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.
  • 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).
  • 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.

Some links

Personal tools