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

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

In response to

Responses

Browse pgsql-general by date

  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