Is there a good way to individually list the DDL for all individual data
base objects?
Running a data base dump like:
pg_dump mydatabase > mydatabase-database.sql
produces one big file with all the DDL and DML to restore the data base,
which is very convenient for most cases.
Using that I have sometimes cut-and-pasted out of it the DDL for
individual items that I want to work on, and then fed that back in with
psql mydatabase < newstuff.sql
What I would like, though, is a set of output files, one for each
DDL/DML item currently represented in the one big file.
I could maybe attempt some convoluted automated parsing of the big file
with, for example, Perl, but that seems, well, convoluted, error-prone,
and probably fragile.
The directory dump output option for pg_dump is similar to, but not
really, what I want (as far as I see the individual files that produces
are only the ones for reloading data, so correct me if I am wrong ...
and please show me how to do it right!)
I have played around with the custom format dump followed by pg_restore
and various options, but did not get what I wanted, at least not as
elegantly as I wanted.
What I have come up with is fairly simple in appearance, but the way it
works, by reading one line-at-a-time from the list file associated with
the dump file, and then running pg_restore with just that one line,
rinse and repeat for each piece of DDL/DML, also seems convoluted and
potentially fragile.
Something along the lines of (... if anyone thinks this a good idea, or
good starting point ...):
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.
Thanks for your help!
---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com
