What are alternatives formats to pg_dump for the purpose of diff-ing schema?

Question

We are storing pg_dump produced schema DDL in our version control.

We find that comparing pg_dump ouput before/ after migration scripts are applied gives a better sense of the changes that are being applied.

However, the problem that I am facing with pg_dump output is that related object changes are all over the file. Therefore, the moment you have a change that affects multiple objects in the schema, it becomes hard to see related changes.

I am wondering if there are any alternative formats that I could export database schemas simply for the purpose of diff-ing them, e.g. I am considering using psql to generate an ascii table representation of objects.

test=# \d+ question
                                                    Table "public.question"
    Column     |  Type   | Collation | Nullable |               Default                | Storage  | Stats target | Description
---------------+---------+-----------+----------+--------------------------------------+----------+--------------+-------------
 id            | integer |           | not null | nextval('question_id_seq'::regclass) | plain    |              |
 question      | text    |           | not null |                                      | extended |              |
 interest_id   | integer |           |          |                                      | plain    |              |
 slack_team_id | integer |           |          |                                      | plain    |              |
Indexes:
    "question_pkey" PRIMARY KEY, btree (id)
    "question_question_slack_team_id_idx" UNIQUE, btree (question, slack_team_id)
    "question_interest_id_idx" btree (interest_id)
    "question_slack_team_id_idx" btree (slack_team_id)
Foreign-key constraints:
    "question_interest_id_fkey" FOREIGN KEY (interest_id) REFERENCES interest(id) ON DELETE CASCADE
    "question_slack_team_id_fkey" FOREIGN KEY (slack_team_id) REFERENCES slack_team(id) ON DELETE CASCADE
Referenced by:
    TABLE "trivia_question" CONSTRAINT "trivia_question_question_id_fkey" FOREIGN KEY (question_id) REFERENCES question(id) ON DELETE CASCADE
Access method: heap

score 0 · Answer 1 · answered Aug 25 '20 at 23:07

Hardly an answer to my question, but I will document what I have found:

simply using \dt+ *.* to generate psql description of the entire database
https://www.schemacrawler.com/output.html generates diff friendly schema in plain text, HTML and JSON.
http://schemaspy.org/ - not what I was looking for, but discovered as a great way to document the current schema.
https://github.com/keithf4/pg_extractor – extracts each database object into its own file. This is what I ended up using.

I will update this as I discover better solutions.

What are alternatives formats to pg_dump for the purpose of diff-ing schema?

1 Answers1