Advanced usage ============== When LiveCompare runs, it creates a folder called ``lc_session_`` in the working directory. This folder contains the following files: - ``lc__.log`` — Log file for the session. - ``summary_.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: .. code:: postgresql select * from .vw_table_summary where session_id = ; - ``differences_.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: .. code:: text +-------------------+-------------------------+-----------------+---------------------+ | 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: .. code:: postgresql select * from ; To understand how LiveCompare consensus worked to decide which databases are divergent, the view ``vw_consensus`` can provide details on the consensus algorithm: .. code:: postgresql select * from ; - ``apply_on_the_first_.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: .. code:: postgresql 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: 1. Use the custom comparison key if configured. 2. Alternatively, use PK if available. 3. Alternatively, if the table has ``UNIQUE`` indexes, among the ``UNIQUE`` indexes that have all ``NOT NULL`` columns, use the ``UNIQUE`` index with fewer columns. 4. 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_statements`` to 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 ``.accept_divergence(session_id, table_name, difference_pk)`` in the output PostgreSQL connection. For example: .. code:: postgresql SELECT livecompare.accept_divergence( 2 -- session_id , public.categories -- table_name , $$(10)$$ -- difference_pk );