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
Hardly an answer to my question, but I will document what I have found:
\dt+ *.*
to generate psql description of the entire databaseI will update this as I discover better solutions.