Installing and Using ACE Functions

ACE is installed by the pgEdge Platform installer. The following commands describe installing ACE on a management system (that is not a member of a replication cluster):

1. Navigate to the directory where ACE will be installed.

2. Invoke the pgEdge installer in that location with the command:

python3 -c "$(curl -fsSL https://pgedge-download.s3.amazonaws.com/REPO/install.py)

3. Create a directory named cluster in the pgedge directory created by the pgEdge installer.

4. Create and update a .json file, and place the file in cluster/cluster_name/cluster_name.json on the ACE host. For example, if your cluster name is us_eu_backend, the cluster definition file for this should be placed in /pgedge/cluster/us_eu_backend/us_eu_backend.json. The .json file must:

   • Contain connection information for each node in the cluster in the node's stanza.
   • Identify the user that will be invoking ACE commands in the db_user property. This user must also be the table owner.

After ensuring that the .json file describes your cluster connections and identifies the ACE user, you're ready to use ACE commands.

ACE Commands

To review online help about the ACE commands and syntax available for your version, use the command:

./pgedge ace [command_name] --help

The commands that follow are currently supported by the ACE module.

ACE table-diff

Use the table-diff command to compare the tables in a cluster and produce a csv or json report showing any differences. The syntax is:

$ ./pgedge ace table-diff cluster_name schema.table_name

• cluster_name is the name of the pgEdge cluster in which the table resides.
• schema.table_name is the schema-qualified name of the table that you are comparing across cluster nodes.

Optional Arguments

Include the following optional arguments to customize ACE table-diff behavior:

• --output specifies the output type;choose from json or csv when including the --output parameter to select the output type for a difference report. By default, the report written to diffs/<YYYY-MM-DD>/diffs_<HHMMSSmmmm>.json. If the output mode is CSV, then ACE will generate coloured diff files to highlight differences.
• --nodes specifies a comma-delimited subset of nodes on which the command will be executed. ACE allows up to a three-way node comparison. Simultaneously comparing more than three nodes at once is not recommended.
• --quiet suppresses messages about sanity checks and the progress bar in stdout. If ACE encounters no differences, ACE will exit without messages. Otherwise, it will print the differences to JSON in stdout (without writing to a file).
• --table-filter is a SQL WHERE clause that allows you to filter rows for comparison.

The following runtime options can impact ACE performance during a table-diff:

• --block_rows specifies the number of tuples to be used at a time during table comparisons. ACE computes an MD5 sum on the full chunk of rows per block and compares it with the hash of the same chunk on the other nodes. If the hashes match up between nodes, then ACE moves on to the next block. Otherwise, the rows get pulled in and a set difference is computed. If block_rows is set to 1000, then a thousand tuples are compared per job across tables. It is worth noting here that while it may appear that larger block sizes yield faster results, it may not always be the case. Using a larger block size will result in a speed up, but only up to a threshold. If the block size is too large, the Postgres array_agg() (opens in a new tab) function may run out of memory, or the hash might take longer to compute, thus annulling the benefit of using a larger block size. The sweet spot is a block size that is large enough to yield quicker runtimes, but still small enough to avoid the issues listed above. ACE enforces that block sizes are between 10^3 and 10^5 rows.
• batch_size: Dictates how many sets of block_rows a single process should handle. By default, this is set to 1 to achieve the maximum possible parallelism – each process in the multiprocessing pool works on one block at a time. However, in some cases, you may want to limit process creation overheads and use a larger batch size. We recommend you leave this setting to its default value, unless there is a specific use-case that demands changing it.
• --max_cpu_ratio specifies the percentage of CPU power you are allotting for use by ACE. A value of 1 instructs the server to use all available CPUs, while .5 means use half of the available CPUs. The default is .6 (or 60% of the CPUs). Setting it to its maximum (1.0) will result in faster execution times. This should be modified as needed.

ACE repset-diff

Use the repset-diff command to loop through the tables in a replication set and produce a csv or json report showing any differences. The syntax is:

$ ./pgedge ace repset-diff cluster_name repset_name

• cluster_name is the name of the cluster in which the replication set is a member.
• repset_name is the name of the replication set in which the tables being compared reside.
• --max_cpu_ratio specifies the percentage of CPU power you are allotting for use by ACE. A value of 1 instructs the server to use all available CPUs, while .5 means use half of the available CPUs. The default is .6 (or 60% of the CPUs).
• --block_rows specifies the number of tuples to be used at a time during table comparisons. If block_rows is set to 1000, then a thousand tuples are compared per job across tables.
• --output specifies the output type;choose from json or csv when including the --output parameter to select the output type for a difference report. By default, the report written to diffs/<YYYY-MM-DD>/diffs_<HHMMSSmmmm>.json.
• --nodes specifies a comma-delimited list of nodes on which the command will be executed.

ACE schema-diff

Use the schema-diff command to compare the schemas in a cluster and produce a csv or json report showing any differences. The syntax is:

$ ./pgedge ace schema-diff cluster_name node_one node_two schema_name --output=json|csv

• cluster_name is the name of the cluster in which the table resides.
• node_one is the name of the node on which the schema you are comparing resides; you will be comparing the schema to the same schema on node_two.
• schema_name is the name of the schema you will be comparing.
• output specifies the output type;choose from json or csv when including the --output parameter to select the output type for a difference report. By default, the report written to diffs/<YYYY-MM-DD>/diffs_<HHMMSSmmmm>.json.

ACE spock-diff

Use the spock-diff command to compare the meta-data on two cluster nodes, and produce a csv or json report showing any differences. The syntax is:

$ ./pgedge ace spock-diff cluster_name node_one node_two --output=json|csv

• cluster_name is the name of the cluster in which the table resides.
• node_one is the name of the node you will be comparing to node_two.
• output specifies the output type;choose from json or csv when including the --output parameter to select the output type for a difference report. By default, the report written to diffs/<YYYY-MM-DD>/diffs_<HHMMSSmmmm>.json.

ACE table-repair

Use the table-repair command to correct any discrepancies or inconsistencies identified by ACE. The syntax is:

$ ./pgedge ace spock-diff cluster_name diff_file source_of_truth schema.table_name --dry-run=false --upsert_only --generate_report

• cluster_name is the name of the cluster in which the table resides.
• diff_file is the path and name of the file that contains the table differences.
• source_of_truth is the name of the node that contains the table that has been manually confirmed to contain accurate rows. The table on other nodes will be updated to match the content of this table.
• schema.table_name is the schema-qualified name of the table that you are comparing across cluster nodes.
• --dry_run - Include this option to perform a test application of the differences between the source_of_truth and the other nodes in the cluster. The default is false.
• --upsert_only (or -u) - Set this option to true to specify that ACE should make only additions to the non-source of truth nodes, skipping any DELETE statements that may be needed to make the data match. This option does not guarantee that nodes will match when the command completes, but can be usful if you want to merge the contents of different nodes. The default value is false.
• --generate_report (or -g) - Set this option to true to generate a .json report of the actions performed;
  Reports are written to files identified by a timestamp in the format: reports/<YYYY-MM-DD>/report_<HHMMSSmmmm>.json. The default is false.

Example: table-repair Report

{
  "time_stamp": "08/07/2024, 13:20:19",
  "arguments": {
    "cluster_name": "demo",
    "diff_file": "diffs/2024-08-07/diffs_131919688.json",
    "source_of_truth": "n1",
    "table_name": "public.acctg_diff_data",
    "dbname": null,
    "dry_run": false,
    "quiet": false,
    "upsert_only": false,
    "generate_report": true
  },
  "database": "lcdb",
  "changes": {
    "n2": {
      "upserted_rows": [],
      "deleted_rows": [],
      "missing_rows": [
        {
          "employeeid": 1,
          "employeename": "Carol",
          "employeemail": "carol@pgedge.com"
        },
        {
          "employeeid": 2,
          "employeename": "Bob",
          "employeemail": "bob@pgedge.com"
        }
      ]
    }
  },
  "run_time": 0.1
}

Within the report:

• time_stamp displays when the function was called.
• arguments lists the syntax used when performing the repair.
• database identifies the database the function connected to.
• runtime tells you how many seconds the function took to complete.

The changes property details the differences found by ACE on each node of your cluster. The changes are identified by node name (for example, n2) and by type:

• The upserted_rows section lists the rows upserted. Note that if you have specified UPSERT only, the report will include those rows in the missing_rows section.
• The deleted_rows section lists the rows deleted on the node.
• The missing_rows section lists the rows that were missing from the node. You will need to manually add any missing rows to your node.

ACE table-rerun

If you suspect that the differences reported by ACE are merely temporary (possibly caused due to replication lag) you can use ACE to re-run a previous table-diff run to check if the differences still exist. The syntax is:

$ ./pgedge ace table-rerun <cluster_name> schema.table_name --diff_file=/path/to/diff_file.json

• cluster_name is the name of the cluster in which the table resides.
• diff_file is the path and name of the file that contains the table differences.
• schema.table_name is the schema-qualified name of the table that you are comparing across cluster nodes.

The table-rerun option reads the diffs from the provided diff file and rechecks the tables to see if those tuples still differ. If ACE identifies any differences, it writes them to a new diff file and reports the file path.