CODEWITHSUNDEEP
postgresqlpg-dump
Sébastien Clément
Sébastien Clément

Reputation: 600

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 ?

Upvotes: 5

Views: 5447

Answers (3)

Davide Muzzarelli
Davide Muzzarelli

Reputation: 989

Use the --no-comments option.

Example:

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

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

Upvotes: 5

MatheusOl
MatheusOl

Reputation: 11845

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

Upvotes: 3

Chris Travers
Chris Travers

Reputation: 26464

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.

Upvotes: 2

Related Questions