Re: How to individually list the DDL for all individual data base objects

Le 2014-11-24 à 10:14, Berend Tober <btober(at)broadstripe(dot)net> a écrit :

> Is there a good way to individually list the DDL for all individual data base objects?
>

<snip>

> grep -v '^;' listfile | while read a b c n
> do
> a=${a/;}
> echo $a > f
> pg_restore -L f -f outputdir/$a dumpfile
> done
>
> This, as it is, creates a set of files named according to the id number that pg_dump uses to identify each element. Ideally, I would like the files named after the schema+object it represents.

Were you aware that pg_restore can restore to STDOUT, and output DDL for only a single named object from a custom dump file?

$ pg_restore —help
…

-f, --file=FILENAME output file name
…
-t, --table=NAME restore named table
…

Such that you could run:

$ pg_restore -f public.mytable.sql -t public.mytable whatever.pgdump

Unfortunately, this does not respect dependencies and you may have issues. The --disable-triggers option can alleviate some of these problems. YMMV.

Hope that helps!
François