An overview of tools for comparing data in PostgreSQL

Database administrators and developers often face situations where data from different databases need to be compared and synchronized, or simply transferred to another working base. In this case, it is very important to choose the right tool to help you cope with this task quickly and efficiently. For PostgreSQL, there are several off-the-shelf tools on the market that allow you to find differences and synchronize data. In this article we will conduct a brief overview of the features of these tools, namely the products of such companies as Devart, SQL Maestro Group, Navicat and Altova.

After installation, when you first start, all products require a license key, but everyone also has a trial period. The SQL Maestro Group and Navicat have 15 days, while the dbForge and Altova have 30 days. And, if in all products the transition to the trial period occurs at the touch of a button, then Altova suggests using their automatic service to get the keys for each product. After that, the mail comes a list of keys for the requested products with the expiration date of their actions.

Each product is quite different from each other in terms of interface and functionality. Therefore, we give a brief overview of applications for first impressions:

1. PostgreSQL DataSync - the application immediately starts the two database comparison wizard, thereby indicating that this application is intended only for data comparison and nothing more. Also in the wizard there is a key Generate command line ..., which allows you to save the script for later use in automating processes.
2. dbForge Data Compare for PostgreSQL - after starting the application, the Start Page window opens, which may contain a list of previous projects. Also, judging by the toolbar, you can call not only the data synchronization wizard, but also the usual SQL window of the document to work with scripts with the standard set of tools, as well as the option to run large scripts directly from the file. To work on data synchronization, the New Data Comparison wizard also opens for entering connections and database names. Also in the wizard there is a button Save Command Line ..., allowing you to save comparison scripts for future use.
3. Navicat for PostgreSQL - after launch opens a studio with a set of tools for working with databases. Those. It offers not only the option of data synchronization, but also the synchronization of schemes and objects. For data comparison, the Data Synchronization wizard is also launched, in which it is proposed to select two connections to the databases and for some reason only one scheme from each database. Thus, to compare several schemes, you need to start synchronization for each scheme separately.
4. Altova DatabaseSpy - after launch, an application opens in which you can choose from the drop-down menu the options for comparing files, directories, data, etc. The proximity of such applications in one list was a little embarrassing, and an attempt to make a simple comparison of the two bases did not lead to any satisfactory result because of the rather peculiar interface. Therefore, unfortunately, we have excluded this application from further comparison.

When comparing simple data, all three applications proved to be quite good, only Navicat stood out with its comparison for each scheme separately. dbForge was pleased with the opportunity to introduce additional user scripts for each pair of comparisons.
When working with complex data has already appeared differences. When synchronizing data with type conversion, all applications display a warning about possible data loss. But, at least on our test data, after synchronization, DataSync and Navicat continue to show that the data in the tables are not equal, but they do not output anything to the synchronization script. After synchronization by the dbForge application during subsequent comparison, the application itself indicates that the data in both databases are equal and additional synchronization is not required. DataSync and Navicat have confirmed that after synchronization with the dbForge Data Compare application for PostgreSQL, all data are equal.

All applications support PostgreSQL up to version 9.6 inclusive. Navicat still allows you to work with databases on Amazon, and dbForge also supports Azure.

When comparing applications, you should pay attention to their prices. The table also added tools that we did not include in the review.

Prices are per user with 1 year of support.

Summarizing, we can say that each tool has both advantages and disadvantages. Which tool to choose depends on your preferences. Based on all the above criteria, dbForge Data Compare for PostgreSQL was selected, which "perfectly" copes with the goals set, while having a low cost.