From: | Berend Tober <btober(at)broadstripe(dot)net> |
---|---|
To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | How to individually list the DDL for all individual data base objects |
Date: | 2014-11-24 15:14:15 |
Message-ID: | 54734B47.7090001@computer.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-11-24 15:29:47 | Re: How to avoid a GIN recheck condition |
Previous Message | Igor Neyman | 2014-11-24 14:34:10 | Re: Avoiding deadlocks when performing bulk update and delete operations |