PostgreSQL diff Explained

Normal development flow requires continuous patching the production database with local changes normally made automatically by the orm software, this method is not perect but deceptively simple, all we'll use is standard Unix commands, and is good enough for us.

You will not see a database name or user name because we'll use the convention to use an operating system account owning the database in local and production and both databases local and remote are named as the user, all this with automatic ssh login in remote.

The diff command and pg_dump

The first aproach is to compare schemas, suppose you have your local dev version in localhost and the production version in other server called remote-server.

1. Obtain backup of db structure in production server

ssh remote-server pg_dump -s -Ox  > remote1.sql

Note: We obtain only the schema (-s) without any permissions and grant sql comands (-Ox).

2. Obtain backup of local database

pg_dump -s -Ox  > local.sql

3. Compare the two schemas

Nexts command compare the two files and filter the lines needed in remote to be like local using standard unix filters grep and sed .

diff remote2.sql local.sql  | grep '^>' | sed 's/^> //'  

Difference in database versions

If the Postgres versions are different, chances are that will be a lot of differences not essential in nature, but in syntax, i.e, schema prefix usages.

To be bullet proof we should make this comparison using the same version of postgres, for this you should create a local database with the remote schema and then make the comparison:

createdb temp1
psql -f remote1.sql temp1
pg_dump -s -Ox temp1  > remote2.sql

Now, compare again schemas same server version

diff remote2.sql local.sql 

Again to show the sql needed to patch remote to be like local use:

diff remote2.sql local.sql  | grep '^>' | sed 's/^> //'

The only catch is if there are only field differences the sql generated could be invalid, so check it out before proceed.

Finally, this script makes all

#!/bin/bash
# Compare a remote database with its local counterpart
if [ $# -ne 1 ]
then
        echo Please give remote server arg
        echo Usage: postgres-diff remote-server-addr
        exit -1
fi
SERVER=$1
TEMPDB="tempdb-$RANDOM"
REMOTE1="/tmp/r1-$RANDOM"
REMOTE2="/tmp/r2-$RANDOM"
LOCAL="/tmp/loc-$RANDOM"
echo Geeting schema from remote server
ssh $SERVER pg_dump -s -Ox  > $REMOTE1.sql
echo Geeting schema from local server
pg_dump -s -Ox  > $LOCAL.sql
echo Restoring remote in local
createdb $TEMPDB
psql -f $REMOTE1.sql $TEMPDB > /dev/null

echo Getting diffs, this SQL could be invalid
pg_dump -s -Ox $TEMPDB  > $REMOTE2.sql
diff $REMOTE2.sql $LOCAL.sql  | grep '^>' | sed 's/^> //'
dropdb $TEMPDB

This code is available in github: https://github.com/hanspoo/postgres-diff