From PostgreSQL wiki
Often it is desirable to dump the database as SQL statements; fine database tools (pgAdmin, phpPgAdmin) need to support this. It is also useful for migration and backup.
There appears to be a Google SoC 2007 idea for this functionality.
Many programming languages (Prolog,ML,Lisp) offer fascilities to dump code as source; with the use of functions this can be done in SQL too.
Currently, the 'official' way of generating DDL dumps from PostgreSQL is pg_dump command, which is external to the backend. This makes it somewhat impractical to use in SQL queries.
Currently there are several implementations of DDL dumper:
- pg_dump command line utility that comes with PostgreSQL
- PgAdminIII has DDL dumping fascilities
Variant: dump as ALTERs when possible. these can sometimes better be used for upgrading existing schemas
Variant: support dumping in strict ANSI SQL.
- schema replication / comparison
- support for DB admin tools
- create packages for xpg_package
- relocation of code from where it is (pg_dump) to where it belongs (backend). This makes this code more accesible to more software.
Problems and limitations of pg_dump
- external to the database. This probably violates some of Codd's 12 rules for RDBMS.
- complex internals, difficult to maintain
- can't use same tool for different versions of backend, it has to be upgraded
- it cannot dump functions only
- it will not properly dump end restore my somewhat hairy database (7.4)
Quick and dirty solution
CREATE OR REPLACE FUNCTION system(text) RETURNS text AS 'my $cmd=shift; return `cd /tmp;$cmd`;' LANGUAGE plperlu; CREATE OR REPLACE FUNCTION ddl_pgdump(name, name) RETURNS text AS ' SELECT system(PGUSER=||current_user|| pg_dump --attribute-inserts --no-owner --no-reconnect || --schema || $1 || --table || $2 || || current_database()); ' LANGUAGE sql;
Of course, we would prefer something more sophisticated.
Dumper backend API
Plan is to implements a set of DDL generating functions withing the backend and an external pg_dump compatible replacement.
High-level language (plpgsql and plperl) is preffered and sufficient.
Generally, the dumper functions are partitioned according to what they dump; this way it is possible to use a high-level program (ie. the before mentioned pg_dump replacement) for ordering dumps. High level tool should also take care of creating pg_restore compatible compressed dump.
DDL functions (should) return sql_statement type.
When dumping objects not in current schema, SQL identifier names are prefixed with schema name. This means you can set the search_path and have functions behave as expected.
Currently we have these end-user functions:
ddl_package(name) - dump a whole schema as one neat SQL ddl_script(name, name) - dump a particular object. this is intended to be used in GUI tools and such ddl_script(name, name, text) - dump a user-friendly script. $3 specifies what to dump.
ddl_script(statement_type,object_type,namespace,name,name) statement type: create, alter od drop object type: table, view, function, type, domain, constraint, index, rule, trigger, grant, default
These are support functions used by the ones above.
ddl_all(name) ddl_class(regclass) - dump a particular class (table or view) ddl_constraints(name) - dump constraints for a whole schema ddl_constraints(regclass) - dump constraints on a particular class ddl_contrib(name) - dump contrib/dependencies. TO BE REPLACED. ddl_defaults(name) - dump column defaults for a whole schema ddl_defaults(regclass) - dump column defaults for a particular class ddl_export(name, uri) ddl_function(regproc) - dump a particular function ddl_functions(name) - dump functions for a whole schema ddl_grants(regclass) - dump grants for a particular class ddl_grants(regclass, name) ddl_indexes(name) - dump indexes for a whole schema ddl_indexes(regclass) - dump indices on a particular class ddl_pgdump(name, name, text) - generate SQL dump by calling external program pg_dump. TO BE REMOVED. ddl_rules(name) - dump rules for a whole schema ddl_rules(regclass) - dump rules on a particular class ddl_schema(name) - dump schema creation ddl_table(regclass) ddl_tables(name) ddl_triggers(name) - dump triggers for a whole schema ddl_triggers(regclass) - dump triggers on a particular class ddl_type(regclass) - dump a type (composite or sequence) ddl_type(regtype) - dump a type (domain) ddl_types(name) - dump types for a whole schema ddl_view(regclass) ddl_views(name)
Not so important for individual objects. More important for whole schemas.
- by dependancy (difficult)
- by increasing oid
Some things (functions, defaults, null constraints) can be CREATE OR REPLACEd or ALTERed without problems.
Apparently the smart/hairy stuff about this is in pg_dump C code.
- Implemented for Postgres 7.4 (or later)
- DDL generator functions largely implemented in plpgsql and plperl(u), as a part of WDBI schema.
- No C code (this is a feature)
- external dumper implemented as unix command wdbi-dump, which is designed to be option-compatible with pg_dump
Friendly system views
Dumper depends on friendly system views (currently those in WDBI schema). Using pg_catalog views directly is somewhat difficult.
Perhaps INFORMATION_SCHEMA should be used, but it's not entirely sufficient at the moment (eg. no OID). SQL spec says INFORMATION_SCHEMA views can be extended by implementation specific columns, though...
Investigate: maybe newsysviews could be used instead; consider implementing the dumper as an add on to newsysviews.