pg_dump without comments on objects?

Question

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 ?

Answer 1

Use the --no-comments option.

Example:

$ pg_dump --no-comments [database] > dump.sql

References: https://www.postgresql.org/docs/12/app-pgdump.html

Answer 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