Pg dump improvements

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
Line 25: Line 25:
 
}}
 
}}
 
Some of the suggestions to handle this are:
 
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.
+
* 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.
+
* 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).
+
* 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.
+
* 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.
  
 
</div>
 
</div>

Revision as of 15:43, 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:

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

Some links

Personal tools