When one works on a changes of the database schema for an application, sometimes it is necessary to understand the difference between the old and the new structure. This information can be analyzed to check whether the changes might have any undesired impact on other applications or used in documentation.
The differences can be found using conventional command line utilities.
For example, suppose one changed the structure of the car portal database.
First, let's create another database that will contain the updated schema. Assuming that the user can access the database running on localhost and is allowed to create databases and connect to any databases, the following command will create a new database using the old one as a template:
user@host:~$ createdb -h localhost car_portal_new -T car_portal -O car_portal_app
Now there are two identical databases. Connect to the new one and deploy the changes in the schema to the new database:
user@host:~$ psql -h localhost car_portal_new
psql (10.0)
Type "help" for help.
car_portal_new=# ALTER TABLE car_portal_app.car ADD insert_date timestamp with time zone DEFAULT now();
ALTER TABLE
Now the structure of these two databases is different. To find the difference, dump the schema of both databases into files:
user@host:~$ pg_dump -h localhost -s car_portal > old_db.sql
user@host:~$ pg_dump -h localhost -s car_portal_new > new_db.sql
The files old_db.sql and new_db.sql that would be created after executing the preceding commands are available in the attached media. It is easy to compare these files using conventional utilities. On Linux, it can be done with the command, diff:
user@host:~$ diff -U 7 old_db.sql new_db.sql
--- old_db.sql 2017-09-25 21:34:39.217018000 +0200
+++ new_db.sql 2017-09-25 21:34:46.649018000 +0200
@@ -351,15 +351,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 NOT NULL,
- mileage integer
+ mileage integer,
+ insert_date timestamp with time zone DEFAULT now()
);
On Windows, this can be done with the command, fc:
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 NOT NULL,
mileage integer
);
***** NEW_DB.SQL
car_model_id integer NOT NULL,
mileage integer,
insert_date timestamp with time zone DEFAULT now()
);
*****
Both make it visible that one line of the schema dump has changed and another one was added. There are a lot of more convenient ways to compare files; for example, using 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 commercial products that can do this; for example, EMS DB Comparer for PostgreSQL.