Diffing PostgreSQL Schema Changes

One of the routine operations when administering PostgreSQL is periodically updating the database system’s architecture. PostgreSQL does a good job of allowing one to update a schema, add types, functions, or triggers, alter a table by adding and removing columns, update column data types, and perform related operations in a reliable manner. However, there is no built-in mechanism to identify differences, let alone generate the necessary SQL, to easily accomplish updates from the development environment to the production environment.

So let’s talk about possible approaches to schema changes.

Using Logical Dump Manifests

The easiest way to identify changes between schemas from one database to another is to compare schema dump manifests.

The following example demonstrates an approach one can take when looking for differences between schemas on different databases.

Example:

This snippet demonstrates looking for differences by comparing the MD5 checksums:

This next snippet diffs the differences between the two manifests, identifying only the objects and attributes that have changed. Notice that redundant information, the first 16 lines, is skipped:

This resultant diff shows the changes made between the two schemas:

The good news is that there are a number of existing tools that can reconcile differences between a proposed schema design and the target schema:

1. Commercial offerings can differentiate schemas between databases in an elegant and efficient manner. Researching available options will reveal the most popular technologies one can use.
2. Regarding open source solutions, there are a number of projects capable of diffing PostgreSQL database schemas.

Working with the apgdiff Extension

The following is an example implementation of the open source tool apgdiff.

Apgdiff can be found in the Postgres community repository. It compares two schema dump files and creates an SQL output file that is, for the most part, suitable for upgrades of old schemata:

The following example demonstrates how one can update differences between the development environment and the production database schema using apgdiff.

Example:

There is more you can accomplish with these simple approaches. By incorporating variations of them, one can create fairly sophisticated shell scripts with little code and, with a little luck, not much effort.