Recommended Drivers #

It is recommended to choose drivers that are written in Pure Go to minimize portability issues with the utility. Specifically, the following drivers are recommended:

Special Considerations #

Limitations #

Types of Tasks #

Generation of an Initial Configuration File #

To generate a configuration file, execute the config-generate command. You can update the configuration values there later. The configuration file is described in Section 5.1 and Section 5.2.

Launching the Data Load from the Source to the Destination DB #

To launch the data load from the source to the destination DB, execute the load command with the configuration file specified.

On launching the data load, the configuration file is validated as follows:

Stopping procopy #

procopy terminates automatically when all the tasks are completed. A task is considered complete if procopy reached the end of the table.

To stop procopy in operation, press Ctrl+C. Pressing Ctrl+C once launches the graceful shutdown. It takes several minutes for the application to shut down correctly and save all the information required for a restart. If Ctrl+C is pressed twice, the application terminates quickly. In this case, multiple “unique constraint violation” errors are likely to occur after a restart. Despite the errors, procopy must continue its operation as usual, but this can take much time.

Error Handling #

If for some reason procopy cannot apply a row, it saves the key and an error in a CSV file <taks_id>_index.csv in the problem_rows.local.save_dir/<timestamp> directory. If the problem_rows.local.save_dir configuration parameter is not specified, the /tmp/<timestamp>/*.csv directory is used.

The default behavior is to skip problematic rows, but in practice, this is not suitable in most cases. In such cases, it is posssible to set up procopy_options to store where to save batches of problematic rows using the following construct:

{
  "version": 1,
  "procopy_options": {
    "problem_rows": {
      "local": {
        "save_dir": "path_to_save_dir"
      }
    }
  }
}

If an error occurs that causes procopy to crash, the application saves the diagnostic information to the Procopy_panic_data_<current_time>.tar.gz file.

Repeated Application of Problematic Rows #

If issues arose during a previous load and you managed to resolve them, you can try to apply these rows without full data reload. To do this, execute the command:

procopy load --file filename --reload-from TIMESTAMP

Where TIMESTAMP is the name of the directory that stores indexes with problematic rows.

If issues occur again, new *_index.csv files will occur under the new TIMESTAMP.

Resuming the Load #

procopy can resume data loading from the point where it stopped last time for tasks of the Schema type and of the Table type for tables with keys.

If a task of the Query type was not completed, it cannot be resumed. Perform truncate before reloading.

If a task of the Table type was not completed for a table without keys (a heap), perform truncate before reloading, otherwise, rows will be duplicated.

If procopy_options.truncate is true or truncate is true for a task, the table will be cleared and data loading will start from scratch.

Configuring the Load Parallelism #

Parallelism of the load process is achieved by:

Set the global limitations on the number of threads for reading data from the source and loading it to the destination in the procopy_options.readers and procopy_options.loaders configuration parameters, respectively. By default, both equal NumCPU/2.

You can split large tables into parts by the ranges of unique keys, so each part will be processed in an individual task. To do this, set the global procopy_options.sub_task_rows parameter or the task.sub_task_rows parameter at the task level.

For each partition of a partitioned table, an individual task is created.

Configuring Tasks of the Table Type #

Configuration settings related to tasks of this type are specified in the tasks.table section of the configuration file. For example:

tasks:
    - id: tab1
      table:
        source_table: source_table
        destination_table: destination_table
        exclude_columns:
            - field4
        include_columns:
            - field1
            - field2
            - field3
            - field4
        column_mapping:
            field1: field2
            field2: field1
        where: (COL1 IS NULL OR COL1 > 10) AND COL2 <> 'value'
      batch_bytes: 976.6KiB
      truncate: false
      transform:
        field1:
            null_to_value: ""
        field2:
            values_to_null:
                - "NULL"
                - NULL_VAL
                - INVALID
        field3:
            null_char_replace: "\n"
      snapshot_id: null

To configure tasks of the Table type, choose the columns that will be finally loaded:

Use the transform setting to list rules to transform null values of the specified columns.

Use the where setting to limit the number of rows to load.

Configuring Tasks of the Schema Type #

Configuration settings related to tasks of this type are specified in the tasks.schema section of the configuration file. For example:

tasks:
    - id: schema_task
      schema:
        source_schema: schema1
        destination_schema: schema2
        exclude_tables:
            - TABLE1
        include_tables:
            - TABLE1
            - TABLE2
            - TABLE2
        table_mapping:
            TABLE2: main_table
      batch_bytes: 976.6KiB
      truncate: false
      snapshot_id: null

To configure tasks of the Schema type, choose tables that will be finally loaded:

Configuring Tasks of the Query Type #

Tasks of the Query type load data to the destination DB from the source DB that is obtained by running a query. Configuration settings related to tasks of this type are specified in the tasks.query section of the configuration file. For example:

tasks:
    - id: tab2
      query:
        sql: SELECT id, name FROM users ORDER BY id
        destination_table: users_table
        destination_columns:
            - id
            - login
      batch_bytes: 976.6KiB
      truncate: false
      snapshot_id: null

When configuring tasks of the Query type, list columns in the tasks.query.destination_columns setting in the same order as in the query.

Configuring Tasks to Specify a Snapshot #

In tasks that load data from PostgreSQL/Postgres Pro, you can specify the snapshot to load data for. To do this, follow the steps below:

For example, the fragment of the configuration file for this task can look as follows:

tasks:
- id: query_task
  query:
    sql: SELECT id, name FROM users ORDER BY id
    destination_table: users_table
    destination_columns:
        - id
        - login
  batch_bytes: 1000000
  truncate: false
  snapshot_id: 00000006-0000000000002007-1

As a result of this task, the data rows will be loaded as of the start of the transaction even if concurrent transactions change these rows.

For automatic creation of a snapshot on launching the data load, set the procopy_options.enable_auto_snapshot configuration parameter to true.

Migrating BFILE Objects #

When migrating BFILE objects, only aliases are transferred. The pure data must be available from the source or transferred by users.

config generate #

procopy config generate [-f|--format json|yaml] [-o|--output filename]

Generates a procopy configuration file.

load #

load -f|--file filename [--log-level error|warn|info|debug]
[-s|--queue-size count_of_batches] [-q|--quiet]
[-r|--reload-from TIMESTAMP] [--dry-run|--read-only]

Launches data load between the databases.