LiveCompare

© Copyright EnterpriseDB UK Limited 2019-2021 - All rights reserved.

Introduction

LiveCompare is designed to compare any number of databases to verify they areidentical. The tool compares any number databases and generates a comparisonreport, a list of differences and handy DML scripts so the user can optionallyapply 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 workerprocesses) and is highly configurable to allow checking just a few tables orjust a section of rows within a table.

Each database comparison is called a “comparison session”. When the programstarts for the first time, it will start a new session and start comparing tableby table. In standalone mode, once all tables are compared, the program stopsand generates all reports. LiveCompare can be stopped and started without losingcontext information, so it can be run at convenient times.

Each table comparison operation is called a “comparison round”. If the table istoo big, LiveCompare will split the table into multiple comparison rounds thatwill also be executed in parallel, alongside with other tables that are beingcarried on by other workers at the same time.

In standalone mode, the initial comparison round for a table starts from thebeginning of the table (oldest existing PK) to the end of the table (newestexisting PK). New rows inserted after the round started are ignored. LiveComparewill sort the PK columns in order to get min and max PK from each table. For eachPK column which is unsortable, LiveCompare will cast it’s content to string. InPostgreSQL that’s achieved by using ::text and in Oracle by using to_char.

When executing the comparison algorithm, each worker requires N+1 databaseconnections, being N the number of databases being compared. The extra requiredconnection is to an output/reporting database, where the program cache is kepttoo, so the user is able to stop/resume a comparison session.

Any differences found by the comparison algorithm can be manually re-checked bythe user at a later convenient time. This is recommended to be done to allow areplication consistency check. Upon the difference re-check, maybe replicationcaught up on that specific row and the difference does not exist anymore, so thedifference is removed, otherwise it is marked as permanent.

At the end of the execution the program generates a DML script so the user canreview it, and fix differences one by one, or simply apply the entire DML scriptso all permanent differences are fixed.

LiveCompare can be potentially used to ensure logical data integrity atrow-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 BDR.

Comparison Performance

LiveCompare has been optimized for use on production systems and has variousparameters for tuning, described later. Comparison rounds are read-onlyworkloads. An example use case compared 43,109,165 rows in 6 tables in 9m 17swith 4 connections and 4 workers, giving comparison performance of approximately77k rows per second, or 1 billion rows in <4 hours.

The use case above can be considered a general use case. For low-load, testing,migration and other specific scenarios, it might be possible to improve speed bychanging the data_fetch_mode setting to use server-side cursors. Each kind ofserver side cursors, in our experiments, provides an increase in performance onuse cases involving either small or large tables.

Security Considerations for the User

When logical_replication_mode = bdr, LiveCompare requires a user that has beengranted the bdr_superuser role. When logical_replication_mode = pglogical,LiveCompare requires a user that has been granted the pglogical_superuserrole.

To apply the DML scripts in BDR, then all divergent connections (potentially alldata connections) require a user that has been granted the bdr_superuser inorder to disable bdr.xact_replication.

If BDR is being used, LiveCompare will associate all fixed rows with areplication origin called bdr_local_only_origin. LiveCompare will also applythe DML with the transaction datetime far in the past, so if there are any BDRconflicts with real DML being executed on the database, LiveCompare DML alwaysloses the conflict.

With the default setting of difference_fix_start_query, the transaction inapply scripts will change role to the owner of the table in order to preventdatabase users from gaining access to the role applying fixes by writingmalicious triggers. As a result the user for the divergent connection needs tohave ability to switch role to the table owner.