Pg dump improvements

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
(Init page)
 
(version 1)
Line 1: Line 1:
== Pg_dump improvements proposal ==
+
== Background ==
  
 
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.
Line 6: Line 6:
  
  
.....
+
== Proposal ==
 +
<div style="padding: 1ex 4em;">
 +
 
 +
{{TodoItem
 +
|pg_dump will also deliver information about "ALTER DATABASE SET" data for a given database when the option ''--create'' is used.
 +
}}
 +
 
 +
{{TodoItem
 +
|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.
 +
}}
 +
 
 +
{{TodoItem
 +
|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.
 +
}}
 +
 
 +
{{TodoItem
 +
|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:
 +
* 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.
 +
 
 +
</div>
 +
 
 +
== Some links ==
 +
 
 +
* [http://www.postgresql.org/message-id/5280E2AE.8070106@usit.uio.no]http://www.postgresql.org/message-id/5280E2AE.8070106@usit.uio.no
 +
* [http://www.postgresql.org/message-id/4864F001.50909@archonet.com]http://www.postgresql.org/message-id/4864F001.50909@archonet.com
 +
* [http://www.postgresql.org/message-id/11646.1272814212@sss.pgh.pa.us]http://www.postgresql.org/message-id/11646.1272814212@sss.pgh.pa.us

Revision as of 15:40, 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 done via pgsql-hackers@postgresql.org and this page attempts to summarize what has been discussed.


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