Preparing your Postgres source database

Connect to the source database

To prepare your Postgres source for the migration, you need administrative privileges. Create a change data capture (CDC) migration role with limited privileges for data migration.

Execute SQL statements with psql or a similar client.

To connect to the source database using psql:

psql -h <PG_HOST> -p <PG_PORT> -U <PG_USERNAME> -d <PG_DB_NAME>

Where:

• <PG_DB_NAME> is the name of the Postgres database source to connect to.
• <PG_HOST> is the Postgres database host.
• <PG_PORT> is the Postgres database port.
• <PG_USERNAME> is an administrative user who can create and grant roles, alter ownership of tables to migrate, and create a replication slot.

This command prompts you for the password associated with <PG_USERNAME>.

Set the Postgres configuration

Ensure the following configuration entries for Postgres are set in the postgresql.conf file as follows:

1. Ensure wal_level is configured as logical.

   The CDC migration process leverages Postgres logical decoding. Setting wal_level to logical enables logical decoding of the Postgres write-ahead log (WAL).

2. Ensure max_wal_senders is configured appropriately.

   If EDB Data Migration Service migration is the first streaming client for your database, set max_wal_senders to at least 1. Other streaming clients might be present. Consult your DBA for the appropriate value for streaming client connectivity.

3. Ensure max_replication_slots is configured appropriately.

   max_replication_slots must be at least 1 for the CDC migration process. This value can be higher if your organization uses Postgres replication.

4. Ensure max_wal_size is configured for adequate WAL LSN lifetime.

   Set the max_wal_size value large enough that production traffic is generating mostly timed checkpoints and not requested checkpoints based on WAL size.

   The streaming migration process also requires changes to be available in the WAL until they can be streamed to durable message storage in the cloud infrastructure of EDB DMS. Setting max_wal_size too small can affect performance. It can also interfere with the migration process by allowing Postgres LSNs to be dropped from the WAL before they can be streamed.

5. Restart the database server to apply these changes.

Create new roles and grant acccess for CDC migration

First, create a new role for CDC migration with LOGIN and REPLICATION abilities granted:

CREATE ROLE <MIGRATION_ROLE> WITH REPLICATION LOGIN PASSWORD '<MIGRATION_ROLE_PASSWORD>';

<MIGRATION_ROLE> needs to own the source tables to autocreate Postgres publications. Because the source tables are already owned by another role, you create a role/user that can act as the new owner and grant the specified replication group role to both the current table owner and to <MIGRATION_ROLE>:

CREATE ROLE <REPLICATION_GROUP>;
GRANT <REPLICATION_GROUP> TO <MIGRATION_ROLE>;
GRANT <REPLICATION_GROUP> TO <ORIGINAL_OWNER>;
ALTER TABLE <TABLE_NAME> OWNER TO <REPLICATION_GROUP>

Where:

• <MIGRATION_ROLE> is the name of the Postgres role or user to use for CDC migration database access.

• <ORIGINAL_OWNER> is the original production owner of the table.

• <REPLICATION_GROUP> is the name of a role used to own the source tables to migrate for publication autocreation.

Grant usage of the source schemas to the CDC migration role

To select tables in a schema, the user must grant usage on the schema in addition to granting SELECT on the tables of the schema. This is necessary for all source schemas containing tables to be migrated.

You can grant usage on a schema using the following command:

GRANT USAGE ON SCHEMA <DB_SCHEMA> TO <MIGRATION_ROLE>;

Grant SELECT on source tables to the CDC migration role

The new <MIGRATION_ROLE> now has schema access, but still needs SELECT access to the source tables of those schemas. You can grant access across a schema or for each table.

For an entire schema's tables, use this command:

ALTER DEFAULT PRIVILEGES IN SCHEMA <DB_SCHEMA> GRANT SELECT ON TABLES to <MIGRATION_ROLE>

For each table, use:

GRANT SELECT ON <TABLE_NAME> TO <MIGRATION_ROLE>

Where:

• <DB_SCHEMA> is the database schema name for the tables to migrate.

• <MIGRATION_ROLE> is the name of the Postgres role or user to use for CDC migration database access.

• <TABLE_NAME> is the name of a table to migrate.

Grant CREATE privileges on the source database to the CDC migration role

Since the pgoutput plugin is being used, the migration user needs to be granted CREATE privileges on the source database in order for Debezium to be able to create publications. The CREATE privilege can be granted using the following command:

GRANT CREATE ON DATABASE <PG_DB_NAME> to <MIGRATION_ROLE>

SSL configuration

Ensure you configure your source database server to accept SSL connections to allow the EDB DMS Reader to connect to it. You must create a server certificate and a server private key, for example, with OpenSSL, to enable this configuration.

Validate your configuration

Now that you have configured the source database, validate your Postgres configuration. For this, you need the configuration validation script packaged with the DMS Reader.

Prerequisites

1. Ensure the machine from where you are running the Reader has Java/OpenJDK 17 or later installed.

2. Install the EDB DMS Reader.

3. Navigate to the EDB DMS Reader folder in /opt/cdcreader/.

Run the config validation script

To illustrate, consider the following example:

1. Create an array that contains the name of all the tables that need to be migrated in <schema_name>.<table_name> format. In this example:

   → WrapCopy

   arr=(test1.table1 test1.table2 test1.table3 test1.table4)

2. Run the script with the necessary parameters. In this example:

   → WrapCopy

   PG_USERNAME=postgres PG_PASSWORD=password PG_HOST=localhost PG_PORT=5432     DB_NAME=postgres DBZ_USERNAME=debezium DBZ_PASSWORD=dbz ./postgresConfigValidation.sh     "${arr[@]}"

   You should get a response similar to the following:

   → WrapCopy

    *** [Transporter] - Validate WAL Level
   wal_level:  logical
   [Pass] wal_level is 'logical'.
   
    *** [Transporter] - Validate max WAL senders
   max_wal_senders: 10
   [Pass] max_wal_senders is at least 1.
   
    *** [Transporter] - Validate max replication slots
   max_replication_slots: 10
   [Pass] max_replication_slots is at least 1.
   
    *** [Transporter] - Validate max WAL size
   max_wal_size: 1 GB
   [Fail] max_wal_size (1 GB) is less than 8 GB
   
    *** [Transporter] - Validate checkpoints
   checkpoints_timed: 378
   checkpoints_req: 3
   [Pass] Timed checkpoints are more frequent than requested checkpoints.
   
    *** [Transporter] - Check debezium user role
   [Pass] User 'debezium' is present
   [Pass] User 'debezium' has replication permission
   
    *** [Transporter] - Check SELECT privilege on the tables to be migrated
   [Pass] User debezium has select privilege on all tables to be migrated.
   
    *** [Transporter] - Check presence of a replication slot
   [Fail] Replication slot 'debezium' is not present
   

Your database is ready for CDC migration.

More information

• See Debezium Postgres Connector for information on CDC configuration.

• See the Postgres replication documentation for more information on max_replication_slots configuration.

• See EDB blog post on tuning max_wal_size and on Postgres WAL documentation.