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_*.sql
script for each database that has inconsistent data.
Aborting comparisons
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.
Comparison key
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
UNIQUE
indexes, among theUNIQUE
indexes that have allNOT NULL
columns, use theUNIQUE
index with fewer columns.If none of these are possible, try to use all
NOT NULL
columns as a comparison key.NULL
columns are also considered ifignore_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
.
Differences to fix
LiveCompare can identify and provide fixes for the following differences:
- A row exists in the majority of the data connections. The fix is an
INSERT
on the divergent databases. - A row doesn't exist in the majority of the data connections. The fix is a
DELETE
on the divergent databases. - A row exists in all databases, but some column values mismatch. The fix is an
UPDATE
on the divergent databases.
The default setting is difference_statements = all
, which means that LiveCompare tries to apply all three DML types (INSERT
, UPDATE
, and 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_statements
to any of these values:
all
(default): FixesINSERT
,UPDATE
, andDELETE
DML types.inserts
: Fixes onlyINSERT
DML types.updates
: Fixes onlyUPDATE
DML types.deletes
: Fixes onlyDELETE
DML types.inserts_updates
: Fixes onlyINSERT
andUPDATE
DML types.inserts_deletes
: Fixes onlyINSERT
andDELETE
DML types.updates_deletes
: Fixes onlyUPDATE
andDELETE
DML types.
When difference_statements
has the values all
, updates
, inserts_updates
, or updates_deletes
, then you can tell LiveCompare to ignore any UPDATE
that sets NULL
to a column.
Difference log
The table 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,
xmin
changed 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: