pg_dump(all) library

Folks,

This subject keeps coming up, then back down, etc.

What would a libpgdump API look like?

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

* David Fetter (david(at)fetter(dot)org) wrote:
> This subject keeps coming up, then back down, etc.
>
> What would a libpgdump API look like?

Honestly, when I was thinking about the "-w" command and whatnot, my
first reaction was "gee, it'd be nice to be able to dump the schema
using a \copy schema or something from psql". And then "you know, some
other utilities that don't use psql might find it useful too.".

That also got me to thinking about the "pgscript" type of idea, and
about wildcards for commands, and being able to loop through objects in
a scriptable way that's not a really ugly combination of psql calls and
shell script...

Dunno if that helps much, just trying to get out what was going through
my head.

Thanks,

Stephen

David Fetter <david(at)fetter(dot)org> writes:
> What would a libpgdump API look like?

Hmm. Start with requirements:

* Ability to enumerate the objects in a database

* Ability to fetch the "properties" of individual objects
(SQL definition is only one property, eg. pg_dump considers
owner, schema, ACL separately from the CREATE command)

* Ability to identify an appropriate dump order (and perhaps
lower-level manipulations of dependency info, not sure what
might be needed)

* Ability to work with different server versions (not sure how
much that impacts the API, but it's definitely something to keep
in mind while designing)

What else?

regards, tom lane

Stephen Frost wrote:
> * David Fetter (david(at)fetter(dot)org) wrote:
>> This subject keeps coming up, then back down, etc.

> That also got me to thinking about the "pgscript" type of idea, and
> about wildcards for commands, and being able to loop through objects in
> a scriptable way that's not a really ugly combination of psql calls and
> shell script...
>
> Dunno if that helps much, just trying to get out what was going through
> my head.

It seems to be that quite a bit of pg_dumps functionality could be
pushed into PostgreSQL as functions. This would end up defining an API
on its own.

pg_dump the executable would just be a shell that calls the functions in
appropriate order. This would also assist in the removal of the should
be defunct years ago pg_dumpall because pg_dump -A would just connect to
database in sequence (or, all at once if we like using multiple
connections).

Consider:

pg_dump_schema(ARRAY,TEXT,TEXT)

SELECT pg_dump_schema('{public,foobar}','BINARY','/tmp/foo.sql');

pg_dump_data(ARRAY,TEXT,TEXT)

SELECT pg_dump_data('{public.foo,foobar.baz}','BINARY','/tmp/mydata.sql');

pg_dump_types
pg_dump_tables - only dumps table structures no indexes or constraints
pg_dump_primary_keys
pg_dump_indexes
pg_dump_constraints

etc...

There could be a problem with the whole use the new pg_dump to dump the
old database.

Sincerely,

Joshua D. Drake

>
> Thanks,
>
> Stephen

Joshua D. Drake wrote:
>
> It seems to be that quite a bit of pg_dumps functionality could be
> pushed into PostgreSQL as functions. This would end up defining an API
> on its own.
>
> pg_dump the executable would just be a shell that calls the functions
> in appropriate order.
[snip]
> There could be a problem with the whole use the new pg_dump to dump
> the old database.
>
>

Indeed. This kills it.

pg_dump is simply essential as an upgrade mechanism.

The client library approach that has been discussed on and off for years
seems by far the best approach.

cheers

andrew