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 ?

Liscomb

I don't think this is possible with pg_dump – Czarism

Possible solution - restore dump, find all coments and drop them, create new dump. You can find and drop all coments with dynamic SQL and metadata query. – Blinker

A smarter sed or perl script could do this, since the grammar of COMMENT ON is pretty simple. All you'd need to do is make sure you got the string escape parsing correct, ignoring doubled quotes for cases like COMMENT ON ... 'It''s alright'; – Almetaalmighty

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

Sycee

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

Versicle

This creating TOC and restoring with modified TOC was a really nice solution! Thanks! Didn't even need to use sed since TOC was modifiable via my editor – Wellmannered

Answer 3

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.

Munson