ProGate Documentation

Copyright © 2016–2026 The Postgres Professional company

Legal Notice

This documentation (hereinafter the Documentation) is intended solely for the use with the ProGate software product (hereinafter the Product) and for users of this Product.

It is not allowed to use the Documentation for third-party products or as part of documentation for other products.

Other terms of use of the Documentation are given in the User Agreement.

Postgres Pro is Copyright © 2016–2026 by Postgres Professional.

The copyright holder of the Product, Postgres Professional (hereinafter the Copyright Holder), does not grant any expressed or implied warranties, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. The Product provided hereunder is on an "as is" basis, and the Copyright Holder has no obligations to provide maintenance, support, updates, enhancements, or modifications.

In no event shall the Postgres Professional company be liable to any party for direct, indirect, special, incidental, or consequential damages, including lost profits, arising out of the use of the Product and its Documentation by the users, even if the Copyright Holder has been advised of the possibility of such damage.

Distribution of this Documentation or its parts requires an explicit written permission from the Postgres Professional company.

Part I. ProGate 1.1 UI Documentation

Chapter 1. ProGate UI Overview

The ProGate UI is a web application designed to configure, manage, and monitor data transfer tasks between ProGate-supported databases.

The UI enables you to:

Configure connections to source and target databases.

Set the operating parameters for ProGate utilities, i.e., ProCopy and ProSync.

Configure data transfer tasks.

Manage data transfer processes.

Monitor the execution of data transfer tasks.

This chapter includes information about the following UI layouts:

Core UI layout

UI login page layout

1.1. Core UI Layout

The displayed UI may contain all or some of the core UI elements depending on the page that is opened by a user.

The core UI elements are:

Navigation panel

Toolbar

Page, including tables

Modal UI elements

1.1.1. Navigation Panel

Always displayed, the navigation panel serves as the primary method of navigation through the UI pages.

The breadcrumbs at the top of pages help users understand their location in the UI.

Possible actions:

To navigate through the pages, click the panel entries.

To quickly go to the home page, click the ProGate logo at the top of the panel.

To collapse or expand the panel, click Collapse or Expand at the bottom of the panel.

1.1.2. Toolbar

Always displayed, the toolbar contains controls for customizing the UI look and feel. It also displays the name of the currently logged-in user and a logout button.

Possible actions:

To switch the UI color theme, click Light theme or Dark theme.

To switch the UI display language, select the language from the drop-down list next to the color theme button.

To log out of the web application, click Log out.

1.1.3. Page

A page displays various ProGate objects and allows viewing and managing them.

A typical ProGate page can contain the following UI elements:

The breadcrumbs that help users understand their location in the UI.

To quickly go to the ProGate home page, click Home.

The page header.

Action buttons.

To take an action, for example, to create a transfer, click the related action button.

Tables. A table displays a list of objects and their attributes.

Tables may use horizontal indicators to display information graphically.

Possible actions:

To manage the displayed columns and their order, click Edit table and configure the desired column layout.

To filter and sort tables, click Filter to filter and Sort down or Sort up to sort.

To restore the default column layout, click Restore table.

To perform an action on an object, select the action in the Actions column of the related table entry. Some actions are located in the Three vertical dots icon menu.

To refresh the list of objects, click Refresh in the top-right corner of the page.

1.1.4. Modal UI Elements

Modal elements appear on top of other content and block interaction with the rest of the UI.

The ProGate UI uses these elements when a user attention or decision is required. For example, ProGate always asks for a confirmation when deleting an object.

The modal UI elements are:

Object panel: This element contains details and parameters of an object, along with all necessary controls to create or edit it.

Confirmation dialog: This element is used to prevent accidental actions.

For example, a confirmation dialog is displayed when a user attempts to delete an object.

1.2. UI Login Page Layout

The login page layout differs from the regular page layout and focuses on the login scenario.

Possible actions:

To switch the UI color theme, click Light theme or Dark theme.

To log in, enter your user name or LDAP login and password.

To show your password in clear text or hide it, click Show password or Hide password.

Chapter 2. Data Transfers

This section explains how to manage data transfers. It includes the following instructions:

Creating a Data Transfer

Viewing Data Transfers

Viewing Detailed Data Transfer Information

Starting a Data Transfer

Editing a Data Transfer

Deleting a Data Transfer

2.1. Creating a Data Transfer

In the navigation panel, go to Transfers.

In the top-right corner of the page, click Create transfer.

Enter parameters of the new data transfer (parameters marked with an asterisk are required):

Name.

Data Source • Type: The source database and its type for the data transfer. The possible values are the database connection names entered while creating a database connection.

Data Target • Type: The target database and its type for the data transfer. The possible values are the database connection names entered while creating a database connection.

Launch Parameter: The ProGate utility to use for the data transfer.

Possible values:

ProSync. If selected, change data capture (CDC) is started after the initial loading.

ProCopy. If selected, the initial loading is performed. This parameter cannot be disabled.

Click Create.

Edit initial load parameters and replication parameters, if applicable.

If only ProCopy is used, you can edit the following parameters (default values are entered automatically):

Number of read threads: The number of parallel read processes. Each of them processes one task at a time until it is finished.

Number of write threads: The number of parallel write processes. Each process retrieves batches from the queue and applies them to the destination.

Data block size limit in bytes: The global limitation on the size of one batch.

The parameter value must be greater than 0.

Need to clear target tables: Specifies whether all target database tables are truncated before the data loading.

If set to No, target database tables are not truncated.

Possible values:

Yes

No

To set more parameters, click All parameters.

The window with the following additional parameters will be displayed:

Disable sorting of records by unique key: Disables loading of the data sorted by the unique primary key. The whole table is retrieved through a cursor.

Possible values:

Yes

No

Log level.

Possible values:

info

debug

Number of decimal places for monetary types: The number of fractional digits in the source database.

LOB size threshold for binary protocol: The threshold for the LOB size that defines how to fetch LOBs.

If the size of a LOB is less than this value, the binary protocol is used and all the data in the LOB is loaded to the random-access memory (RAM).

If the size of the LOB is greater than this value, the LOB is read in parts.

Number of data block sending attempts on write error: The number of batch sending attempts in case of error.

Time between data block sending attempts on error: The time between batch sending attempts in case of error.

Transfer bfile: If this parameter is specified, all the BFILE objects are copied as a whole from Oracle during the data loading.

By default, only identifiers are copied, that is, the directory aliases and filenames.

Possible values:

Yes

No

Sliding window reading size in rows: The size of the sliding window. Number of rows selected at a time.

Disable index hints usage when reading data: Specifies whether to disable index hints when creating SELECT queries.

Possible values:

Yes

No

Maximum data block reading time: The maximum time to read a data block.

Number of records in one subtask: The number of rows contained in one subtask of a large table.

Type conversion options to string.

Enter the key word and its value.

You can add multiple options by clicking + Add option.

If ProSync is also used, you can edit the following parameters (default values are entered automatically):

Data block size for reading from source in bytes: The batch size for ProSync reading from Oracle LogMiner to be used.

Reading changes from Oracle online REDO logs: Specifies whether to read the changes from archive and online REDO LOGS. This parameter enables getting the changes as soon as they occur in online logs. Not recommended to use for reliability. Because of how Oracle writes logs, some operations can be missed.

Possible values:

Yes

No

Operations count threshold within transaction for intermediate cache usage: The number of operations in one transaction that causes writing of intermediate data to disk.

To set more parameters, click All parameters.

The window with the following additional parameters will be displayed:

Maximum data block size when reading from source: The maximum size to which the batch size can be increased when reading.

If the maximum size is reached, ProSync terminates with an error. This can happen if there is an open transaction that does not close. The user must resolve this issue.

Data block size for processing: The batch size for processing a transaction.

This parameter is for internal tuning of the application.

Data block size for writing: The batch size for inserting data.

The value of 0 means that the data is inserted as new data occurs.

Validate record sequence: Specifies whether to validate a sequence of LogMiner log files.

If a log file is missing, ProSync terminates with an error.

If you are prepared to lose data, set this parameter to No.

Possible values:

Yes

No

Data volume in bytes to process in one iteration for LOB type data: The maximum number of bytes to write at a time when writing LOBs.

Click Save.

Add replication tasks.

In the top-right corner of the page, click + Add task.

Enter parameters of the new replication task (parameters marked with an asterisk are required):

Task Type.

Possible values:

SQL-Query

Schema-Schema

Name.

Source Schema: The schema of the replication source database.

Target Schema: The schema of the replication target database.

Target Table: The table in the schema of the replication target database. This field is displayed if you select the SQL-Query task type.

If you select the SQL-Query task type:

Type the SQL query for the data transfer task. The console window supports syntax checking and autocompletion based on keywords, field names, and table names. If the query is incorrect, an error is displayed.

To enable or disable query validation, use the Query validation toggle in the top-left corner above the console window.

Map fields from the SQL query with target table fields:

Manually by selecting target fields from drop-down lists next to source fields.

Automatically by clicking Map objects.

If you select the Schema-Schema task type:

Map tables from the source schema with target schema tables:

Manually by selecting target tables from drop-down lists next to source tables.

Automatically by clicking Map objects.

To view or hide table fields for manual field mapping, click Expand or Collapse.

Click Add task.

Click Finish setup.

2.2. Viewing Data Transfers

In the navigation panel, go to Transfers.

The table of data transfers with the following columns will be displayed:

Name.

Source: The source database for the data transfer. The possible values are the database connection names entered while creating a database connection.

Target: The target database for the data transfer. The possible values are the database connection names entered while creating a database connection.

Created at: The date and time when the data transfer was created.

Status.

Possible values:

Draft

Prepared

Start Requested

ProSync Init Start Requested

ProSync Init Started

ProSync Init Completed

ProCopy Run Start Requested

ProCopy Run Started

ProCopy Run Completed

ProSync Run Start Requested

ProSync Run Started

Stop Requested

ProSync Run Stop Requested

Status change: The date and time when the data transfer status was changed.

Execution progress: The number of completed data transfer tasks with a horizontal progress bar.

Actions.

For more information about available actions, refer to other instructions in this section.

2.3. Viewing Detailed Data Transfer Information

In the navigation panel, go to Transfers.

Click the name of the transfer.

The data transfer page with the following web interface elements will be displayed:

The ProCopy Tasks tile with the following parameters:

Completed: The number of completed tasks.

Data transfer speed: The rate at which the data is transferred.

Records transfer speed: The rate at which the records are transferred.

Total records: The total number of transferred records.

Total records with errors: The total number of records transferred with errors.

The ProSync Tasks tile. This tile is displayed if ProSync is used. The displayed parameters are as follows:

Parsing SCN: The system change number processed by ProSync.

Latest SCN: The current system change number in the source database.

Data transfer speed: The rate at which the data is transferred.

Records transfer speed: The rate at which the records are transferred.

Total data: The total volume of transferred data.

Total records: The total number of transferred records.

The table of the data transfer with the following columns:

Status: The data transfer status.

Created At: The time when the data transfer was created.

Changed by: The user who initiated the change of the data transfer status.

Process: The process number.

To view detailed process information, click View grey icon next to the process.

2.4. Starting a Data Transfer

In the navigation panel, go to Transfers.

Click Start next to the data transfer.

You can stop the already started data transfer. To do this, click Stop next to the data transfer.

2.5. Editing a Data Transfer

In the navigation panel, go to Transfers.

Click Three vertical dots icon → Configure next to the data transfer. You can edit only transfers in the Draft status or stopped transfers.

Edit source and target database parameters (parameters marked with an asterisk are required) and click Next.

Edit initial load parameters and replication parameters, if applicable, and click Next.

Edit replication tasks and click Next.

Click Finish setup.

2.6. Deleting a Data Transfer

Important

Deleted data transfers cannot be restored.

To delete a data transfer:

In the navigation panel, go to Transfers.

Click Three vertical dots icon → Delete next to the data transfer.

Click Delete.

Chapter 3. Database Connections

This section explains how to manage database connections. It includes the following instructions:

Creating a Database Connection

Viewing Database Connections

Checking a Database Connection

Editing a Database Connection

Deleting a Database Connection

3.1. Creating a Database Connection

In the navigation panel, go to Connections.

In the top-right corner of the page, click Add connection.

Enter parameters of the new database connection (parameters marked with an asterisk are required):

Name.

Database Type.

Possible values:

Postgres Pro/PostgreSQL

Oracle

Specify the following parameters:

Server.

Port.

Database.

Username: The name of the user to access the database.

Password: The user password to access the database.

Max Connections.

Connection Options: The connection option.

Enter the key word and its value.

For more information, refer to the official Postgres Pro documentation on connection parameter key words.

You can add multiple connection options by clicking + Add option.

Click Create.

3.2. Viewing Database Connections

In the navigation panel, go to Connections.

The table of database connections with the following columns will be displayed:

Name/ID.

Type.

Possible values:

PostgreSQL

Oracle Database

Creation date: The date and time when the database connection was created.

Host/Port: The host and port of the database connection.

Health: The database connection state.

Possible values:

Healthy

Unreachable

Actions.

For more information about available actions, refer to other instructions in this section.

3.3. Checking a Database Connection

In the navigation panel, go to Connections.

Click Check connection next to the database connection.

3.4. Editing a Database Connection

In the navigation panel, go to Connections.

Click Edit next to the database connection.

Edit database connection parameters.

Click Save.

3.5. Deleting a Database Connection

Important

Deleted database connections cannot be restored.

To delete a database connection:

In the navigation panel, go to Connections.

Click Delete next to the database connection.

Click Delete.

Part II. ProGate 1.1 Documentation

Chapter 4. About ProGate

ProGate is designed for data migration and replication between different source and destination databases, later referred to as sources and destinations.

ProGate consists of the following components:

procopy — a utility for initial data loading from a source to a destination.

prosync — a utility for CDC-based replaying changes to a source on a destination (CDC — change data capture).

procheck — a utility to verify data after a migration from a source to a destination.

4.1. Supported Sources and Destinations

The following table shows the supported sources and destinations.

Table 4.1. Supported Versions of Sources and Destinations

DBMS/File Format | Versions Supported as Source | Versions Supported as Destination
Oracle | 19c | —
Postgres Pro | 13 or higher | 16.4 or higher
PostgreSQL | 13 or higher | 16 or higher
Parquet | — | +

4.2. Supported Data Types

Types in the source DB are converted to types in the destination DB as follows: the source driver maps the types of the source DB to Go types, and then encoders are used to convert data to the binary format of the destination DB.

The following Oracle data types are supported:

NUMBER, CHAR, NCHAR, VARCHAR, NVARCHAR, VARCHAR2, NVARCHAR2, STRING, DATE, LONG, LONG RAW, CLOB, NCLOB, BLOB, BFILE, RAW(16), RAW(32), RAW, ROWID, UROWID, FLOAT, DEC, DECIMAL, DOUBLE PRECISION, INT, INTEGER, BINARY_INTEGER, PLS_INTEGER, SMALLINT, REAL, BINARY_FLOAT, BINARY_DOUBLE, TIMESTAMP, BOOLEAN, INTERVAL, XMLTYPE, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, VARRAY.

The following PostgreSQL data types are supported:

BFILE, BOOLEAN, BYTEA, CHAR, CHARACTER VARYING(N), VARCHAR(N), CHARACTER(N), CHAR(N), BPCHAR(N), BPCHAR, DBMS_LOB.BLOB, DBMS_LOB.CLOB, NAME, TEXT, SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC, REAL, DOUBLE PRECISION, SMALLSERIAL, SERIAL, BIGSERIAL, TIMESTAMP, DATE, TIME, INTERVAL, HSTORE, UUID, XML, JSON, JSONB, OID, MONEY, CIDR, INET, MACADDR, MACADDR8, tsquery, tsvector, txid_snapshot, pg_snapshot, pg_lsn, and custom data types.

When transferring data of the MONEY type, the value is read from the source without the currency sign, and the data is written to the destination with the currency sign corresponding to the destination locale.

The following table explains the mapping between Oracle and PostgreSQL data types:

Table 4.2. Mapping of Oracle Types to PostgreSQL Types

Oracle Type | Go Type | PostgreSQL Type

VARCHAR2
NVARCHAR2
CHAR
NCHAR
LONG

string

text
char(n)
varchar(n)
bool

CLOB
NCLOB

string

dbms_lob.clob
text

BINARY_DOUBLE
float64

real
double precision
smallint
integer
bigint
numeric
smallserial
serial
bigserial

FLOAT
DEC
DECIMAL
NUMBER

Decimal
numeric

BINARY_FLOAT | float32 | real
DATE | time.Time | timestamp

TIMESTAMP(0)
TIMESTAMP(3)
TIMESTAMP(6)
TIMESTAMP(9)
TIMESTAMP(0) WITH TIME ZONE
TIMESTAMP(3) WITH TIME ZONE
TIMESTAMP(6) WITH TIME ZONE
TIMESTAMP(9) WITH TIME ZONE

time.Time
timestamp [with timezone]

LONG RAW
BLOB
RAW

[]byte

sfile
dbms_lob.blob
bytea

BFILE

model.BFile
{
      alias        string,
      filename string
}

bfile

BOOLEAN | bool | bool

NTERVAL ...

model.Interval
{
    Years        int
    Months       int
    Days         int
    Hours        int
    Minutes      int
    Seconds      int
    Milliseconds int
    Sign         int
}

interval

Several next tables explain mapping of PostgreSQL data types to files in the Parquet format.

The table below explains mapping of numeric data types:

Table 4.3. Mapping of PostgreSQL Numeric Types to Files in the Parquet Format

PostgreSQL Type | Parquet Type | PostgreSQL Example | Parquet Example | Comment
smallint | INT32 | 5 | 5 |
integer | INT32 | 5 | 5 |
bigint | INT64 | 5 | 5 |
numeric | FIXED_LEN_BYTE_ARRAY | -1.258 | 0xFFFFFFFFFFFFFFFF​FFFFFFFFB5047180 | Precision 38 and scale 9 are only supported. The physical type is FIXED_LEN_BYTE_ARRAY(16), and the logical type is DECIMAL. The minimum value is -1701411834604692​31731687303715.​884105728, and the maximum value is 1701411834604692​31731687303715.​884105727.
decimal | FIXED_LEN_BYTE_ARRAY | -1.258 | 0xFFFFFFFFFFFFFFFFF​FFFFFFFB5047180 | The comment for the numeric type holds.
real | FLOAT | 129.153 | 129.1529998779297 |
double precision | DOUBLE | -1.258 | -1.258 |

The table below explains mapping of monetary data types:

Table 4.4. Mapping of PostgreSQL Monetary Types to Files in the Parquet Format

PostgreSQL Type | Parquet Type | PostgreSQL Example | Parquet Example | Comment
money | BYTE_ARRAY | $1.25 | 1.25 | The type is mapped as a string without the currency sign.

The table below explains mapping of string data types:

Table 4.5. Mapping of PostgreSQL String Types to Files in the Parquet Format

PostgreSQL Type | Parquet Type | PostgreSQL Example | Parquet Example | Comment
varchar | BYTE_ARRAY | "test string" | "test string" | Among others, varchar(n) with the specified length is supported, but the length is not taken into account.
char(10) | BYTE_ARRAY | "test" | "test" | If the string is shorter than the length, remaining characters are filled with spaces.
text | BYTE_ARRAY | "test string" | "test string" |

The table below explains mapping of date/time data types:

Table 4.6. Mapping of PostgreSQL Date/Time Types to Files in the Parquet Format

PostgreSQL Type | Parquet Type | PostgreSQL Example | Parquet Example | Comment
timestamp without time zone | INT64 | "2006-01-02 15:04:05" | 113621424​5000000 | timestamp(6) is supported, but the precision is ignored. The number of microseconds since the start of the UNIX epoch is returned.
timestamp with time zone | INT64 | "02 Jan 06 15:04 EST" | 11362322​40000000 | The time zone and precision are ignored. Microseconds are returned.
date | BYTE_ARRAY | "2006-01-02" | 2006-01-02 | A date is stored as a string in this format. BC dates are not supported.
time without time zone | INT64 | "15:04:05" | 54245000000 | The number of microseconds since the start of the day. timestamp(6) is supported, but the precision is ignored.

interval
FIXED_LEN_BYTE_ARRAY
"1 years 0 mons 2 days 3 hours 20 mins 0.0 secs"
1 year 2 days 03:20:00

The physical type is FIXED_LEN_BYTE_ARRAY(12). 4 bytes are allocated for each value of months, days, and milliseconds. The type is implemented as specified in LogicalTypes.md. The logical type is INTERVAL, and the converted type is INTERVAL.

Bear in mind that this mapping is performed with a loss of accuracy: while PostgreSQL stores interval up to microseconds, Parquet stores INTERVAL only up to milliseconds.

MinMax statistics is not collected for this type. If the statistics for this type is available, the following duckDB query results in errors:

SELECT * FROM parquet_metadata(./file.parquet)

The table below explains mapping of other data types.

Table 4.7. Mapping of PostgreSQL Other Types to Files in the Parquet Format

PostgreSQL Type | Parquet Type | PostgreSQL Example | Parquet Example
boolean | BOOLEAN | TRUE | true
bytea | BYTE_ARRAY | "test bytes" | "test bytes"

Note

The Parquet type xml is not supported at present.

4.3. Installation and Initial Setup

This section describes installation of the ProGate management service and initial setup. ProGate management service is a set of applications that supports ProGate web interface.

4.3.1. Hardware and Software Requirements

The ProGate management service requires an allocated or virtual server with the following minimum characteristics:

CPU: 8 cores

RAM: 16 GB

Disk space: 100 GB

Network interface: 1Gb/sec

ProGate runs on the following Linux-based operating systems:

ALT 9/10/11, ALT SP Release 10

Astra Linux 1.7/1.8

Debian 12/13

Red OS Murom 7.3/8.2

Red Hat Enterprise (RHEL) Linux 8/9

ROSA Chrome 2021.2

SUSE Linux Enterprise Server (SLES) 15

Ubuntu 22.04/24.04

4.3.2. Components

ProGate is shipped as the package progate, which includes all the needed dependencies.

The following utilities are included in progate, but they can also be installed individually (for example, to only use the command line):

procopy (package)

prosync (package)

procheck (package)

Note

procheck is not available through the web interface yet. So it can only be used as a command-line utility.

4.3.3. Installation Process

To install and configure the ProGate management service, follow the steps below.

Install the repository:

wget -O pgpro-repo-add.sh https://repo.postgrespro.ru/progate/progate/keys/pgpro-repo-add.sh
sh pgpro-repo-add.sh

Install the package:

apt install progate

The result of the installation is as follows:

The user and group named progate are created.

The procopy, prosync, and procheck utilities are installed in the /opt/pgpro/progate/bin/ directory.

The progate.service service is added but initially disabled.

Note

At this point, only the procopy, prosync, and procheck command-line utilities are available. The web interface is not yet ready and requires additional setup. So follow the instructions from Section 4.3.4.

4.3.4. Initial Setup

To start working with the ProGate web interface, execute the following steps:

Create a database, user, and schema.

The ProGate web interface requires a PostgreSQL/Postgres Pro database. In this database, create a dedicated schema where the web interface will store its support data and a user to work with this schema. For example, create the dbname database, the progate schema in it, and the user user with the password password:

CREATE USER user WITH PASSWORD 'password';
CREATE SCHEMA progate AUTHORIZATION user;
GRANT ALL PRIVILEGES ON SCHEMA progate TO user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA progate TO user;
ALTER DEFAULT PRIVILEGES IN SCHEMA progate
GRANT ALL PRIVILEGES ON TABLES TO user;
GRANT USAGE, CREATE ON SCHEMA progate TO user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA progate TO user;

Specify the database for the service.

To specify the database, in the environment file /opt/pgpro/progate/etc/env_progate, edit the line under the comment below so that it looks as follows (for details, see Section 4.3.4.1):

# PostgreSQL/Postgres Pro connection string
PROGATE_DB="postgres://user:password@host:5430/dbname?sslmode=disable&search_path=progate"

Set the value of search_path above to the name of the dedicated schema.

When composing the connection string, double-check that:

The user specified in the connection string has permissions for the progate schema.

The schema is in the specified database.

Launch the progate.service service using the following commands and add them to the system autostart:

systemctl start progate.service
systemctl enable progate.service

(Optional) If a firewall is used, allow network connections on the TCP/8081 port.

If all the steps are performed without errors, the ProGate management service is launched successfully. The ProGate web interface is available at http://<server IP address>:8081. See Section 4.3.5 for how to log in.

4.3.4.1. Setting up ProGate with the Environment File

For the service to work properly, in the environment file, which is created automatically, edit the PROGATE_DB setting to specify your user, password, host port, dbname, and search_path. search_path must contain the name of the schema where the support information is stored. The environment file is located at /opt/pgpro/progate/etc/env_progate and has the contents that may look like these:

# Port for ProGate backend
PROGATE_HTTP_SERVER_PORT="8081"

# ProGate backend serves static files from given directory
PROGATE_HTTP_SERVER_STATIC_DIR="/opt/pgpro/progate/ui"

# PostgreSQL/Postgres Pro connection string
PROGATE_DB="postgres://user:password@host:5430/dbname?sslmode=disable&search_path=progate"

# Log format: json | pretty
PROGATE_LOGS_FORMAT=pretty

# Log level: debug | info | warn | error
PROGATE_LOGS_LEVEL=info

# Length of the secret token. Must be greater than or equal to 32 characters
PROGATE_AUTH_ACCESS_TOKEN_SECRET="mT-Y8ICZv-zhirL_j2K69vX_Upbo8B6rMKtplqv4U4I="

# Lifetime of the web interface's access token
PROGATE_AUTH_ACCESS_TOKEN_LIFETIME="168h"

# Lifetime of the web interface's access refresh token
PROGATE_AUTH_REFRESH_TOKEN_LIFETIME="720h"

# Path to prosync bin
PROGATE_TRANSFER_WORK_PROSYNC_BIN="/opt/pgpro/progate/bin/prosync"

# Path to procopy bin
PROGATE_TRANSFER_WORK_PROCOPY_BIN="/opt/pgpro/progate/bin/procopy"

# Path to directory with procopy/prosync configs and other ProGate backend-specific support files
PROGATE_TRANSFER_WORK_WORKING_DIR="/var/lib/progate/execution"

For how to set values of time environment variables, refer to Section 4.3.6.

4.3.4.2. Setting up ProGate with the Configuration File

In addition to environment variables, ProGate can be set up by means of the .yaml configuration file. Pass the path to this file with the -c flag. For example:

/opt/pgpro/progate/bin/progate -c path/to/config.yaml

The configuration file defines the same characteristics as the environment variables and may look as follows:

http_server:
  port: 8081
  static_dir: "/opt/pgpro/progate/ui"
db: "postgres://dbaas@localhost:5430/dbaas?sslmode=disable&search_path=progate"
logs:
  format: pretty
  level: info
auth:
  access_token_secret: "mT-Y8ICZv-zhirL_j2K69vX_Upbo8B6rMKtplqv4U4I="
  access_token_lifetime: 168h
  refresh_token_lifetime: 720h
transfer_work:
  prosync:
    bin: "/opt/pgpro/progate/bin/prosync"
  procopy:
    bin: "/opt/pgpro/progate/bin/procopy"
  working_dir: "/var/lib/progate/execution"

The configuration parameters are described in Table 4.8.

Table 4.8. Contents of the ProGate Configuration File

Cofiguration parameter | Environment variable | Description | Example
http_server.port | PROGATE_HTTP_SERVER_PORT | Port for the ProGate backend | 8081
http_server.static_dir | PROGATE_HTTP_SERVER_STATIC_DIR | ProGate backend serves static files from this directory | /opt/pgpro/progate/ui
db | PROGATE_DB | PostgreSQL/Postgres Pro connection string | postgres://user:password@host:port/dbname​?sslmode=disable?search_path=progate
logs.format | PROGATE_LOGS_FORMAT | Log format: json / pretty | pretty
logs.level | PROGATE_LOGS_LEVEL | Log level: debug / info / warn / error | info
auth.access_token_secret | PROGATE_AUTH_ACCESS_TOKEN_SECRET | Length of the secret token. Must be greater than or equal to 32 characters | mT-Y8ICZv-zhirL_j​2K69vX_Upbo8B6rMKtplqv4U4I=
auth.access_token_lifetime | PROGATE_AUTH_ACCESS_TOKEN_LIFETIME | Lifetime of the web interface's access token | 168h
auth.refresh_token_lifetime | PROGATE_AUTH_REFRESH_TOKEN_LIFETIME | Lifetime of the web interface's access refresh token | 720h
transfer_work.prosync.bin | PROGATE_TRANSFER_WORK_PROSYNC_BIN | Path to the prosync binary | /opt/pgpro/progate/bin/prosync
transfer_work.procopy.bin | PROGATE_TRANSFER_WORK_PROCOPY_BIN | Path to the procopy binary | /opt/pgpro/progate/bin/procopy
transfer_work.working_dir | PROGATE_TRANSFER_WORK_WORKING_DIR | Path to the directory with procopy / prosync config files and other ProGate backend-specific support files | /var/lib/progate/execution

4.3.5. Logging in to the Web Application

To log in to the web application, use the following credentials:

Login: progate

Password: progate

4.3.6. Setting Time Values

In ProGate, time environment variables and configuration values follow the Golang duration string format. For example:

100 ms — 100 milliseconds

5.5s — five and a half seconds

5m — five minutes

5h — five hours

1h7m — one hour and seven minutes

4.4. Setting up the ProGate Source and Destination

This section describes the setup that is needed to run ProGate.

4.4.1. Setting up the Source

4.4.1.1. Oracle Source

The following setup of the Oracle source is needed:

Grant the user under which the migration will run with the following permissions:

GRANT CONNECT, RESOURCE TO PROGATE_USER;

GRANT EXECUTE ON DBMS_LOGMNR TO PROGATE_USER;
GRANT EXECUTE ON SYS.DBMS_CRYPTO TO PROGATE_USER;  -- for procheck to compare LOB objects

GRANT EXECUTE_CATALOG_ROLE TO PROGATE_USER;
GRANT LOGMINING TO PROGATE_USER;

GRANT SELECT ON V_$LOG TO PROGATE_USER;
GRANT SELECT ON V_$LOGFILE TO PROGATE_USER;
GRANT SELECT ON V_$LOGMNR_LOGS TO PROGATE_USER;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO PROGATE_USER;
GRANT SELECT ON V_$LOGMNR_PARAMETERS TO PROGATE_USER;
GRANT SELECT ON V_$ARCHIVED_LOG TO PROGATE_USER;
GRANT SELECT ON V_$TRANSACTION TO PROGATE_USER;
GRANT SELECT ON V_$DATABASE TO PROGATE_USER;

For procheck to work with an Oracle source, the following drivers are supported:

oracle, does not require third-party software.

godror, requires installation of the Oracle Instant Client. Once the downloaded archive is unpacked, add the path to the catalog to the LD_LIBRARY_PATH environment variable:

LD_LIBRARY_PATH=<path_to_oracle_client>

4.4.2. Setting up the Destination

Note

No specific setup is needed for the Parquet-format file destination.

4.4.2.1. Postgres Pro Destination

The following setup of the Postgres Pro destination is needed:

Install and enable the following extensions, available starting with Postgres Pro 16.4:

CREATE EXTENSION IF NOT EXISTS pgpro_bfile;
CREATE EXTENSION IF NOT EXISTS pgpro_sfile;
CREATE EXTENSION IF NOT EXISTS dbms_lob;

do
$$
BEGIN
    PERFORM sf_initialize();
EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE 'already initialized';
end;
$$;

To migrate BFILE objects, create the same aliases as in the source DB:

do
$$
    BEGIN
        PERFORM bfile_directory_create('ALIAS1', '/tmp/alias1');
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'alias1 already exists';
    end;
$$;

do
$$
    BEGIN
        PERFORM bfile_directory_create('ALIAS2', '/tmp/alias2');
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'alias2 already exists';
    end;
$$;

Transfer the schema from the source DB using ora2pgpro.

4.5. Cold and Hot Database Migration

Data migration can happen if the source database server is stopped for writing, that is, offloaded (cold DB), or when the source database server continues running normally (hot DB).

To migrate a cold DB, it suffices to run procopy. When the DB does not change, procopy can load the whole DB to the destination keeping the data consistent.

To perform a complete migration of a hot DB, both procopy and prosync are needed. For more details, see prosync Usage.

To migrate a hot DB, the destination DB must have unique keys and indexes. Otherwise, duplicate data can occur. To migrate a table without unique keys (heap), the cold DB must be migrated. For PostgreSQL/Postgres Pro sources, a snapshot can also be specified (see the section called “Configuring Tasks to Specify a Snapshot” for more details).

Chapter 5. ProGate Configuration Parameters

To configure procopy and prosync, a configuration file is used. To create the configuration file for each of these utilities, execute the config generate command of the respective utility and fill the configuration file generated with values.

procopy and prosync configuration files have a lot of common contents, and each has its specific section.

Important

If the names of schemas, tables, or columns in the source or destination DB are case-sensitive, enclose these names in double quotes.

5.1. procopy-Specific Configuration Parameters

The following is an example of the procopy configuration file fragment in the YAML format that lists procopy-specific configuration parameters:

version: 1
work_dir: ""
procopy_options:
    readers: 11
    loaders: 11
    batch_bytes: 1MiB
    quick_fetch_lob_size: 20MiB
    money_precision: 2
    window_rows_limit: 1000000
    disable_order_by: false
    inprogress_timeout: 10m0s
    show_load_percent: true
    snapshot_id: null
    parquet_options:
        path: ./parquet
        max_size: 300MiB
        page_size: 8KiB
        compression: GZIP
        row_group_size: 128MiB

For how to set values for time configuration parameters, refer to Section 4.3.6.

The following table explains procopy-specific configuration parameters:

Table 5.1. procopy-Specific Configuration Parameters

Name | Description | Default Value | Example
version | Configuration file version |  |
procopy_options | procopy global parameters |  |
procopy_options.readers | Number of parallel read processes. Each of them processes one task at a time until it is finished. | NumCPU/2 |
procopy_options.loaders | Number of parallel write processes. Each process retrieves batches from the queue and applies them to the destination. | NumCPU/2 |
procopy_options.load_max_retries | Number of batch sending attempts in case of error | 0 |
procopy_options.load_​retry_timeout | Time between batch sending attempts in case of error | 0 |
procopy_options.batch_bytes | Global limitation on the size of one batch, in bytes. The parameter value must be greater than 0. | 1MiB |

procopy_options.truncate

Global flag. If enabled, all destination tables will be truncated before the data loading. If not specified or set to false, destination tables will not be truncated.

Note that for non-completed tasks to migrate heaps or raw queries, it is required to set this value to true/false globally here or for the specific task in the tasks.truncate configuration parameter.

null

procopy_options.quick_fetch_lob_size
Threshold for the LOB size that defines how to fetch LOBs. If the size of a LOB is less than this value, the binary protocol will be used and all the data in the LOB will be loaded to the random-access memory (RAM). If the size of the LOB is greater than this value, the LOB will be read in parts. The minimum value must be 1024 bytes.
20MiB

procopy_options.bfile_copy | If specified, all the BFILE objects are copied as a whole form Oracle during the data loading. By default, only identifiers are copied, that is, the directory aliases and filenames. Note that prosync cannot load BFILE objects. | false |
procopy_options.problem_rows | Section for setting up the work with rows that cannot be inserted |  |
procopy_options.problem_rows.local | Section for the local setting up |  |
procopy_options.problem_rows.local.save_dir | Path to the directory where the problematic rows will be stored |  |
procopy_options.money_precision | Number of fractional digits in the destination database. Depends on the lc_monetary configuration parameter on the destination server. Meaningful only for Postgres Pro and the MONEY type. In other cases, leave the default value. | 2 |
procopy_options.window_rows_limit | Size of the sliding window. Number of rows selected at a time. |  |
procopy_options.disable_order_by | Disables loading of the data sorted by the unique primary key. Actually, the whole table is retrieved through a cursor. |  |
procopy_options.disable_index_hint | Disables index hints when creating select queries |  |
procopy_options.inprogress_timeout | Maximum time to read a data block | 1s |
procopy_options.show_load_percent | Output the percent of the task execution | true |
procopy_options.encoder_chan_size | Size of a buffer used by encoders | 10000 |
procopy_options.snapshot_id | Global identifier of the PostgreSQL/Postgres Pro snapshot. Used if the snapshot configuration parameter is needed, but not specified for the task. | 10000 |
procopy_options.sub_task_rows | Global setting of the number of rows in a task for parallel reading of tables. Used to split reading large tables into subtasks. The value of zero turns off splitting into subtasks. | 0 |
procopy_options.enable_auto_snapshot | Global flag to enable automatic creation of a snapshot in PostgreSQL/Postgres Pro. If true, a snapshot is created to be used to get data. Can be redefined at the task level using the tasks.enable_auto_snapshot configuration parameter. | false |

procopy_options.parquet_options
Section that specifies options related to the Parquet format destination

parquet_options:
    path: ./parquet
    max_size: 300MB
    page_size: 8kB
    compression: GZIP
    row_group_size: 128MB

procopy_options.parquet_options.path | Absolute or relative path to the directory where Parquet files will be stored | ./parquet |
procopy_options.parquet_options.max_size | Maximum size of a Parquet file. If a file size is to exceed this value, a new file is created. The minimum value must be 1024 bytes. | 300MiB |
procopy_options.parquet_options.page_size | Page size in a Parquet file. See https://parquet.apache.org/docs/file-format/configurations/ for the concept of a page in Parquet. The minimum value must be 1024 bytes. | 8KiB |
procopy_options.parquet_options.compression | Codec to compress data in a Parquet file. Supported codecs are UNCOMPRESSED, SNAPPY, GZIP, and LZ4. LZO, BROTLI, ZSTD, and LZ4_RAW codecs are not supported. See https://parquet.apache.org/docs/file-format/data-pages/compression/ for information on codecs for Parquet. | GZIP |
procopy_options.parquet_options.row_group_size | Size of a row group in a Parquet file. See https://parquet.apache.org/docs/file-format/configurations/ for more details. The minimum value must be 1024 bytes. | 128MiB |
procopy_options.to_char | Key-value dictionary of column types that must be converted to a string at the database side. Only applicable for Oracle. Each key contains the column type, and the value is the format. Key-value pairs are comma-separated. |  | "to_char": {"DATE":"YYYY-MM-DD HH24:MI:SS", "NUMBER":""}
procopy_options.null_char_replace | A global parameter to replace the null symbol (the symbol with the zero code). Can be redefined at the task level for each specific column using the null_char_replace field of the tasks.transform configuration parameter. | If the source is Oracle and destination is PostgreSQL or Postgres Pro, an empty string (""), otherwise nil | null_char_replace: "\n"

5.2. Configuration Parameters Shared by Applications

The following is an example of the procopy configuration file fragment in the YAML format that lists configuration parameters used both in procopy and prosync:

source:
    driver_name: oracle
    host: enter host
    port: 1521
    database: enter database
    username: enter username
    password: enter password if necessary
    max_conn: 50
    options:
        - PREFETCH_ROWS=500
destination:
    driver_name: postgresql
    host: enter host
    port: 5432
    database: enter database
    username: enter username
    password: enter password if necessary
    max_conn: 50
    options:
        - sslmode=disable
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
      snapshot_id: null
    - 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
    - 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

For how to set values for time configuration parameters, refer to Section 4.3.6.

The following table explains configuration parameters that are used both in procopy and prosync:

Table 5.2. Configuration Parameters Shared Between Applications

Name | Description | Default Value | Example
source | Parameters to connect to the source |  |

source.dsn
DSN for the connection. If specified, this parameter has precedence: it defines the actual settings.

For BiHA cluster:

dsn: "postgresql://db-biha-1:5432,
db-biha-2:5432,
db-biha-3:5432/postgres?user=user&
password=password"

source.driver_name | Driver for the connection. Possible values: oracle, postgres, parquet (although Parquet is only used as a destination), shardman. |  |
source.host | Host name or IP address |  |
source.port | Connection port |  |
source.database | Name of the database to connect to |  |
source.username | Connection login |  |
source.password | Connection password. Leave empty if the password is not required. |  |
source.max_conn | Maximum number of open connections | 50 |

source.options
Additional connection parameters

options:
- PREFETCH_ROWS=500
- sslmode=disable

source.shards
Connection parameters for Shardman, where each key is a shard name and the corresponding value is the connection parameters for the shard. Only used when source.driver_name is shardman. For each connection inside source.shards, the dsn parameter is only used.

shard-1:
    dsn: "postgresql://db-biha-1:5432,
    db-biha-2:5432,
    db-biha-3:5432/postgres?user=user&
    password=password"
shard-2:
    dsn: "postgresql://db-biha-4:5432,
    db-biha-5:5432,
    db-biha-6:5432/postgres?user=user&
    password=password"

destination | Parameters to connect to the destination |  |

destination.dsn
DSN for the connection. If specified, this parameter has precedence: it defines the actual settings.

For BiHA cluster:

dsn: "postgresql://db-biha-1:5432,
db-biha-2:5432,
db-biha-3:5432/postgres?user=user&
password=password"

destination.driver_name | Driver for the connection. Possible values: oracle, postgres, parquet, shardman (although shardman is only used as a source). |  |
destination.host | Host name or IP address |  |
destination.port | Connection port |  |
destination.database | Name of the database to connect to |  |
destination.username | Connection login |  |
destination.password | Connection password. Leave empty if the password is not required. |  |
destination.max_conn | Maximum number of connections allowed for the connection pool | 50 |

destination.options
Additional connection parameters

options:
- PREFETCH_ROWS=500
- sslmode=disable

destination.shards
Connection parameters for Shardman, where each key is a shard name and the corresponding value is the connection parameters for the shard. Only used when destination.​driver_name is shardman. For each connection inside destination.​shards, the dsn parameter is only used.

shard-1:
    dsn: "postgresql://db-biha-1:5432,
    db-biha-2:5432,
    db-biha-3:5432/postgres?user=user&
    password=password"
shard-2:
    dsn: "postgresql://db-biha-4:5432,
    db-biha-5:5432,
    db-biha-6:5432/postgres?user=user&
    password=password"

tasks | List of tasks. A task can be of the Query, Schema, or Table type. For more details, see the section called “Types of Tasks”. |  |
tasks.id | Task name. Must be unique. It is recommended to avoid changing it between tasks. |  | ID: "task_schema_1"
tasks.table | Section for a task of the Table type. For more details, see the section called “Types of Tasks”. |  |

tasks.table.source_table
Qualified name of the source table: <schema>.<table>. The names that are specified without quotes are meant to be case-insensitive.

source_table:
"SYSTEM"."PRODUCTS"

tasks.table.destination_table
Qualified name of the destination table: <schema>.<table>. The names that are specified without quotes are meant to be case-insensitive.

destination_table:
"public"."products"

tasks.table.exclude_columns
List of columns to exclude from data loading. List columns with case-sensitive names exactly as they are called in the source DB. Quotes are not needed. Incompatible with tasks.table.​include_columns.

exclude_columns:
- COLUMN_1
- COLUMN_2

tasks.table.include_columns
List of columns such that only these columns will be loaded. List columns with case-sensitive names exactly as they are called in the source DB. Quotes are not needed. Incompatible with tasks.table.​exclude_columns.

include_columns:
- COLUMN_1
- COLUMN_2

tasks.table.column_mapping
Mapping of column names in the source database to names in the destination database if these names are different, in the format: “source_name”: “destination_name”. Specify columns with case-sensitive names exactly as they are called in the appropriate DB. Quotes are not needed.

column_mapping:
"COLUMN_1": "new_column_1"
"COLUMN_2": "new_column_2"

tasks.table.where | Filtering conditions for the source table, which enables limiting the number of rows to load. An SQL statement that returns boolean. |  | where: "(COL1 IS NULL OR COL1 > 10) AND COL2 <> 'value'"
tasks.table.uniq_keys | Unique key to be used for the selection. If not specified, the most suitable key will be chosen automatically. |  |
tasks.table.last_row | Deprecated |  |

tasks.table.index_hint
A hint for an index name to be used for an index scan. In some situations, the index that is actually chosen for the index scan can differ from the one specified.

index_hint: "/+ INDEX (employees emp_department_ix)/" # Oracle
index_hint: "/+ IndexScan(tableName indexName)/" # Postgres Pro
index_hint: "/+ IndexScanOnly(tableName indexName)/" # Postgres Pro

tasks.table.disable_order_by | Disables sorting during a selection. If not specified, the global setting from procopy_options is used. |  |
tasks.query | Section for a task of the Query type. For more details, see the section called “Types of Tasks”. |  |

tasks.query.sql
User's SQL query for the task

sql: "SELECT id, name FROM users ORDER BY id"

tasks.query.destination_table
Qualified name of the destination table: <schema>.<table>. If the schema name or table name is specified without quotes, this name is meant to be case-insensitive.

destination_table:
"public"."products"

tasks.query.destination_columns
List of destination columns where data will be inserted. Column names must be in the same order as in the query. List columns with case-sensitive names exactly as they are called in the destination DB. Quotes are not needed.

destination_columns
- id
- login

tasks.query.destination_column_types
Types of destination columns where data will be inserted. Must be listed in the same order as in tasks.query.​destination_​columns. Only used to migrate data to the Parquet format. Type names must follow the types of the source DB. Therefore, Parquet types should not be used here.

For migration

PostgreSQL -> Parquet:

destination_column_​types:
- text
- integer
- boolean

tasks.schema | Section for a task of the Schema type. For more details, see the section called “Types of Tasks”. |  |

tasks.schema.source_schema
Name of the source schema to load. Must be exactly as in the database. No quotes are needed.

source_schema: SYSTEM
source_schema: BOOK.shelves

tasks.schema.destination_schema
Name of the destination schema. Must be exactly as in the database. No quotes are needed.

destination_schema: public
destination_schema: book.SHELVES

tasks.schema.exclude_tables
List of tables to exclude from the data loading. List tables with case-sensitive names exactly as they are called in the source DB. Quotes are not needed. Incompatible with tasks.schema.​include_tables.

exclude_tables:
- PRODUCTS
- TOKENS

tasks.schema.include_tables
List of tables such that only these tables will be loaded. List tables with case-sensitive names exactly as they are called in the source DB. Quotes are not needed. Incompatible with tasks.schema.​exclude_tables.

include_tables:
- PRODUCTS
- TOKENS

tasks.schema.table_mapping
Mapping of table names in the source database to names in the destination database if these names are different, in the format <source_name>: <destination_name>. List tables with case-sensitive names exactly as they are called in the appropriate DB. Quotes are not needed.

table_mapping:
  "source_name_tab1": "destination_name_tab1"
  "source_name_tab2": "destination_name_tab2"

tasks.batch_bytes | Limitation on the batch size for the current task, in bytes. If not specified, the global limitation procopy_options.batch_bytes is used. |  |
tasks.truncate | If enabled, a destination table (tables) will be truncated before the data loading. If not specified, the global flag procopy_options.​truncate is used. |  |

tasks.transform
Rules to transform null values of the specified columns. Each rule can contain the following fields:

null_to_value contains the value to which null values of the column will be converted during the data loading.

values_to_null contains an array of column values to be converted to null during the data loading.

null_char_replace contains a string to replace the symbol with the zero code (\0x00, CHR(0)). It can be one symbol, a longer string, or an empty string.

For tasks of the Schema type, if keys do not contain dots, all the columns with this name in all tables will be processed according to the specified rules. And if keys contain a dot, the first dot will be considered as a separator of the table name from the column name, so the column in only one table will be processed.

It is important to specify tables and columns in the same case as in the destination DB.

transform:
column1:
null_to_value: "UNKNOWN"
null_char_replace: "\n"
table1.column2: # for tasks of the Schema type
values_to_null: ["NOTSET", "notset", "NotSet"]

tasks.disable_index_hint | Disables index hints when creating select queries. If not specified, the global flag procopy_options.disable_index_hint is used. |  |
tasks.window_rows | Number of rows selected at a time. If not specified, the global value of the procopy_options.window_rows is used. |  |
tasks.encoder_chan_size | Size of a buffer used by encoders. If not specified, the global value of the procopy_options.encoder_chan_size is used. |  |
tasks.snapshot_id | Identifier of the PostgreSQL/Postgres Pro snapshot. If not specified, the global value of the procopy_options.​snapshot_id is used. |  |
tasks.enable_auto_snapshot | If true, a snapshot is created in PostgreSQL/Postgres Pro to be used to get data. |  |
task.sub_task_rows | Number of rows in a task for parallel reading of tables. Used to split reading large tables into subtasks. The value of zero turns off splitting into subtasks. | 0 |
tx_storage | Parameters to connect to the supporting DB to be used to store transactions. If not specified, the destination DB is used. |  |

tx_storage.dsn
DSN for the connection. If specified, this parameter has precedence: it defines the actual settings.

For BiHA cluster:

dsn: "postgresql://db-biha-1:5432,
db-biha-2:5432,
db-biha-3:5432/postgres?user=user&
password=password"

tx_storage.driver_name | Driver for the connection. Possible values: oracle, postgres, parquet (only for destination), shardman (only for source). |  |
tx_storage.host | Host name or IP address |  |
tx_storage.port | Connection port |  |
tx_storage.database | Name of the database to connect to |  |
tx_storage.username | Connection login |  |
tx_storage.password | Connection password. Leave empty if the password is not required. |  |
tx_storage.max_conn | Maximum number of open connections | 50 |

tx_storage.options
Additional connection parameters

options:
- PREFETCH_ROWS=500
- sslmode=disable

tx_storage.shards
Connection parameters for Shardman, where each key is a shard name and the corresponding value is the connection parameters for the shard. Only used when tx_storage.driver_name is shardman. For each connection inside tx_storage.shards, the dsn parameter is only used.

shard-1:
    dsn: "postgresql://db-biha-1:5432,
    db-biha-2:5432,
    db-biha-3:5432/postgres?user=user&
    password=password"
shard-2:
    dsn: "postgresql://db-biha-4:5432,
    db-biha-5:5432,
    db-biha-6:5432/postgres?user=user&
    password=password"

storage | Parameters to connect to the supporting DB to be used to store the prosync state. If not specified, the destination DB is used. |  |

storage.dsn
DSN for the connection. If specified, this parameter has precedence: it defines the actual settings.

For BiHA cluster:

dsn: "postgresql://db-biha-1:5432,
db-biha-2:5432,
db-biha-3:5432/postgres?user=user&
password=password"

storage.driver_name | Driver for the connection. Possible values: oracle, postgres, parquet (only for destination), shardman (only for source). |  |
storage.host | Host name or IP address |  |
storage.port | Connection port |  |
storage.database | Name of the database to connect to |  |
storage.username | Connection login |  |
storage.password | Connection password. Leave empty if the password is not required. |  |
storage.max_conn | Maximum number of open connections | 50 |

storage.options
Additional connection parameters

options:
- PREFETCH_ROWS=500
- sslmode=disable

storage.shards
Connection parameters for Shardman, where each key is a shard name and the value is the connection parameters for the shard. Only used when storage.driver_name is shardman. For each connection inside storage.shards, the dsn parameter is only used.

shard-1:
    dsn: "postgresql://db-biha-1:5432,
    db-biha-2:5432,
    db-biha-3:5432/postgres?user=user&
    password=password"
shard-2:
    dsn: "postgresql://db-biha-4:5432,
    db-biha-5:5432,
    db-biha-6:5432/postgres?user=user&
    password=password"

5.3. prosync-Specific Configuration Parameters

The following is an example of the prosync configuration file fragment in the YAML format that lists prosync-specific configuration parameters:

prosync_options:
    export_batch_size: 10000000
    max_export_batch_size: 100000000
    process_batch_size: 10000
    batch_save_after_timeout: 5m0s
    import_batch_size: 1000000
    prefetch_rows: 250
    use_online_logs_unsafe: false
    validate_log_sequence: true
    lob_write_size: 30000
    operation_count_for_external_storage: 5000

For how to set values for time configuration parameters, refer to Section 4.3.6.

Table 5.3. prosync Configuration Parameters

Name | Description | Default Value | Example
prosync_options | prosync-specific parameters |  |
prosync_options.export_batch_size | Batch size for prosync reading from Oracle LogMiner |  |
prosync_options.max_export_batch_size | Maximum size to which the batch size can be increased when reading. If the maximum size is reached, prosync terminates with an error. This can happen if there is an open transaction that would not close. The user must resolve this issue. |  |
prosync_options.process_batch_size | Batch size for processing a transaction. This parameter is for internal tuning of the application. | 10000 |
prosync_options.batch_save_after_timeout | Time interval after which a batch will be written to the destination DB if its size is still less than max_export_batch_size. The value of zero disables time-based flush to the destination DB. | 5s | 5m
prosync_options.import_batch_size | Batch size for inserting data. Zero value means that the data will be inserted as new data occurs. |  |
prosync_options.prefetch_rows | Deprecated. Number of rows that the driver retrieves from the databases at a time. |  |
prosync_options.use_online_logs_unsafe | Read the changes from archive and online REDO LOGS. The flag enables getting the changes as soon as they occur in online logs. Not recommended to use for reliability. Because of how Oracle writes logs, some operations can be missed. | false |
prosync_options.validate_log_sequence | Validate a sequence of log files. If a log file is missing, prosync terminates with an error. If losing some data is OK, set this parameter to false. | true |
prosync_options.lob_write_size | Maximum number of bytes to write at a time when writing LOBs |  |
prosync_options.operation_count_for_external_storage | Number of operations in one transaction that causes writing of intermediate data to disk |  |
prosync_options.transfer_id | Data transfer identifier. Used to create names of slots for replication and publications. This parameter is required. |  | d87708a4-c35e-4379-8845-d173683e5385

prosync_options.null_char_replace
String to replace the null character, that is, the symbol with the zero code. This is a global setting, which can be overridden for each column with the value of tasks.transform.​column_name.​null_char_replace.

nil, but for the Oracle source and PostgreSQL/Postgres Pro destination:

null_char_replace:
  replace: ""
  destination_types: ["varchar", "char", "bpchar",
  "text", "json", "jsonb", "xml"]

prosync_options.null_char_replace.replace | Character or string to replace \0x0000. If not specified, no replacement will be done. | nil | replace: "\n"

prosync_options.null_char_replace.destination_types
Specifies the types in the destination database to replace null characters for. Character or string to replace \0x0000. If not specified, no replacement will be done.
[]

destination_types: ["varchar", "char",
"text", "json", "jsonb", "xml"]

prosync_options.readers | Number of readers from LogMiner. If set to a positive value, the setting of use_online_logs_unsafe is ignored. | 0 — one-threaded slow reading, which is, however, compatible with use_online_logs_unsafe |
prosync_options.use_failover_slots | Whether to use failover replication slots. Only applicable to the PostgreSQL/Postgres Pro 17.0 or higher source. | false |
prosync_options.use_replica_auto_sync_slots | Enables prosync logic for propagation of replication slots. Use if you need failover replication slots on the cluster, but have PostgreSQL/Postgres Pro lower than 17.0 or if you need to read from a replica (alone or not). | false |
prosync_options.postgres_slot_advance_interval | How often a slot will be moved on the whole cluster if prosync_options.use_replica_auto_sync_slots is enabled. Must be greater than 100 milliseconds. | 5s | 500ms
prosync_options.loaders | Number of workers that write to the destination. If not specified or equals zero, one worker will be used. | 0 |
prosync_options.truncate_process | Whether to process the truncate operations. If true, truncate operations will be processed for the destination table in the destination DB. | false |
prosync_options.get_replication_message_timeout | Timeout for receiving messages from the PostgreSQL/Postgres Pro source DB. Must be greater than or equal to wal_sender_timeout. | 60s |
prosync_options.dump_mode | Dump mode. If enabled, operations are saved to a dump table rather than performed. | false |

5.4. procheck Configuration Parameters

The following is an example of the procheck configuration file in the YAML format:

options:
  skip_lobs: true
  skip_auto_count_test: true
  skip_passed_tables: true

source:
  driver_name: oracle
  host: 192.168.23.251
  port: 1523
  database: db18c
  username: pg_migr
  password: <password>

destination:
  driver_name: pgx
  host: 192.168.21.191
  port: 5432
  database: parus
  username: data_quality
  password: <password>
  max_conn: 10

comparison_rules:
  schema1:
    source_schema: xpress
    destination_schema: xpress
    exclude_tables: [unnecessary_table]
    tests:
      - name: test1
        tables: [all]
        type: count
      - name: test2
        tables: [all]
        type: max_id
  schema2:
    source_schema: src_schema
    destination_schema: dst_schema
    table_mapping:
      src_table1: dst_table1
      src_table2: dst_table2
      src_table3: dst_table3
    column_mapping:
      src_table1:
        src_column1: dst_column1
        src_column2: dst_column2
    skip_lobs:
      table1: true
      table2: false
    exclude_columns:
      table1: [column1, column2]
      table2: [column3, column4]
    source_where:
      src_table4: id >= 100 AND id  < 1000
    tests:
      - name: test1
        tables: [src_table1, src_table2]
        type: count
      - name: test2
        tables: [all]
        exclude_tables: [src_table1]
        exclude_columns:
          table3: [column5]
        type: all_rows
      - name: test3
        tables: [all_other]
        type: max_id
        source_where:
          src_table3: src_id >= 100 AND src_id  < 1000
        destination_where:
          dst_table3: dst_id >= 100 AND dst_id  < 1000
      - name: test4
        tables: ['@filename.txt']
        type: table_list
      - name: test5
        type: query
        source_query: SELECT col1, COUNT(*) FROM src_schema.table1 GROUP BY col1
        destination_query: '@query2.sql'

The table below explains procheck configuration parameters. Note that some global configuration parameters can be redefined at the level of the schema and some parameters can be specified both at the schema level and for individual tests. Lower-level configuration parameters with the same name redefine the higher-level ones. See an example of the configuration file:

Table 5.4. procheck Configuration Parameters

Name | Description | Default Value | Example
options | Global configuration parameters |  |
options.skip_lobs | Exclude columns of the CLOB, BLOB, BFILE, and SFILE types from row-wise comparison. For PostgreSQL, also exclude columns of types specified by the pg_hashed_types configuration parameter. The global value can be redefined for specific tables. | false | skip_lobs: true

options.pg_hashed_types
Defines additional PostgreSQL data types that are compared by computing their MD5 hashes, in addition to those that are always compared this way, such as SFILE or types defined in the dbms_lob extension. The global value can be redefined for specific tables at the schema level or for an individual test. If the value is an empty list, the default value is used.
[BYTEA, TEXT]

options.skip_auto_count_test | Turns off automatic execution of count tests for tables for which tests were skipped due to lack of a required key | false |
options.skip_passed_tables | Turns off the output of detailed information on tables with the PASSED test status to only output the total number of such tables. | false |
options.all_rows_max_mistakes | Minimum number of errors that interrupts execution of all_rows tests | 0 — the tests are performed for all rows |
options.chunk_size | Approximate number of rows in chunks into which the table is split during execution of all_rows tests. When the value is zero, the default value is used. If the value is less than zero, tables are not split into chunks. | 100000 |
options.max_parallel_chunks | Maximum number of table chunks compared in parallel for all_rows tests. If the value is less than or equal to zero, the default value is used. | 10 |
options.max_parallel_tables | Maximum number of tables compared in parallel. If the value is less than or equal to zero, the default value is used. | 50 |
options.ora_empty_string_is_null | Defines processing of Oracle empty strings. If true, an empty string is equivalent to NULL. | true |
options.ora_true_values | Array of string values equivalent to boolean true in Oracle | [Y] | [Y, YES]
options.ora_false_values | Array of string values equivalent to boolean false in Oracle | [N] | [N, NO]
options.comparison_accuracy | Minimum number of consequtive 9 or 0 in the fractional part that causes rounding of the number to the previous decimal place | 0 — no rounding |
options.round_places | Number of decimal places to which the number is rounded when compared. If the value is less than zero, the whole-number part is rounded to the nearest 10^(-round_places) | 0 — no rounding |
options.ora_string_types | List of Oracle data types for which COLLATE must be specified when sorting | [CHAR, VARCHAR2] |
options.pg_string_types | List of PostgreSQL data types for which COLLATE must be specified when sorting | [BPCHAR, VARCHAR] |
options.skip_type_mismatch | Skip errors of impossible comparison of different types in all_rows tests | false |
source | Configuration parameters to set up the connection to the source DB |  |
source.driver_name | Driver for the connection. Possible values: oracle — the godror driver, which works through the Oracle Instant Client, or go-ora — the go-ora driver that is developed in Go. |  |
source.host | Host name |  |
source.port | Connection port | 1521 for Oracle |
source.database | Name of the database to connect to |  |
source.username | Connection login |  |
source.password | Connection password |  |
source.max_conn | Maximum number of connections to DB. If the value is less than or equal to zero, the number is unlimited. | Unlimited |
destination | Configuration parameters to set up the connection to the destination DB |  |
destination.driver_name | Driver for the connection. Possible values: postgresql or the pgx alias — the pgx driver. |  |
destination.host | Host name |  |
destination.port | Connection port | 5432 for PostgreSQL |
destination.database | Name of the database to connect to |  |
destination.username | Connection login |  |
destination.password | Connection password |  |
destination.max_conn | Maximum number of connections to DB. If the value is less than or equal to zero, the number is unlimited. | Unlimited |
comparison_rules | Data comparison rules |  |
comparison_rules.source_schema | Schema name in the source DB |  |
comparison_rules.destination_schema | Schema name in the destination DB |  |

comparison_rules.exclude_tables
Comma-separated list of tables excluded from all tests. Enclosed in square brackets. Can contain a special value, such as '@filename', which means that the list should be read from the file specified by this value.

[unnecessary_table] or ['@filename.txt']

comparison_rules.table_mapping
Mapping of table names

src_table1: dst_table1
src_table2: dst_table2

comparison_rules.exclude_columns
List of table columns to be excluded from all tests.

exclude_columns:
  table1: [column1, column2]
  table2: [column3, column4]

comparison_rules.column_mapping
Mapping of column names in tables

src_table1:
  src_column1: dst_column1
  src_column2: dst_column2

comparison_rules.skip_lobs
Redefines the skip_lobs global parameter for single tables

skip_lobs:
  table1: true
  table2: false

comparison_rules.source_where | Filtering condition for the source table. An SQL statement that returns boolean. |  | src_table3: src_id >= 100 AND src_id < 1000
comparison_rules.destination_where | Filtering condition for the destination table. An SQL statement that returns boolean. |  | dst_table3: src_id >= 100 AND src_id < 1000
comparison_rules.tests | Tests for the comparison rule |  |
comparison_rules.tests.name | Name of the test |  |
comparison_rules.tests.type | Type of the test. See Table 6.1 for the list of possible values. |  |

comparison_rules.tests.tables
Comma-separated list of tables to perform the test for. Enclosed in square brackets. Can contain special values:

all — all tables in the schema

all_other — all tables in the schema that are not explicilty listed in test descriptions

'@filename' — the list should be read from the file specified by this value

[src_table1, src_table2] or [all_other]

comparison_rules.tests.source_query | Text of the source query for a test of the query type (see Table 6.1 for details). To read the query from a file, specify the value as '@file_name'. |  | SELECT col1, COUNT(*) FROM src_schema.table1 GROUP BY col1
comparison_rules.tests.destination_query | Text of the destination query for a test of the query type (see Table 6.1 for details). To read the query from a file, specify the value as '@file_name'. |  | '@destination_query.sql'

Chapter 6. Reference

procopy

procopy — a utility for fast data loading from a source database to a destination database

Synopsis

procopy config generate [option...]

procopy load [option...]

Description

procopy is a utility for fast loading of data from a source database, such as Oracle, PostgreSQL, MySQL, etc., to a destination database, such as PostgreSQL, Postgres Pro Enterprise, etc. For the sources and destinations that are currently supported, see Table 4.1.

procopy is not responsible for:

Database schema transfer.

Replaying the changes that occurred during data loading.

Retaining the data consistency. Therefore, it is recommended to remove existing constraints where possible and bring them back when prosync finishes.

Data verification after loading.

procopy is developed in Go for efficient resource utilization when migrating databases of the 10 TB size or larger. At a high level, procopy consists of a pool of Readers, pool of Loaders, and a built-in bus used to exchange data between Readers and Loaders:

Special Considerations

Because of the binary load format for COPY, addition of new types requires the support of the corresponding encoders.

Loading of BLOB and BFILE objects is slower than loading of other objects because at least two SQL commands are needed for each row. This is much slower than just using COPY.

Limitations

procopy does not support views and materialized views as sources.

Usage

During ProGate operation, tasks of different types are performed. A task can be of the Query, Schema, or Table type. The tasks section in the configuration file enables you to configure procopy tasks.

Types of Tasks

Table.

Tasks of this type are used to load a specific table with a possibility to filter and map columns.

Schema.

Tasks of this type are used to load the whole schemas with a possibility to filter and map tables.

Query.

In tasks of this type, you can specify a statement that defines the selection from the source database.

Important

In procopy, tasks of the Query type have some limitations. They cannot be continued after a restart.

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:

In case of incorrect names of configuration parameters, procopy completes with an error.

Values of configuration parameters are checked (for example, correctness of IP addresses and host names).

If not all the required configuration parameters are specified, procopy completes with an error.

If incompatible configuration parameters are specified, procopy completes with an error.

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:

...
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.

Note

Rows from *_index.csv files will be reloaded. New rows that occurred in tables will not be applied.

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:

Setting up the read and write processes

Configuring splitting tasks into subtasks

Processing partitioned tables

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:

Only choose specific columns to load using the include_columns setting. All the columns are assumed by default.

Exclude columns from loading using the exclude_columns setting.

Rename columns to be loaded using the column_mapping setting.

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:

Only choose specific tables to load using the include_tables setting. All the tables are assumed by default.

Exclude tables from loading using the exclude_tables setting.

Rename tables to be loaded using the table_mapping setting.

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:

In the source DB, start the transaction with the REPEATABLE READ isolation level.

Get the snapshot identifier with the query

SELECT pg_export_snapshot()

In the section of the configuration file related to this task, specify the returned value for snapshot_id.

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.

Command-Line Reference

This section describes procopy commands. Optional parameters are enclosed in square brackets.

config generate

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

Generates a procopy configuration file.

-f json|yaml
--format json|yaml

Specifies the format of the configuration file: JSON or YAML. The default is YAML.

-o filename
--output filename

Specifies the filename of the configuration file. By default, the configuration file will be output to stdout.

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.

-f filename
--file filename

Name of the configuration file. Required.

--log-level error|warn|info|debug

Logging level. The default is info.

Important

Detailed logging can affect the performance. So only choose debug when you are researching errors.

-s count_of_batches
--queue-size count_of_batches

Size of the queue between Readers and Loaders to be used by procopy. The default is 128.

Important

This value can excessively increase the memory size. It is usually enough to set it to NumLoaders * 2. If procopy starts to slow down when it loads data to the destination, the memory size can grow more than by count_of_batches * batch_bytes bytes.

-q
--quiet

Turns off output of the procopy progress.

-r TIMESTAMP
--reload-from TIMESTAMP

Launches a repeated load of problematic rows. TIMESTAMP is the timestamp of one of the previous launches of loading. If this option is specified, procopy tries to only load rows that had issues during loading with the TIMESTAMP. You can learn this timestamp from logs or find it in the directory specified in the problem_rows.local.save_dir configuration parameter.

--dry-run

Turns off writing to the destination. procopy will perform all the operations except writing. This flag is useful for checking the performance of writing.

--read-only

Turns off all the procopy logic except reading from the source. This flag is useful for checking the performance of reading.

prosync

prosync — utility to replay changes to a source database on a destination database of a different type

Synopsis

prosync config generate [option...]

prosync init [option...]

prosync run [option...]

prosync complete [option...]

Description

prosync is a utility to perform the change data capture (CDC) of a source database and replay the changes on a destination database of a different type.

prosync is not responsible for:

Database schema transfer.

Transferring data that is missing from log files.

Data verification after the data transfer.

Supported Operations

DML: INSERT, UPDATE, DELETE

DDL: TRUNCATE

Usage

Migrating the Database

prosync participates in the hot DB migration (see Section 4.5 for more details).

Preparing for a Migration of a Hot DB

To prepare for the migration of a hot DB, perform the following steps:

Prepare and set up the destination DB for the migration:

Create the necessary schemas and tables.

Remove or turn off triggers.

Generate the configuration file:

prosync config generate -o filename

Update the configuration file with appropriate values.

Initialize prosync with the command:

prosync init -f filename

For an Oracle source, prosync will store the current SCN to start the subsequent replication with this value. For a PostgreSQL/Postgres Pro source, prosync will create a logical replication slot for subsequent reading of changes.

Performing the Migration of a Hot DB

Perform the migration as follows:

Launch the procopy load:

procopy load -f filename

When it finishes, launch the prosync replay of changes:

prosync run -f filename

Wait until the difference between Parsing SCN and Oracle latest SCN decreases to an admissible value. For the migration of a hot DB, it is critical for this difference to decrease:

Parsing SCN          3671057
Oracle latest SCN    4995972

Disable the load on the source DB and wait until Parsing SCN and Oracle latest SCN virtually stop changing. Stop prosync.

Note

Changes always occur in Oracle even if nobody changes the data, so Oracle latest SCN is continuously growing. Therefore, the difference between Parsing SCN and Oracle latest SCN never reaches zero.

Excluding Columns from Tasks of the Table Type

When executing tasks of the TABLE type (see the section called “Types of Tasks” for details) during data migration involving prosync, do not exclude (through exclude_columns or include_columns) columns that are used by LogMiner as a row key. Otherwise, executing DELETE or UPDATE commands can change more than one row.

For tables without keys (heap), do not exclude any columns as LogMiner uses the whole row for identification.

Note

These rules do not apply for data migrations that are only performed by procopy.

Notes for PostgreSQL/Postgres Pro Sources

When migrating the PostgreSQL/Postgres Pro DB, a replication slot is needed for running prosync replay of changes. Therefore:

Before launching the replay of changes by prosync run, execute the init command, which will create the replication slot.

When run finishes, execute complete command to clear the replication slot.

Command-Line Reference

This section describes prosync commands. Optional parameters are enclosed in square brackets.

config generate

prosync config generate [-f|--format json|yaml]
[-o|--output filename] [-c|--source_config]

Generates a prosync configuration file.

-f json|yaml
--format json|yaml

Specifies the format of the configuration file: JSON or YAML. The default is YAML.

-o filename
--output filename

Specifies the filename of the configuration file. By default, the configuration file will be output to stdout.

-c
--source-config

Use the procopy configuration file as the basis of the prosync configuration file and add missing options there.

init

prosync init -f|--file filename [-u|--update-config]

Gets the current SCN from the source database.

-f filename
--file filename

Name of the configuration file, where the connection options are specified.

-u
--update-config

Use the procopy configuration file as the basis and add missing options there.

run

prosync run -f|--file filename
[--log-level error|warn|info|debug] [--dry-run|--read-only]
[-c|--clear] [--with-table-stats]

Launches the replay of changes to the source DB on the destination DB.

-f filename
--file filename

Name of the configuration file, where the connection options are specified.

--log-level error|warn|info|debug

Logging level. The default is info.

--dry-run

Turns off writing to the destination. prosync will perform all the operations except writing. This flag is useful for checking the performance of writing.

--read-only

Turns off all the prosync logic except reading from the source. This flag is useful for checking the performance of reading.

-c
--clear

Clear the screen before each next output of the statistics. When enabled, it is recommended to output logs to a file, as follows:

prosync run ... -c 2>prosync.log

--with-table-stats

Output the extended statistics on tables.

complete

prosync complete [-f filename]

Clears the replication slot used by run. The slot name is determined from the configuration file, whose default name is config.yaml.

procheck

procheck — utility to verify data after a migration

Synopsis

procheck [option...]

Description

procheck is a utility for data verification using specialized tests. It is intended to be used after a data migration performed by procopy and prosync. The configuration parameters, specified in the configuration file, define how the tests are performed.

procheck performs the following verification:

Compares the values of unique identifiers in source and destination tables.

Compares the number of rows in tables.

Compares data row-by-row.

Compares query results.

Usage

Generating the Configuration File

To generate a sample configuration file with the config_file name, execute the command:

procheck --generate_config config_file

or

procheck --generate_config_db config_file

You can later update configuration values there.

Launching the DB Verification

To launch the DB verification that is specified in the config_file configuration file, execute the command:

procheck -c config_file

Configuring the DB Connection

The connection to the source DB is specified in the source section of the configuration file, and the connection to the destination DB is specified in the destination section of the configuration file. See also an example of the configuration file.

Specifying the Tests to Perform

Tests that procheck performs are defined in the comparison_rules section of the configuration file. See also an example of the configuration file.

For each schema, the source and destination databases must be specified, as well as a list of tests.

To exclude unnecessary tables from tests, use the optional configuration parameter exclude_tables, which can be defined for the whole schema and for an individual test.

To exclude table columns from row-wise comparison, use the optional configuration parameter exclude_columns, which can also be defined for the whole schema and for an individual test.

You can define the mapping of table names between the source and destination databases at the schema level. To do this, use the optional configuration parameter table-mapping.

To define the mapping of column names between the source and destination databases at the schema level, use the optional configuration parameter column-mapping.

To specify tables to be included in tests, use the required configuration parameter tables, which should be defined for each test.

You can set the the optional global parameter skip_lobs to specify whether to compare large objects for specific tables. The global parameter can be redefined for specific tables using the skip_lobs parameter in the comparison_rules section.

Types of tests are listed in the table below:

Table 6.1. procheck Types of Tests

Configuration value of type | Description
min_id | Comparison of minimum values of a unique key. Only performed for tables with a unique key that consists of one column.
max_id | Comparison of maximum values of a unique key. Only performed for tables with a unique key that consists of one column.
minmax_id | Comparison of minimum and maximum values of a unique key. Only performed for tables with a unique key that consists of one column.
count | Comparison of the number of rows in tables.

query
Comparison of results of user queries to the source and destination DBs. The queries are specified in the source_query and destination_query parameters. When writing the queries, you must ensure the same order of rows and columns in the results. Column names do not matter for the comparison. The tables and exclude_tables parameters are not used for tests of this type.

all_rows
Comparison of all rows in tables. For columns of the BLOB, CLOB, BYTEA, BFILE, SFILE, and TEXT types, values of the MD5 hash are compared. If the value of skip_lobs is true, comparison of these types of columns is skipped. The test is only performed if the table has a unique key. If this key is numeric and consists of one column, the table can be split into chunks by the value of this key to be compared in parallel. The maximum number of rows in a chunk and the maximum number of chunks processed in parallel are controlled by the chunk_size and max_parallel_chunks parameters, respectively.

table_list | Verifying the availability of tables in DB.
col_types | Verifying the comparability of data types in the source and destination databases.

For tables for which tests were skipped because of the lack of a required key, count tests are automatically performed. If the count test is already specified in the configuration file for some table, the test is not repeated. Automatically preforming count tests can be turned off with the skip_auto_count_test configuration parameter.

Specifying the Row Filtering

You can filter table rows to perform tests on. To specify filters for tables of the source DB, use the source_where configuration parameter. To specify filters for tables of the destination DB, use the destination_where configuration parameter. Both can be specified either at the schema level or for an individual test. Filters specified for individual tests override those specified at the schema level. The filter condition can be any SQL statement that returns the boolean type.

You need to specify destination_where only if column names in the filter for the destination DB differ from column names in the source DB. In this case, the table name in the destination DB must be specified.

Return Codes

procheck may return one of the following codes:

0 — Successful termination. All the tests have been successfully passed, that is, have the PASSED status.

1 — The application terminated with an error.

3 — The application terminated due to an error in the configuration or in command-line options.

4 — The application terminated due to an error of the database access.

5 — The application terminated due to an error of the file input/output.

6 — The application terminated due to an error of the terminal input/output.

7 — Normal termination, but at least one test completed with the ERROR status.

8 — Normal termination, all the tests completed without errors, but at least one test completed with the FAILED status.

Command-Line Reference

This section describes procheck commands. Optional parameters are enclosed in square brackets.

procheck

procheck -c|--generate_config config_file|
--generate_config_db config_file
[--source_snapshot_id identifier]
[--destination_snapshot_id identifier]
[--json] [-v|--version]
[config_options] [output_control_options]

Performs the tests if the -c option is specified and generates a procheck configuration file if the --generate_config option is specified. config_file specifies the name of the configuration file. If the --generate_config_db option is specified, the configuration file is created with comparison rules corresponding to the source database structure.

--source_snapshot_id, --destination_snapshot_id

Verify data from the source or destination snapshot specified by identifier. Only applicable for PostgreSQL/Postgres Pro.

--json

Output the results of tests in the JSON format and save them to the config_file_results.json file. By default, the results in a plain-text format are saved to the config_file_results.txt file and output to the terminal.

-v
--version

Output the procheck version.

Command-Line Configuration Options

Additionally, command-line configuration options can be specified, which override respective settings in the configuration file:

-p
--skip_passed_tables

Sets the skip_passed_tables configuration parameter to true, which means no display of detailed information on tables with the PASSED test status.

-s integer
--chunk_size integer

Sets the value of the chunk_size configuration parameter.

-g integer
--max_parallel_chunks integer

Sets the value of the max_parallel_chunks configuration parameter.

-t integer
--max_parallel_tables integer

Sets the value of the max_parallel_tables configuration parameter.

Command-Line Output-Control Options

Command-line options can also be specified that control displaying the information on the screen:

-i

Only display information on tables: list of columns and their types.

--no_tui

Display no progress information for tests.

-q
--quiet

Display no progress information on tests and their results.

The --skip_passed_tables option above also controls displaying the information on the screen.

Index

C

complete

prosync command, complete

config generate

procopy command, config generate

prosync command, config generate

I

init

prosync command, init

L

load

procopy command, load

P

procheck, procheck

procheck command, procheck

procopy, procopy

prosync, prosync

R

run

prosync command, run

Appendix A. Release Notes

A.1. ProGate 1.1

Release date: 2026-02-06

This is the first public release of the ProGate software, designed for data migration and replication between different source and destination databases, referred to as sources and destinations.

Main features of ProGate are as follows:

Main data migration and replication features.

Support of Oracle, PostgreSQL, and Postgres Pro DBMS as sources.

Support of PostgreSQL and Postgres Pro DBMS as destinations.

Data export to files in the Apache Parquet format.

Techniques to check the quality of data migration.

Conversion of source data types to destination data types; a possibility to configure strings to be converted to NULL.

A possibility to change EOL characters during data processing.

Redefinition of lists of tables and fields for data migration and replication.

Support for case-insensitive names of database objects.

Configuration of the parallelism of data migration tasks. Specifically, a possibility of multi-threaded transfer of individual tables.

A possibility to use a DB snapshot to get data for the migration.

Support of conditions for data selection.

A possibility to stop data migration or replication process correctly and restore the process from the termination point.

Support of high-availability features offered by Postgres Pro BiHA.

ProGate includes the following components:

procopy — the command-line utility for data migration.

prosync — the command-line utility for data replication.

procheck — the command-line utility to check the quality of data transfer.

ProGate web interface — a component to manage data migration and replication processes.

Data migration and replication tasks can be managed through the web interface or by means of the command-line interface.