v0.9.2 Summary Chapter 5: Export, import and diff 5. Introduction 5.3. Compare a model and a database

5.3. Compare a model and a database

The comparison or diff process in pgModeler consists in generate a set of SQL commands that will make both model and database synchronized. In most recent versions of the tool there's a possibility to compare two databases as well. In details, the comparison is made from model to database, meaning that any object existent in model and absent in the database will be created. In the same analogy, any object absent in the model and present in the database will be destroyed in the latter. The key idea here is that the model is the main reference meaning that any change made to the database that is not in the former is completely ignored. This is useful to keep the model always up to date because the user need to alter first that one and then apply the changes to the database.

The process itself is quite simple, first pgModeler will generate a temporary model based upon the database to be compared using the reverse engineering feature. Once generated the model it's time to compare the objects from the input model (the reference) and the ones present in the temporary one. Any detected difference is stored and processed later resulting in a set of SQL commands that may create new objects, destroy old ones or just alter the structure of the current ones. When the comparison finishes, the commands are ready to be applied to the database. In this moment, depending upon the settings done by the user, the generated commands will be saved in a file or exported directly to the server. In case of exporting to the server, the user will be prompted to proceed with the irreversible changes to be done to the database. Before confirm the modifications it is highly recommended to make a backup of the database in case of something goes wrong.

Finally, the model-­database diff dialog has a long set of options and they may affect how the resulting commands are generated as well their semantics. You seldom need to change their values but in some few cases the default settings can't generate the diff as expected so you'll need to tweak them to achieve the desired result. Below all available options are fully described. This feature is still in experimental state and may produce false-positive results or extra/redundant code in the final code, but these issues are being solved little by little.

Source database

Option/Field Description
Current model The working database model used as input of the diff process.
Database If you intend to compare two databases instead of a model and a database you can select this option and choose a connection on the field Connectionto browse the desired database. Once connected to the server the combo box Database will list all available databases to be used in the comparison. Note that the user configured in the selected connection must have the needed permissions to query the system catalogs and manage objects or the process will certainly fail.

Compare to

Option/Field Description
Connection Previous configured connection used as communication channel to the server where to look for the database compared against the input model or database. This connection also needes to be configured with a user with the needed permissions to query the system catalogs and manage objects.
Database The database selected here is the one that will be compared to the model or database selected in the Source database field.

Diff mode

Option/Field Description
Use PostgreSQL Override the PostgreSQL version when generating the diff code. The default is to use the same version as the input database (detected automatically).
Store in SQL file Compares the model and the input database storing the diff in a SQL file for later usage.
Apply on server Compares the model and the input database generating a diff and applying it directly to the latter. This mode causes irreversible changes on the database and in case of failure the original structure is not restored, so make sure to have a backup before proceed.

Diff options

Option/Field Description
Keep cluster objects Database cluster level objects like roles and tablespaces will not be dropped.
Keep object's permissions Permissions already set on database objects will be kept. The ones configured on the model will be applied to the database.
Do not drop missing objects Avoid the generation of DROP commands for objects that exist in database but not in the model. This is useful when diff a partial model against the complete database.
Drop missing columns and constraints Force the generation of DROP commands for columns and constraints that exist in database but not in the model. This is useful when diff a partial model against the complete database and the user needs to drop columns and constraints but preserves the rest of the objects.
Preserve database name No command to rename the destination database will be generated even the model's name differ from database name.
Drop or truncate in cascade mode For DROP command, the objects that depend on an object to be dropped will be deleted as well. For TRUNCATE command, tables that are linked to a table to be truncated will be truncate too. This option can affect more objects than listed in the output or diff preview.
Truncate tables before alter columns Clears the data of all tables which will have columns modified. This is useful to avoid errors related to type casting. Do not use this option on production servers and always make a backup before use it.
Reuse sequences on serial columns Serial columns are converted to integer and having the default value changed to nextval(sequence) function call. By default, a new sequence is created for each serial column but checking this option sequences matching the name on column's default value will be reused and will not be dropped.
Force recreation of objects Instead of use an ALTER command to modify certain kind of objects a DROP and CREATE will be used in order to do a full modification. This option does not affect the database object.
Recreate only unmodifiable objects Recreates only objects that can't be changed through ALTER commands according to pgModeler implementation not the PostgreSQL one. Currently, those objects are: aggregate, cast, constraint, collation, conversion, language, operator, operator class, operator family, rule, trigger and view.

The options available in the tab Import & Export are the same ones used by the import and export processes so the details about them can be seen in the previous sections in this chapter. After configure the desired options you can start a diff process by hitting the button Generate. The whole process can take a long time due to the amount of the comparisons done and the size of the involved model and database. Unlike the import, the diff dialog will not be closed automatically at the end of the process this because the user can compare other databases to the model using the current settings.

Jan 3, 2020 at 12:33