Advanced usage v2

Suggest edits

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:
```
select *
from <output_schema>.vw_table_summary
where session_id = <session_id>;
```
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:

+-------------------+-------------------------+-----------------+---------------------+
| 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 execute the following query against the output database:

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

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

```postgresql
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 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:
```
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. 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 the UNIQUE indexes that have all NOT NULL columns, use the UNIQUE 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 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.

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_statementsto any of these values:

all (default): Fixes INSERT, UPDATE, and DELETE DML types.
inserts: Fixes only INSERT DML types.
updates: Fixes only UPDATE DML types.
deletes: Fixes only DELETE DML types.
inserts_updates: Fixes only INSERT and UPDATE DML types.
inserts_deletes: Fixes only INSERT and DELETE DML types.
updates_deletes: Fixes only UPDATE and DELETE 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:

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