All geek questions in one place

Pg_dump without comments on objects?

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

I use the COMMENT ON command extensively to describe all objects and often include newline characters for clearer descriptions, for example:

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

However, since there are new lines in the dump, I cannot just delete comments with the grep -v 'COMMENT ON' command.

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

+4
source share
2 answers

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

  • Dump and restore db as is or create a new db from the old using createdb -T or CREATE DATABASE WITH TEMPLATE

  • Run the following command

     Delete from pg_description;
  • Dump and restore this database. This ensures that you don't have any annoying dependencies floating around.
+2
source

AFAIK, neither pg_dump nor pg_restore have options for deleting COMMENT s. But if you use a binary dump format, for example:

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

you can extract the entry in the TOC and edit it:

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

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

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

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

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

Source: https://habr.com/ru/post/1485462/

More articles:


All Articles