When one works on a new version of the database schema for an application, sometimes, it is necessary to understand the difference between the old and new structure. This information is used in release notes, and it can also be analyzed to check if the changes might have any undesired impact on other applications.

The differences can be found using conventional command-line utilities.

For example, suppose one changed the structure of the car portal database, which is used as a sample database in this book. The script to create the database is in the addendum. To learn how to create a database and how to use the psql console and other PostgreSQL utilities, please refer to the previous chapters.

First, let's create another database which will contain the updated schema:

user@host:~$ createdb car_portal_new -T car_portal -O car_portal_app

Now there are two identical databases. Then, deploy the changes in the schema to the new database:

user@host:~$ psql car_portal_new
psql (9.4.0)
Type "help" for help.
car_portal_new=# ALTER TABLE car_portal_app.car ADD insert_date timestamp with time zone DEFAULT now();

Now the structure of those two databases is different. To find the difference, let's dump the schema into the files:

user@host:~$ pg_dump -s car_portal > old_db.sql
user@host:~$ pg_dump -s car_portal_new > new_db.sql

Now, one can use any utility to compare the two text files. For example, the Linux diff utility will return the following output (the output is truncated):

user@host:~$ diff -U 7 old_db.sql new_db.sql
--- old_db.sql  2015-01-24 13:46:20.608109000 -0500
+++ new_db.sql  2015-01-24 13:46:28.312109000 -0500
@@ -251,15 +251,16 @@
 CREATE TABLE car (
     car_id integer NOT NULL,
     number_of_owners integer NOT NULL,
     registration_number text NOT NULL,
     manufacture_year integer NOT NULL,
     number_of_doors integer DEFAULT 5 NOT NULL,
     car_model_id integer,
-    mileage integer NOT NULL
+    mileage integer NOT NULL,
+    insert_date timestamp with time zone DEFAULT now()
 );

In Windows, similar results can be achieved using the fc command:

C:dbdumps> fc old_db.sql new_db.sql
Comparing files old_db.sql and NEW_DB.SQL
***** old_db.sql
    car_model_id integer,
    mileage integer NOT NULL
);
***** NEW_DB.SQL
    car_model_id integer,
    mileage integer NOT NULL,
    insert_date timestamp with time zone DEFAULT now()
);
*****

From both the outputs it is visible that one line was changed, and another line was added to the script.

Alternatively, this file comparison can be done using any of the commonly used text editors, like vim or Notepad++.

In many cases, it is not enough to just see the difference in the schema. It could also be necessary to synchronize the schema of the two databases. There are some commercial products that can do that, like EMS DB Comparer for PostgreSQL.

Moreover, there are many open source projects. For example, apgdiff, which is written in Java and available at http://www.apgdiff.com/index.php.

The output of apgdiff will be:

user@host:~$ java -jar apgdiff-2.4.jar old_db.sql new_db.sql

SET search_path = car_portal_app, pg_catalog;

ALTER TABLE car
        ADD COLUMN insert_date timestamp with time zone DEFAULT now();

It is an SQL script that can be used to migrate from the old data structure to the new one.