General Settings v3

logical_replication_mode

Affects how the program interprets connections and table filter settings and also the requirements to check for in the connections before starting the comparison. Currently the possible values are:

• off
  Assumes there's no logical replication between the databases.

• native
  Assumes there's native logical replication between the databases. Enables the use of the Table Filter -> publications setting to specify the list of tables to use. Requires PostgreSQL 10+ on all databases.

• pglogical
  Assumes there's pglogical replication between the databases. Enables the use of the Table Filter -> replication_sets setting to specify the list of tables to use. Also enables the use of node_name to specify the data connections, which requires setting the Initial Connection that's used to retrieve DSN information of the nodes. Requires the pglogical extensions to be installed on all databases.

• bdr
  Assumes all data connections are nodes from the same PGD cluster. Enables use of the Table Filter -> replication_sets setting to specify the list of tables to use. Also enables the use of node_name to specify the data connections, which requires setting the Initial Connection that's used to retrieve DSN information of the nodes. Requires pglogical and bdr extensions installed on all databases.

all_bdr_nodes

If logical_replication_mode is set to bdr, then you can specify only the Initial Connection and let LiveCompare build the connection list based on the current list of active PGD nodes. Default: off.

max_parallel_workers

Number of parallel processes to consider. Each process works on a table from the queue. Default: 2.

buffer_size

Number of rows to retrieve from the tables on every data fetch operation. Default: 4096.

log_level

Verbosity level in the log file. Possible values: debug, info, warning, or error. Default: info.

data_fetch_mode

Affects how LiveCompare fetches data from the database.

• prepared_statements
  Uses prepared statements (a query with LIMIT) for data fetch. Only a very small amount of data (buffer_size = 4096 rows by default) is fetched each time, so it has the smallest impact of all three modes, and for the same reason it's the safer fetch mode. Allows asynchronous data fetch (defined by parallel_data_fetch). For the general use case, this fetch method provides good performance, but a performance decrease can be felt for large tables. This is the default and strongly recommended when server load is medium-high.

• server_side_cursors_with_hold
  Uses server-side cursors WITH HOLD for data fetch. As table data is retrieved in a single transaction, it holds back xmin and can cause bloat and replication issues and also prevent VACUUM from running well. Also, the WITH HOLD clause tells Postgres to materialize the query (workers can hang for a few seconds waiting for the data to materialize), so the whole table data consumes RAM and can be stored on Postgres side disk as temporary files. You can reduce all that impact by increasing buffer_size a little. Allows asynchronous data fetch (defined by parallel_data_fetch). For the general use case, this fetch method doesn't provide any benefits when compared to prepared_stataments, but for multiple small tables it's faster. However, this mode is recommended only when load is very low, for example, on tests and migration scenarios.

• server_side_cursors_without_hold
  Uses server-side cursors WITHOUT HOLD for data fetch. As server_side_cursors_with_hold, this mode can also hold back xmin, thus it potentially can cause bloat, VACUUM, and replication issues on Postgres. However, such impact is higher because WITHOUT HOLD cursors require an open transaction for the whole comparison session (this requirement will be lifted in later versions). As the snapshot is held for the whole comparison session, comparison results might be helpful depending on your use case. As the query isn't materialized, memory usage and temp file generation remains low. Asynchronous data fetch isn't allowed. In terms of performance, this mode is slower for the general use case, but for large tables it can be the faster. We recommend it when load on the database is low-medium.

parallel_chunk_rows

Removed in LiveCompare 3.0. If this setting is present in any .ini file, remove it before using the file with LiveCompare 3.0.

parallel_data_fetch

Specifies whether data fetch is performed in parallel (that is, using async connections to the databases). Improves performance of multi-way comparison. If any data connections aren't PostgreSQL, then this setting is automatically disabled. It's allowed only when data_fetch_mode = prepared_statements or data_fetch_mode = server_side_cursors_with_hold. Default: on.

comparison_algorithm:

Affects how LiveCompare works through table rows to compare data. Using hashes is faster than full-row comparison. It can assume one of the following values:

• full_row
  Disables row comparison using hashes. Full comparison, in this case, is performed by comparing the row column by column. For comparisons involving Oracle 10g database, full_row is the only valid value for the comparison_algorithm parameter.

• row_hash
  Enables row comparison using hashes and enables table splitting. Tables are split so each worker compares a maximum of parallel_chunk_rows per table. Data row is hashed in PostgreSQL, so the comparison is faster than full_row. However, if the hash for a specific row doesn't match, then for that specific row, LiveCompare falls back to the full_row algorithm (that is, compare row by row). If any data connection isn't PostgreSQL, then LiveCompare uses a row hash that's defined as the MD5 hash of the concatenated column values of the row being considered, a common hash among the database technologies being compared.

• block_hash
  Works the same as row_hash, but instead of comparing row by row, LiveCompare builds a block hash, that is, a hash of the hashes of all rows in the data buffer that was just fetched (maximum of buffer_size rows). Conceptually it works like a two-level Merkle tree. If the block hash matches, then LiveCompare advances the whole block, which is why this comparison algorithm is faster than row_hash. If block hash doesn't match, then LiveCompare falls back to row_hash and performs the comparison row by row in the buffer to find the divergent rows. This is the default value.

min_time_between_heart_beats

Time in seconds to wait before logging a heart beat message to the log. Each worker tracks it separately per round part being compared. Default: 30 seconds.

min_time_between_round_saves

Time in seconds to wait before updating each round state when the comparison algorithm is in progress. A round save can happen only during a heart beat, so min_time_between_round_saves must be greater than or equal to min_time_between_heart_beats. When the round finishes, LiveCompare always updates the round state for that table. Default: 60 seconds.

comparison_cost_limit

If > 0, corresponds to a number of rows each worker processes before taking a nap of comparison_cost_delay seconds. Defaults to 0, meaning that each worker processes rows without taking a nap.

comparison_cost_delay

If comparison_cost_limit > 0, then this setting specifies how long each worker sleeps. Default: 0.0.

stop_after_time

Time in seconds after which LiveCompare stop as if you press Ctrl-C. You can resume the comparison session that was interrupted, if not finished yet, by passing the session ID as an argument in the command line. Default: stop_after_time = 0, which means that automatic interruption is disabled.

consensus_mode

Consensus algorithm used by LiveCompare to determine which data connections are divergent. Possible values are off, simple_majority, quorum_based, or source_of_truth. If consensus_mode = source_of_truth, then difference_sources_of_truth must be filled. Default: simple_majority.

difference_required_quorum

If consensus_mode = quorum_based, then this setting specifies the minimum quorum required to decide which connections are divergent. Must be a number between 0.0 and 1.0. 0.0 means no connection is required, and 1.0 means all connections are required. Both cases are extreme and we don't recommend using them. The default value is 0.5, and we recommend using a value close to that.

difference_sources_of_truth

Comma-separated list of connections names (or node names, if logical_replication_mode = bdr and all_bdr_nodes = on) to consider as the source of truth. It's used only when consensus_mode = source_of_truth. For example: difference_sources_of_truth = node1,node2. In this example, either the sections node1 Connection and node2 Connection must be defined in the .ini file or all_bdr_nodes = on and only the Initial Connection is defined, while node1 and node2 must be valid PGD node names.

difference_tie_breakers

Comma-separated list of connection names (or node names, if logical_replication_mode = bdr and all_bdr_nodes = on) to be considered as tie breakers whenever the consensus algorithm finds a tie situation. For example: difference_tie_breakers = node1,node2. In this example, either the sections node1 Connection and node2 Connections must be defined in the .ini file or all_bdr_nodes = on and only the Initial Connection is defined, while node1 and node2 must be valid PGD node names. Default: Don't consider any connection as tie breaker.

difference_statements

Controls the kind of DML statements for LiveCompare to generate. The value of difference_statements can be one of:

• all (default)
• inserts
• updates
• deletes
• inserts_updates
• inserts_deletes
• updates_deletes

difference_allow_null_updates

Determines whether commands like UPDATE SET col = NULL are allowed in the difference report. Default: on.

difference_statement_order

Controls order of DML statements that LiveCompare generates. The value of difference_statement_order can be one of:

• delete_insert_update
• delete_update_insert (default)
• insert_update_delete
• insert_delete_update
• update_insert_delete
• update_delete_insert

difference_fix_replication_origin

When working with PGD databases, for difference, LiveCompare creates a specific replication origin if it doesn't exist yet. It then uses the replication origin to create an apply script with DML fixes. The setting difference_fix_replication_origin specifies the name of the replication origin used by LiveCompare. If you don't set any value for this setting, then LiveCompare sets difference_fix_replication_origin = bdr_local_only_origin. The replication origin that LiveCompare creates isn't dropped to allow verification after the comparison. However, if needed, you can manually drop the replication origin later. Requires logical_replication_mode = bdr.

difference_fix_start_query

Arbitrary query that's executed at the beginning of the apply script generated by LiveCompare. Additionally, if a PGD comparison is being performed and the difference_fix_start_query is empty, then LiveCompare also automatically does the following:

• If the divergent connection is PGD 3.6.7, adds SET LOCAL bdr.xact_replication = off;
• Adds commands that set up transaction to use the replication origin specified in difference_fix_replication_origin

show_progress_bars

Determines whether to show progress bars in the console output. Disabling this setting might be useful for batch executions. Default: on.

output_schema

In the output connection, the schema where the comparison report tables are created. Default: livecompare.

hash_column_name

Every data fetch contains a specific column that's the hash of all actual columns in the row. This setting specifies the name of this column. Default: livecompare_hash.

rownumber_column_name

Some fetches need to use the row_number() function value inside a query column. This setting specifies the name of this column. Default: livecompare_rownumber.

fetch_row_origin

When this setting is enabled, LiveCompare fetches the origin name for each divergent row, which might be useful for debugging purposes. To be enabled, requires logical_replication_mode set to pglogical or bdr. Default: off.

column_intersection

When this setting is enabled, for a given table that's being compared, LiveCompare works only on the intersection of columns from the table on all connections, ignoring extra columns that might exist on any of the connections. When this setting is disabled, LiveCompare checks if columns are equivalent on the table on all connections and aborts the comparison of the table if there are any column mismatches. Default: off.

ignore_nullable

For a specific table comparison, if LiveCompare is using a comparison key different from the primary key, then LiveCompare requires all columns to be NOT NULL if ignore_nullable is enabled (default). You can override that behavior by setting ignore_nullable = off, which allows LiveCompare to consider null-able columns in the comparison, which in some corner cases can produce false positives.

check_uniqueness_enforcement

If LiveCompare is using a user-defined comparison key or using all columns in the table as a comparison key, then LiveCompare checks for table uniqueness on the comparison key if setting check_uniqueness_enforcement is enabled (default).

oracle_ignore_unsortable

When enabled, tells LiveCompare to ignore columns with Oracle unsortable data types (BLOB, CLOB, NCLOB, BFILE) if column isn't part of the table PK. If enabling this setting, we recommend also enabling column_intersection.

oracle_user_tables_only

When enabled, tells LiveCompare to fetch table metadata only from the Oracle logged-in user. This approach is faster because it reads, for example, from sys.user_tables and sys.user_tab_columns instead of sys.all_tables and sys.all_tab_columns. Default: off.

oracle_fetch_fk_metadata

When enabled, tells LiveCompare to fetch foreign-key metadata, which can be a slow operation. Overrides the value of the setting fetch_fk_metadata on the Oracle connection. Default: off.

schema_qualified_table_names

Table names are treated as schema qualified when this setting is enabled. Disabling it allows comparing tables without using schema-qualified table names. On Oracle x Postgres comparisons, it requires also enabling oracle_user_tables_only. On Postgres x Postgres, it allows for comparisons of tables that are under different schemas, even in the same database. Also, when schema_qualified_table_names is enabled, Table Filter -> tables, Row Filter, and Column Filter allow table name without the schema name. Default: on.

force_collate

Removed in LiveCompare 3.0. If this setting is present in any .ini file, remove it before using the file with LiveCompare 3.0.

work_directory

Path to the LiveCompare working directory. The session folder containing output files is created in this directory. Default: . (current directory).

abort_on_setup_error

When enabled, if LiveCompare encounters any error when trying to set up a table comparison round, the whole comparison session is aborted. Default: off.

custom_dollar_quoting_delimiter

When LiveCompare finds differences, it outputs the DML using dollar quoting on strings. The default behavior is to create a random string to compose it. If you want by any means to use a custom one, you can set this parameter as the delimiter to use. You need to set only the constant, not the $ symbols around the constant. Default: off, which means LiveCompare uses an md5 hash of the word LiveCompare.

session_replication_role_replica

When enabled, LiveCompare uses the session_replication_role PostgreSQL setting as replica in the output apply scripts. That's useful if you want to prevent firing triggers and rules while applying DML in the nodes with divergences. Enabling it requires a PostgreSQL superuser. Otherwise, it has no effect. Default: off.

split_updates

When enabled, LiveCompare splits UPDATE divergences. That is, instead of generating an UPDATE DML, it generates corresponding DELETE and INSERT in the apply script. Default: off.

float_point_round

An integer to specify decimal digits that LiveCompare rounds when comparing float-point values coming from the database Default: -1, which disables float-point rounding.

• On this page
• logical_replication_mode
• all_bdr_nodes
• max_parallel_workers
• buffer_size
• log_level
• data_fetch_mode
• parallel_chunk_rows
• parallel_data_fetch
• comparison_algorithm:
• min_time_between_heart_beats
• min_time_between_round_saves
• comparison_cost_limit
• comparison_cost_delay
• stop_after_time
• consensus_mode
• difference_required_quorum
• difference_sources_of_truth
• difference_tie_breakers
• difference_statements
• difference_allow_null_updates
• difference_statement_order
• difference_fix_replication_origin
• difference_fix_start_query
• show_progress_bars
• output_schema
• hash_column_name
• rownumber_column_name
• fetch_row_origin
• column_intersection
• ignore_nullable
• check_uniqueness_enforcement
• oracle_ignore_unsortable
• oracle_user_tables_only
• oracle_fetch_fk_metadata
• schema_qualified_table_names
• force_collate
• work_directory
• abort_on_setup_error
• custom_dollar_quoting_delimiter
• session_replication_role_replica
• split_updates
• float_point_round