General Settings

General Settings#

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 .

Important

Each process keeps N+1 open connections: one to each data connection and another one to the output database.

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.

Important

The choice of the right data_fetch_mode for the right scenario is very important. Using prepared statements has the smallest footprint on the database server, so it's the safest approach, and it's good for the general use case. Another point is that prepared statements allow LiveCompare to always see the latest version of the rows, which might not happen when using server-side cursors on a busy database. So we recommend using prepared_statements for production, high-load servers and either server_side_cursors_* setting for testing, migration scenarios, and low-load servers. The best strategy probably mixes server_side_cursors_without_hold for very large tables and prepared_statements for the remaining tables. The following table shows a comparison of the cost/benefit ratio.

prepared_statements

server_side_cursors_with_hold

server_side_cursors_without_hold

xmin hold

very low

medium

high

xmin released per

buffer

chunk

whole comparison session

temp files

very low

very high

low

memory

very low

high

low

allows async conns

yes

yes

no

fastest for

general

small tables

large tables

recommended load

high

very low

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.

Important

If you cancel execution of LiveCompare by pressing Ctrl-C and start it again, then LiveCompare resumes the round for that table, starting from the point where the round state was saved.

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 .

Note

If consensus_mode = off and comparison_algorithm = block_hash , LiveCompare won't switch to a more granular comparison algorithm. This significantly improves the performance when divergences are found for a specific table, at the cost of not having the information about which rows are divergent. The user can subsequently run LiveCompare again adding the divergent tables, if any, to the [Table Filter] and compare only the divergent tables to get the information about which rows are divergent.

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 .

Important

PGD 3.6.18 introduced the new pre-created bdr_local_only_origin replication origin to use for applying local-only transactions. So if LiveCompare is connected to PGD 3.6.18, it doesn't create this replication origin, and we recommend you don't try to drop this replication origin.

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 .

Important

If a table has PK, then the PK columns aren't allowed to be different, even if column_intersection = on .

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 .

Important

Setting abort_on_setup_error is considered only during compare mode. In recheck mode, LiveCompare always aborts at the first error in setup.

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.

Contents