Advanced usage v2
When LiveCompare runs, it creates a folder called
lc_session_<session_id> in the working directory. This folder contains the following files:
lc_<execution_mode>_<current_date>.log— Log file for the session.
summary_<current_date>.out— A list of all tables that were processed. For each table, it shows the time LiveCompare took to process the table, the total number of rows and how many rows were processed, how many differences were found in the table, and the maximum number of ignored columns, if any.
To get the complete summary, you can also execute the following query against the output database:
differences_<current_date>.out— Useful information about any differences. This file isn't generated if there are no differences.
The following is an example of a difference list:
To get the full list of differences with all details, you can execute the following query against the output database:
apply_on_the_first_<current_date>.sql— If there are any differences, this file shows a DML command to apply on the first database to make it consistent with all other databases. The following is an example of a script for the differences shown in the table:
LiveCompare generates this script. To fix the inconsistencies in the first database, execute the script in it.
LiveCompare generates a similar
apply_on_*.sqlscript for each database that has inconsistent data.
Before starting the comparison session, LiveCompare tries all connections. If the number of reachable connections isn't at least two, then LiveCompare aborts the whole session and gives an error message. If at least two connections are reachable, then LiveCompare proceeds with the comparison session. For all connections, LiveCompare writes a flag
connection_reachable in the
connections table in the cache database.
For all reachable connections, LiveCompare does some sanity checks around the database technologies and the setting
logical_replication_mode. If any of the sanity checks fail, then LiveCompare aborts the comparison and gives an error message.
Considering the tables available on all reachable connections, LiveCompare builds the list of tables to compare, taking into account the table filter. If a specific table doesn't exist on at least two connections, then the comparison on that specific table is aborted.
LiveCompare initially gathers metadata from all tables. This step is called setup. If any errors happen during the setup, for example, the user doesn't have access to a specific table, then it's called a setup error. If
abort_on_setup_error is enabled, then LiveCompare aborts the whole comparison session, and the program finishes with an error message. Otherwise, only the table having the error has its table comparison aborted, and LiveCompare moves on to the next table.
For each table that LiveCompare starts the table comparison on, LiveCompare first checks the table definition on all reachable connections. If the tables don't have the same columns and column data types, LiveCompare applies
column_intersection. If there are no columns to compare, then LiveCompare aborts the table comparison.
For each table being compared, when gathering the table metadata, LiveCompare builds the comparison key to use in the table comparison, following these rules:
Use the custom comparison key if configured.
Alternatively, use PK if available.
Alternatively, if the table has
UNIQUEindexes, among the
UNIQUEindexes that have all
NOT NULLcolumns, use the
UNIQUEindex with fewer columns.
If none of these are possible, try to use all
NOT NULLcolumns as a comparison key.
NULLcolumns are also considered if
ignore_nullable = false.
If you decide to use strategies 1 or 4 as a comparison key, then LiveCompare also checks for uniqueness on the key. If uniqueness isn't possible, then LiveCompare aborts the comparison on that table. You can disable this behavior by using
check_uniqueness_enforcement = false.
LiveCompare can identify and provide fixes for the following differences:
- A row exists in the majority of the data connections. The fix is an
INSERTon the divergent databases.
- A row doesn't exist in the majority of the data connections. The fix is a
DELETEon the divergent databases.
- A row exists in all databases, but some column values mismatch. The fix is an
UPDATEon the divergent databases.
The default setting is
difference_statements = all, which means that LiveCompare tries to apply all three DML types (
DELETE) for each difference it finds. But you can specify the type of DML for LiveCompare to consider when providing difference fixes. Change the value of the setting
difference_statementsto any of these values:
inserts: Fixes only
updates: Fixes only
deletes: Fixes only
inserts_updates: Fixes only
inserts_deletes: Fixes only
updates_deletes: Fixes only
difference_statements has the values
updates_deletes, then you can tell LiveCompare to ignore any
UPDATE that sets
NULL to a column.
difference_log stores all information about differences every time LiveCompare checks them. You can run LiveCompare in recheck mode multiple times, so this table shows how the difference evolved over the time window
in which LiveCompare was rechecking it.
Detected (D): The difference was just detected. In recheck and fix modes, LiveCompare marks all Permanent and Tie differences as Detected so it can recheck them.
Permanent (P): After rechecking the difference, if data is still divergent, LiveCompare marks the difference as Permanent.
Tie (T): This entry is the same as Permanent, but there isn't enough consensus to determine the connections that are the majority.
Absent (A): If, upon a recheck, LiveCompare finds that the difference doesn't exist anymore, that is, the row is now consistent between both databases, then LiveCompare marks the difference as Absent.
Volatile (V): If, upon a recheck,
xminchanged on an inconsistent row, then LiveCompare marks the difference as Volatile.
Ignored (I): You can stop difference recheck of certain differences by manually calling the function
<livecompare_schema_name>.accept_divergence(session_id, table_name, difference_pk)in the output PostgreSQL connection. For example: