Advanced Usage

After the end of execution of LiveCompare, you will notice it created a foldercalled lc_session_<session_id> in the working directory. This folder containsthe 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 againstthe 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 thefollowing 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 aredivergent, then the view vw_consensus can provide details on the consensusalgorithm:

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 theinconsistencies in the first database, you can simply execute the script inthe first database.

LiveCompare generates a similar apply_on_*.sql script for each database thathas 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 tryto apply all 3 DML types (INSERT, UPDATE and DELETE) for each differenceit finds. But it is possible to specify which type of DML LiveCompare shouldconsider when providing difference fixes, by changing the value ofthe setting difference_statements, which can be:

  • `all` (default): Fixes `INSERT`s, `UPDATE`s and `DELETE`s;

  • `inserts`: Fixes only `INSERT`s;

  • `updates`: Fixes only `UPDATE`s;

  • `deletes`: Fixes only `DELETE`s;

  • `inserts_updates`: Fixes only `INSERT`s and `UPDATE`s;

  • `inserts_deletes`: Fixes only `INSERT`s and `DELETE`s;

  • `updates_deletes`: Fixes only `UPDATE`s and `DELETE`s.

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

Difference log

Table difference_log stores all information about differences every timeLiveCompare checked them. Users can run LiveCompare in re-check mode multipletimes, so this table shows how the difference has evolved over the time windowwhere 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
);