Advanced Usage

After the end of execution of LiveCompare, you will notice it created 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: shows a list of all tables that were processed, and 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 also the maximum number of ignored columns, if any.

To get the complete summary, you can also execute the following query against the output database:

select *
from <output_schema>.vw_table_summary
where session_id = <session_id>;

  • differences_<current_date>.out: if there are any differences, this file shows useful information about each difference. This file is not generated if there are no differences.

For example, the difference list could be like this:

+-------------------+-------------------------+-----------------+---------------------+
| table_name| table_pk_column_names|   difference_pk| difference_status|
|-------------------+-------------------------+-----------------+---------------------|
| public.categories| category|             (7)| P|
| public.categories| category|            (10)| P|
| public.categories| category|            (17)| P|
| public.categories| category|            (18)| P|
+-------------------+-------------------------+-----------------+---------------------+

To get the full list of differences with all details, you can also execute the following query against the output database:

select *
from <output_schema.vw_differences
where session_id = <session_id>;

To understand how LiveCompare consensus worked to decide which databases are divergent, then the view vw_consensus can provide details on the consensus algorithm:

select *
from <output_schema.vw_consensus
where session_id = <session_id>;

  • apply_on_the_first_<current_date>.sql: if there are any differences, this file will show a DML command to be applied on the first database, to make the first database consistent all other databases. For example, for the differences above, this script could be:

BEGIN;

DELETE FROM public.categories WHERE (category) = 7;
UPDATE public.categories SET categoryname = $lc1$Games Changed$lc1$ WHERE (category) = 10;
INSERT INTO public.categories (category,categoryname) VALUES (17, $lc1$Test 1$lc1$);
INSERT INTO public.categories (category,categoryname) VALUES (18, $lc1$Test 2$lc1$);

COMMIT;

LiveCompare generates this script automatically. In order to fix the inconsistencies in the first database, you can simply execute the script in the first database.

LiveCompare generates a similar apply_on_*.sql script for each database that has inconsistent data.

Which differences to fix

LiveCompare is able to identify and provide fixes for the following differences:

  • A row exists in the majority of the data connections. The fix will be an INSERT on the divergent databases;

  • A row does not exist in the majority of the data connections. The fix will be a DELETE on the divergent databases;

  • A row exists in all databases, but some column values mismatch. The fix will be an UPDATE on the divergent databases.

By default difference_statements = all, which means that LiveCompare will try to apply all 3 DML types (INSERT, UPDATE and DELETE) for each difference it finds. But it is possible to specify which type of DML LiveCompare should consider when providing difference fixes, by changing the value of the setting difference_statements, which can be:

  • all (default): Fixes INSERTs, UPDATEs and DELETEs;

  • inserts: Fixes only INSERTs;

  • updates: Fixes only UPDATEs;

  • deletes: Fixes only DELETEs;

  • inserts_updates: Fixes only INSERTs and UPDATEs;

  • inserts_deletes: Fixes only INSERTs and DELETEs;

  • updates_deletes: Fixes only UPDATEs and DELETEs.

When difference_statements has the values all, updates, inserts_updates or updates_deletes, then it is possible to tell LiveCompare to ignore any UPDATEs that would set NULL to a column.

Difference log

Table difference_log stores all information about differences every time LiveCompare checked them. Users can run LiveCompare in re-check mode multiple times, so this table shows how the difference has evolved over the time window where LiveCompare was re-checking it.

  • Detected (D): The difference was just detected. In re-check and fix modes, LiveCompare will mark all Permanent and Tie differences as Detected in order to re-check them.

  • Permanent (P): After having re-checked the difference, if data is still divergent, LiveCompare marks the difference as Permanent.

  • Tie (T): Same as Permanent, but there is not enough consensus to determine which connections are the majority.

  • Absent (A): If upon a re-check LiveCompare finds that the difference does not exist anymore (the row is now consistent between both databases), then LiveCompare marks the difference as Absent.

  • Volatile (V): If upon a re-check xmin has changed on an inconsistent row, then LiveCompare marks the difference as Volatile.

  • Ignored (I): Users can stop difference re-check 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:

SELECT livecompare.accept_divergence(
    2                   -- session_id
  , 'public.categories' -- table_name
  , $$(10)$$            -- difference_pk
);