LiveCompare¶
LiveCompare is designed to compare any number of databases to verify they are identical. The tool compares any number of databases and generates a comparison report, a list of differences and handy DML scripts so the user can optionally apply the DML and fix the inconsistencies in any of the databases.
By default, the comparison set will include all tables in the database. LiveCompare allows checking of multiple tables concurrently (multiple worker processes) and is highly configurable to allow checking just a few tables or just a section of rows within a table.
Each database comparison is called a “comparison session”. When the program starts for the first time, it will start a new session and start comparing table by table. In standalone mode, once all tables are compared, the program stops and generates all reports. LiveCompare can be stopped and started without losing context information, so it can be run at convenient times.
Each table comparison operation is called a “comparison round”. If the table is too big, LiveCompare will split the table into multiple comparison rounds that will also be executed in parallel, alongside with other tables that are being carried on by other workers at the same time.
In standalone mode, the initial comparison round for a table starts from
the beginning of the table (oldest existing PK) to the end of the table
(newest existing PK). New rows inserted after the round started are
ignored. LiveCompare will sort the PK columns in order to get min and
max PK from each table. For each PK column which is unsortable,
LiveCompare will cast it’s content to string . In PostgreSQL that’s
achieved by using ::text and in Oracle by using to_char .
When executing the comparison algorithm, each worker requires N+1 database connections, being N the number of databases being compared. The extra required connection is to an output/reporting database, where the program cache is kept too, so the user is able to stop/resume a comparison session.
Any differences found by the comparison algorithm can be manually re-checked by the user at a later convenient time. This is recommended to be done to allow a replication consistency check. Upon the difference re-check, maybe replication caught up on that specific row and the difference does not exist anymore, so the difference is removed, otherwise it is marked as permanent.
At the end of the execution the program generates a DML script so the user can review it, and fix differences one by one, or simply apply the entire DML script so all permanent differences are fixed.
LiveCompare can be potentially used to ensure logical data integrity at row-level; for example, for these scenarios:
Database technology migration (Oracle x Postgres);
Server migration or upgrade (old server x new server);
Physical replication (primary x standby);
After failover incidents, for example to compare the new primary data against the old, isolated primary data;
In case of an unexpected split-brain situation after a failover. If the old primary was not properly fenced and the application wrote data into it, it is possible to use LiveCompare to know exactly which data is present in the old primary and is not present in the new primary. If desired, the DBA can use the DML script that LiveCompare generates to apply those data into the new primary;
Logical replication. Three kind of logical replication technologies are supported: Postgres native logical replication, pglogical and EDB Postgres Distributed (PGD, formerly known as BDR).
