The Schema Diff Tool in pgAdmin 4 allows you to compare objects between two databases or two schemas. It allows you to compare tables, views, functions, sequences, packages, procedures and other database objects between two schemas/databases. It will report any discrepancies between schemas such as missing or mismatching procedures, tables, triggers, columns, indexes and constraints. It also will detect column discrepancies such as data type, nullability and defaults.
It saves time and effort when comparing and synchronizing database schemas on PostgreSQL server and EDB Postgres Advanced Server.
The Schema Diff Tool allows you to:
- Compare and synchronize the database objects (from source to target).
- Visualize the differences between database objects.
- List the differences in SQL statements for target database objects.
- Generate synchronization scripts.
How to access Schema Diff in pgAdmin 4
To access the schema diff tool click on Schema Diff under the Tools menu to open the selection panel.
Selecting Databases/Schemas
You can select the source and target servers of the same major version, and databases/schemas to be compared. You can select any server listed under the browser tree whether it is connected or disconnected. If you select a server that is not connected then it will prompt you for the password before using the server if required.
Next, select the database or schema that will be compared. The database can be the same or different (and within the same server or from different servers).
Comparing Databases/Schemas
After you select servers, databases, or schemas, click on the Compare button to obtain the Comparison Result:
Filters in Schema Diff Tool
You can use filters to view the database objects based on the following comparison criteria:
- Identical – If the object is found in both databases with the same SQL statement, then the comparison result is identical.
- Different – If the object is found in both databases but has different SQL statements, then the comparison result is different.
- Source Only – If the object is found in source database only and not in target database, then the comparison result is source only.
- Target Only – If the object is found in the target database only and not in the source database, then the comparison result is target only.
DDL View of source object, target object and their difference
You can review the DDL statements of all the database objects to check for the differences in the SQL statements. Select any object and review the DDL statements in the DDL Comparison Panel at the bottom.
The DDL Comparison panel displays three columns:
- The first column displays the DDL statement of the object from the source database.
- The second column displays the DDL statement of the object from the target database.
- The third column displays the difference in the SQL statement of the target database object, expressed as SQL statements that will make the object on the target match the object on the source.
Generating the synchronization script
You can generate an SQL script to synchronize the target database with the source database. To generate the script, select the checkboxes of the database objects you wish to synchronize in the object comparison panel and then click on the *Generate Script* button in the upper-right hand corner.
A new Query Tool panel with the SQL script to be run on the target database will be opened for review, modification (if desired), and execution.
Conclusion
The Schema Diff Tool is a very useful database comparison tool to compare two databases or schemas and generate a synchronization script. Dependencies are automatically resolved when you select any object and the tool finds it is dependent on some other object, and that object or objects are automatically selected. It saves time and manual effort to compare two databases or schemas.