Postgres Pro ProGate : Documentation: 1: 4.4. Setting up the Postgres ProGate Source and Destination : Postgres Professional

4.4. Setting up the Postgres ProGate Source and Destination
Prev	Up	Chapter 4. About Postgres ProGate	Home	Next

4.4. Setting up the Postgres ProGate Source and Destination #

4.4.1. Setting up the Source

4.4.1.1. Oracle Source
4.4.1.2. Postgres Pro/PostgreSQL Source

4.4.2. Setting up the Destination

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

4.4.1. Setting up the Source #

4.4.1.1. Oracle Source #

The following setup of the Oracle source is needed:

To run prosync, on the source, enable SUPPLEMENTED LOGS of the PRIMARY KEY level:
```
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS
```

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 prosync to work with the Oracle source, grant the user under which the migration will run with more permissions:

GRANT CONNECT, RESOURCE TO PROGATE_USER;

GRANT EXECUTE ON DBMS_LOGMNR TO PROGATE_USER;

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.1.2. Postgres Pro/PostgreSQL Source #

Grant the user under which the connection to the database will occur with the CONNECT, SELECT, and EXECUTE permissions:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO PROGATE_USER;

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO PROGATE_USER;

GRANT CONNECT ON DATABASE dbname TO PROGATE_USER;

If large objects are available in the source database, grant additional permissions to access them.

To read the changes, set the configuration parameter of the database server:

wal_level = logical

For correct operation of prosync, set max_slot_wal_keep_size to a value that is greater than the volume of changes that occur during the time interval needed to execute the initial data load. If the value set is not sufficiently great, database errors can be encountered while running prosync: ERROR: can no longer get changes from replication slot. In this case, the correct transfer of the changes by prosync gets impossible. It is recommended to set max_slot_wal_keep_size to -1 for running the migration.

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.

Prev	Up	Next
4.3. Installation and Initial Setup	Home	4.5. Cold and Hot Database Migration

epub pdf