How to Compare Multiple Databases From the Command Line

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

1. Open any third-party editor, for example, Notepad.
2. 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.

   For example:

   Target_server1, Target_DB_name1
   
   Target_server2, Target_DB_name2
   
   Target_server3, Target_DB_name3

Step 2. Creating a .bat file

1. Open any third-party text editor, for example, Notepad++.
2. 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:

• Port
• 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.

Conclusion

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.