pg_dump without comments on objects?

问题:

Is there a way to perform a pg_dump and exclude the COMMENT ON for tables/views and columns ?

I use extensively the COMMENT ON command to describe all objects, and often include newlines in them for clearer descriptions, e.g.:

COMMENT ON TABLE mytable1 IS 'MAIN TABLE...
NOTES:
1. ...
2. ...
3. ...
';

However, since there are newlines in the dump as well, I cannot simply remove the comments with a grep -v 'COMMENT ON' command.

Any other way to quickly remove these COMMENT ON from the dump ?

回答1:

I would actually do this with a two-stage dump and restore.

1. Dump and restore the db as is or create a new db from the old one with createdb -T or CREATE DATABASE WITH TEMPLATE

2. Run the following command

   Delete from pg_description;
   

3. Dump and restore that database. That will ensure you don't have any annoying dependencies floating around.

回答2:

AFAIK, neither pg_dump nor pg_restore have options to remove COMMENTs. But, if you use a binary dump format like:

 $ pg_dump -Fc <your connection> -f /path/to/backup.dump

you could extract the TOC entry and edit it:

 $ pg_restore -l -f /path/to/backup.toc /path/to/backup.dump

The above will extract a TOC file and save it at /path/to/backup.toc, then you could find each line with COMMENT entry and remove or comment it. If you don't use strange names on your objects, a simple sed would solve the problem, to comment the lines with COMMENTs you could do this (a semicolon starts a comment):

$ sed -i 's/^\(.* COMMENT .*\)/;\1/g' bar.toc

With this new TOC file, you can now use pg_restore to restore your dump (with -L option):

$ pg_restore -L /path/to/backup.toc -d <your database> /path/to/backup.dump