16

Consider the following setting:

  • A production DB
  • A dev db, on which schema changes are made to enable new features

When the development of a new feature is complete, I have to manually update the prod db schema until pg_dump --schema-only on both DBs is identical. This process is error prone and tedious.

So, I am looking for a tool that can:

  • Show a summary of the differences between two schemas (like diff). Note that I am not looking for a mere textual diff of the schema, but a more elaborate tool that can draw conclusions like "Table X has a new column Y".
  • Auto-generate the SQL code that would convert one schema to another (like patch)

Is there a schema diff/patch tool that can help me convert prod schemas to the more advanced dev schemas?

4
  • 2
    Rather than doing a diff, you should manage your migration scripts in a controlled way. Never do ad-hoc DDL changes to a DBMS, always put the change into a script (which is stored in a version control system) and then apply the script. Look into tools like Liquibase or Flyway Aug 13 '14 at 6:27
  • 1
    @a_horse_with_no_name Thanks. Even with this approach, a diff/patch tool would make my life easier. BTW, I can't help humming the song.
    – Adam Matan
    Aug 13 '14 at 6:46
  • You might try pg_comparator: pgfoundry.org/projects/pg-comparator (I have never used it though). Liquibase has a built-in diff as well and emits the results as a Liquibase changeset if I'm not mistaken so that might be a good starting point for more a controlled schema management Aug 13 '14 at 6:51
  • Thanks. Care to post it as an answer so I can upvote?
    – Adam Matan
    Aug 13 '14 at 6:52
12

Sorry to resurrect an old question

Recently I have been using the 0xDBE DataGrip Database management tool by JetBrains.

It supports multiple database engines, in the excellent Jetbrains IDE, and a key feature I have found useful is the ability to diff 2 tables (DEV and PROD).

Below is a screenshot of the diff in action (in this case there is only one column difference). The screenshot is the result of the "Merge Right" button at the top, generating the SQL required to bring the right table up to scratch.

0xDBE SQL Table Diff

Hope this new tool helps.

6
  • 3
    There is no problem with resurrecting old questions (there are even badges to achieve from that). And a question: is it possible to compare whole databases (I mean, at least, all tables in them)?
    – dezso
    Nov 3 '14 at 9:13
  • @dezso - Thanks for the reassurance. Yes you can compare at a database, schema and table level.
    – Ewan
    Nov 3 '14 at 10:42
  • 1
    @BasilBourque - From the left hand Database menu, select the 2 tables you want to compare (with cmd/ctrl + click), right click and select Compare
    – Ewan
    Sep 9 '15 at 6:42
  • 3
    I've recently came across a new tool, written in python, migra. It can track changes to changes to tables, views, functions, indexes, constraints, enums, sequences, and installed extensions and can also be used as a library
    – scripts
    Aug 12 '16 at 1:48
  • 1
    @Ewan Also, migra tracks everything in both databases, including triggers and functions. But DataGrip doesn't. That's why, I discourage from using DataGrip as a diff tool. Drawbacks are also present: 1) migra is only a PostgreSQL tool; 2) migra does track changes, but doesn't always produce appropriate DDL operators, like alter table... add column, drop column instead of alter table... rename column etc. And I haven't found any tool, that produces correct DDL commands. They always need polishing. Also, without some migration tool, like Liquibase, any database modifications will be too complex. Jun 27 '19 at 15:10
6

Use liquibase.

It supports diff, generating a db from scratch, patching a db, rolling back a db, and a bunch of other stuff.

You used to have to write everything in XML with liquibase, but not anymore. You can write 99% of it in the SQL dialect of your choosing. Example:

--liquibase formatted sql

--changeset neil:1 

create table contacts(
  contact_id serial primary key,
  name text not null unique
);

--changeset neil:2
alter table contacts add column phone_num text;

You should keep your liquibase changelogs in git or what-have-you.

2
  • An issue with this is that it doesn't get the ordering or removing constraints and primary keys right.
    – monksy
    May 7 '15 at 2:34
  • 2
    Flyway is another choice similar to Liquibase. Sep 9 '15 at 0:10

Not the answer you're looking for? Browse other questions tagged or ask your own question.