Preparing Postgres source databases v2_preview

Suggest edits

Configuring Postgres for EDB Transporter requires 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 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>.

Postgres configuration

To perform Postgres configuration:

  1. Verify Postgres configuration.
  2. Create new roles and grant acccess for CDC migration.
  3. Grant SELECT on source tables to the CDC migration role.
  4. Create a logical replication slot

Verify Postgres configuration

Verify or set these configuration entries for Postgres.

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

    See the Postgres replication documentation for more information.

  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 EDB Transporter's cloud infrastructure. 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.

    For more information, see this EDB blog post on tuning max_wal_size and the Postgres WAL documentation.

Config validation script

CDCReader installation comes with a helper script that validates the Postgres configuration and helps you identify any issues. After you configure the database, we recommend running the script and ensuring all checks passed.

Run the script without arguments to print the usage:

/opt/cdcreader/postgresConfigValidation.sh

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 SELECT on source tables to the CDC migration role

The new <MIGRATION_ROLE> needs SELECT access to source tables. 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.

Create a logical replication slot

The CDC migration process for Postgres sources leverages logical decoding and the publication/subscription mechanism. To use Postgres as a source, you need to create a replication slot for your CDC migration role:

PERFORM pg_create_logical_replication_slot('<MIGRATION_ROLE>',  'pgoutput');

Where:

  • <MIGRATION_ROLE> is the name of the Postgres role or user to use for CDC migration database access.
  • pgoutput is the logical decoding plugin supplied by Postgres that EDB Transporter uses.

More information

Your database is ready for CDC migration.

For more information, see the Debezium Postgres Connector documentation.

← Prev

Preparing Oracle source databases

Next →

Preparing target databases