CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.2
Marcio Mazzucato
Marcio Mazzucato

Reputation: 9305

Removing comments from pg_dump output

When a PostgreSQL pg_dump is done it inserts some comments for each element, as follows.

--
-- Name: my_table; Type: TABLE; Schema: account; Owner: user; Tablespace:
--

CREATE TABLE my_table(
    id integer
);

--
-- Name: my_seq; Type: SEQUENCE; Schema: account; Owner: user
--

CREATE SEQUENCE my_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

Is it possible to force pg_dump to remove (exclude) them? I would like to receive just:

CREATE TABLE my_table(
    id integer
);

CREATE SEQUENCE my_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

Upvotes: 16

Views: 9874

Answers (4)

Robins Tharakan
Robins Tharakan

Reputation: 2473

I've just submitted this patch for Postgres 11+ (still under consideration) that should allow one to dump without COMMENTS (until an ideal solution is in place), which should be a slightly better kludge than the ones we resort to using.

If there are enough voices, it may even get back-patched to Postgres 10!

[UPDATE]

This is now a feature in Postgres v11+

pg_dump --no-comments

Upvotes: 7

Laurenz Albe
Laurenz Albe

Reputation: 247270

On a UNIX type operating system, I would do it like this:

pg_dump [options] mydatabase | sed -e '/^--/d' >mydatabase.dmp

This could accidentally swallow data lines that start with --. To work around that problem, use the --inserts option of pg_dump.

Upvotes: 20

vitaly-t
vitaly-t

Reputation: 25890

The are only 2 good reasons for removing comments from SQL:

  1. The SQL file contains formatting variables (placeholders) that need to be replaced dynamically. In this case removing comments prevents false variable detection when those are referenced in comments.

  2. The SQL file is to be minimized, to reduce the size of what needs to go through IO and into the database server.

In either cases, it implies the SQL file is now meant only for execution, and not for reading.

And specifically for PostgreSQL, there is package pg-minify which does exactly that:

  • It removes all the comments and minimizes the resulting SQL
  • It can optionally compress the SQL to its bare minimum (option compress)

complete example

const minify = require('pg-minify');
const fs = require('fs');

fs.readFile('./sqlTest.sql', 'utf8', (err, data) => {
    if (err) {
        console.log(err);
    } else {
        console.log(minify(data));
    }
});

Upvotes: 2

Colin Beckingham
Colin Beckingham

Reputation: 525

Marcio, piping is the process of taking the output of one process and feeding it directly into another to achieve a specific purpose. Let's say you were using Julia to achieve the result you need. Create a test database and play with it to ensure that you get the desired result. This Julia command would produce a backup with comments:

run(pipeline(`pg_dump -d test`,"testdump.sql"))

Here Julia is asked to dump the backup into testdump.sql so that we can check the result. Note the backticks. Then comes another command which uses the filter suggested by @LaurenzAlbe:

run(pipeline(`cat testdump.sql`,`sed -e '/^--/d'`,"testdump2.sql"))

Here we have a three part pipeline which scans the backup with the comments, strips the comments out and dumps what remains into testdump2. You can now check that the first and second files are what is required.

Once you have confidence that the solution provided by @LaurenzAlbe is correct, you can make the required substitutions to run the entire thing in one pipeline command. Of course you can do the same thing directly in a bash terminal or Python or the scripting engine of your choice.

Upvotes: 1

Related Questions