How to Use It

It depends on your way of work, your needs etc how you will exactly "plug" apgdiff into your development cycle. This page is here just to show you the basic general usage schema.

Create SQL Dump Files

apgdiff compares SQL dump files of original database schema (might be your production server database schema) and new database schema (most probably will be your development database schema) to which you want to upgrade the original database schema. So lets say our production database is at prod.server.com, our development database is at dev.server.com and the database is named "cool-app-db" on both servers. To get the schema dumps, we have to issue following commands:

pg_dump -h prod.server.com -s -f original.sql cool-app-db
pg_dump -h dev.server.com -s -f new.sql cool-app-db

After this step, we have two files, original.sql containing schema of our production database, and new.sql containing schema of our development database.

Generate Upgrade Statements

Now, to get our schema upgrade statements that we could use on our production database, we must tell apgdiff to compare our two files and give us the statements we can use for production database schema upgrade. We do that using following command (--ignore-start-with is used here to ignore START WITH parameters of SEQUENCEs as we do not want to diff those):

java -jar apgdiff-2.4.jar --ignore-start-with original.sql new.sql

This command will tell apgdiff to compare the two schema dumps and to give us the upgrade statements. As we did not forward the output to a file, it will be displayed on screen. If we would want to store the output in a file, the command would look like this:

java -jar apgdiff-2.4.jar --ignore-start-with original.sql new.sql > upgrade.sql

The output of apgdiff is sequence of statements that will transform our production database to the same state our development database is at. But before you run those statements on your production server, you should go through the file, check whether it contains what you expect, possibly test it on another database server with copy of production server database (or just copy of production database schema) so you are sure everything goes well while doing schema upgrade on production database.

Upgrading Production Schema

When you are ready to upgrade your production database schema, run this command:

psql -h prod.server.com -f upgrade.sql cool-app-db

After running this command, your production database schema will be transformed to the same state as your development database schema was when you dump'd your new.sql file.

More Information

That's all to say here. You can find more information at my blog in article Upgrading PostgreSQL Database Schemas and on Links page.

Download
Another PostgreSQL Diff Tool
Version 2.4