Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux