Welcome to Another PostgreSQL Diff Tool

Another PostgreSQL Diff Tool is simple PostgreSQL diff tool that is useful for schema upgrades. The tool compares two schema dump files and creates output file that is (after some hand-made modifications) suitable for upgrade of old schema.

I created this tool because I need to perform PostgreSQL database schema upgrades for our application. I didn't found anything suitable so I created this simple tool. I decided to place it here on SourceForge.Net because it might be useful for other developers too. If you find the tool useful too please let me know and I may create a Used By section here at the site.

Installation

You can use the binary distribution from download page. Just unpack it on your disk and run it using:

java -jar apgdiff.jar

apgdiff is also available as installable package on some Linux distributions:

  • Debian
  • Gentoo
  • Ubuntu

(If I missed some distribution then please let me know.)

How the Program Works

apgdiff is a command-line tool. It takes two parameters. One is the original database schema dump file, the other is the new database schema dump file. The command might look like this:

java -jar apgdiff.jar original_schema_dump.sql new_schema_dump.sql

After you submit the command, you will see list of SQL commands and modification information on the screen. You can also redirect the output to a file:

java -jar apgdiff.jar original_schema_dump.sql new_schema_dump.sql > diff.sql

For more information run apgdiff without parameters:

java -jar apgdiff.jar

List of Features

  • apgdiff looks for differences in definitions of TABLEs, SEQUENCEs, CONSTRAINTs, INDEXes, VIEWS, FUNCTIONs and TRIGGERs
  • apgdiff creates SQL commands for creations, drops and modifications of database objects
  • CREATE SCHEMA, CREATE TABLE, ALTER TABLE (except ALTER TABLE ... OWNER TO ...), CREATE SEQUENCE, CREATE INDEX, CREATE (OR REPLACE )VIEW, CREATE (OR REPLACE )FUNCTION, CREATE TRIGGER and SET search_path commands are supported but some specific features of the commands may not be implemented yet (see Note below)
  • Comments, empty lines, SET (except SET search_path), COMMENT, SELECT, INSERT INTO, REVOKE, GRANT and possibly other commands are ignored

Note: apgdiff is no more depending on formatting of SQL commands so it can be used on dumps from different version of PostgreSQL, on SQL commands from pgAdmin, hand-written SQL commands etc. I do the testing on dumps from PostgreSQL 8.1.x. Please note that there are some differences in what parameters are default in what versions of PostgreSQL (for example creation of OIDS) so have this in mind while creating your diffs as apgdiff defaults to the defaults of PostgreSQL 8.x.

Requirements

apgdiff is a Java program so you need Java installed. apgdiff requires at least Java 1.5. I use "Sun JDK 1.6.0".

Bugs

There are two kinds of bugs in apgdiff:

  • bugs for currently supported features (they should be rare because they should be caught by automated tests)
  • bugs caused by not implemented features

No matter of what bug you came across, please file it to Bugs Tracker. If you have a patch for such a bug then please file it to Patches.

Some Links

Here are some links mentioning apgdiff i found on internet:

Future

As I use this tool quite often I will probably release new versions from time to time. If you are interested in this tool and have a patch for it, put it to Patch Tracker and I'll add it to a new release. Also, if you would like to see a new feature in apgdiff then you can submit Feature Request.

Contact

You can contact me at miroslav.sulc@startnet.cz.