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?

Improve this question
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 Commented Aug 13, 2014 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. Commented Aug 13, 2014 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 Commented Aug 13, 2014 at 6:51
  • Thanks. Care to post it as an answer so I can upvote? Commented Aug 13, 2014 at 6:52

2 Answers 2

Reset to default
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.

Improve this answer
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)? Commented Nov 3, 2014 at 9:13
  • @dezso - Thanks for the reassurance. Yes you can compare at a database, schema and table level. Commented Nov 3, 2014 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 Commented Sep 9, 2015 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 Commented Aug 12, 2016 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. Commented Jun 27, 2019 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.

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.