Very often, there is a need to compare or synchronize data between two databases on the same server. To detect differences, you can simply use dbForge Data Compare for PostgreSQL, which allows you to perform this process with a few clicks. However, what if we need to compare data of multiple target and source databases located on different servers?
This post describes the way to compare multiple databases on different servers through the command line.
Step 1. Creating a text file with the source and target databases and servers
- Open any third-party editor, for example, Notepad.
- Type a name of the source server and database, separated by a comma and click Save. Here you can write as many servers and databases as you need.
The example is as follows:
Source_server1, Source_DB_name1 Source_server2, Source_DB_name2 Source_server3, Source_DB_name3
You need to repeat this process with the target servers and databases by writing names separated by comma as well.
Target_server1, Target_DB_name1 Target_server2, Target_DB_name2 Target_server3, Target_DB_name3
Step 2. Creating a .bat file
- Open any third-party text editor, for example, Notepad++.
- Create .bat file with the following content, for example:
Set Compare="C:\Program Files\Devart\dbForge Data Compare for PostgreSQL\datacompare.com" FOR /F "eol=; tokens=1,2* delims=, " %%e in (Source_Databases.txt) do ( FOR /F "eol=; tokens=1,2* delims=, " %%g in (Target_Databases.txt) do ( %compare% /datacompare /source connection:"Connection Lifetime=120;Host=%%e;Port=5440;Database=%%f;User ID=postgres;Password=********;Pooling=False" target connection:"Connection Lifetime=120;Host=%%g;Port=5440;Database=%%h;User ID=postgres;Password=********;Pooling=False" /log:"D:\Multiple_Data_Compare.log" ) ) pause
D:\Multiple_Data_Compare.log is the file where the output result will be stored. If you wish, you can change the path to the log file, as well as to the .bat file.
If necessary, you can change the following details in the source and target connection:
- User ID and password
Note: Set Compare is a default installation path of dbForge Data Compare for PostgreSQL. However, if you have changed it, you will need to specify the correct path of the Data Compare file as well.
3. Save the script.
Step 3. Comparing source and target databases through the command line
Execute the .bat file through the command line.
As you can see, the .bat file provides you with the summary comparison results: whether the source and target databases are identical, how many different or conflicting records there are either in source or in target databases.
In addition, the output file will be generated after the successful completion of the process.
In this post, we have reviewed how to compare multiple databases located on different servers, by simply running a .bat file through the command line. As you can see, this simple process will not take much time and will be completed fast by just using a few button clicks.