Preparing your Postgres source database Innovation Release

Configure your Postgres source database and create the required users before starting your migration. What you need to configure depends on your goal:

• Schema ingestion only with the EDB Postgres AI agent: Create a machine user in Hybrid Manager (HM), set up a dedicated OS user for the agent on your auxiliary machine, and create a migration user with schema extraction permissions. No WAL-level database changes are required.

• Data migration with the Data Migration Service (DMS) agent: Configure WAL settings and grant the required replication privileges.

If you plan to run both schema ingestion and data migration, complete all sections. If you only plan to do schema or data migration, each section explains whether it's applicable for your use case.

Creating the migration user

Create a single migration user that serves both the EDB Postgres AI agent and the DMS agent. Using one user simplifies credential management across agents.

Using a database admin account, connect to your source Postgres database and create the migration user:

CREATE USER <migration_user> WITH REPLICATION LOGIN PASSWORD '<migration_user_password>';

REPLICATION is required for the DMS agent to create replication slots and read from the WAL. If you're performing schema ingestion only, you can omit it — but including it now means you won't need to alter the user later if you add data migration.

Granting schema ingestion permissions

1. Grant the EDB Postgres AI agent connect access to the source database:

   → WrapCopy

   GRANT CONNECT ON DATABASE <source_database> TO <migration_user>;

   Where <source_database> is the name of the database you want to assess. If you plan to connect the agent to additional databases on the same server, grant CONNECT on each one.

2. Grant the roles needed for a complete schema extraction:

   • Postgres 14 and later
   • Postgres 10 to 13

   → WrapCopy

   GRANT pg_monitor TO <migration_user>;
   GRANT pg_read_all_data TO <migration_user>;
   GRANT pg_read_all_settings TO <migration_user>;

   Together, these three roles enable a full schema extraction, including ownership and privilege information. With only pg_read_all_data granted, the agent performs a partial extraction limited to schema object definitions, without ownership and privilege information.

   pg_read_all_data isn't available in Postgres 10 to 13:

   → WrapCopy

   GRANT pg_monitor TO <migration_user>;
   GRANT pg_read_all_settings TO <migration_user>;

3. If you're using Postgres 16 or earlier, also grant the following to allow the agent to discover the Postgres log file location:

   → WrapCopy

   GRANT EXECUTE ON FUNCTION pg_catalog.pg_current_logfile() TO <migration_user>;

Granting data migration permissions

1. Grant the migration user usage on each schema containing tables to migrate:

   → WrapCopy

   GRANT USAGE ON SCHEMA <db_schema> TO <migration_user>;

2. Grant SELECT on source tables. For an entire schema:

   → WrapCopy

   GRANT SELECT ON ALL TABLES IN SCHEMA <db_schema> TO <migration_user>;

   For individual tables:

   → WrapCopy

   GRANT SELECT ON <table_name> TO <migration_user>;

3. Grant CREATE on the source database so the DMS agent can create publications:

   → WrapCopy

   GRANT CREATE ON DATABASE <pg_db_name> TO <migration_user>;

Preparing for schema ingestion (EDB Postgres AI agent)

Creating a dedicated OS user for the agent

Create a dedicated OS user on your auxiliary machine to run beacon-agent. This strategy avoids granting the agent permissions it doesn't need. This is a Linux-specific example, the exact command might vary by distribution:

sudo useradd <agent_os_user>

Preparing for data migration (DMS agent)

Configuring WAL settings

The DMS agent uses Postgres logical decoding to capture changes from the write-ahead log (WAL). Configure the following parameters in postgresql.conf:

1. Set wal_level to logical to enable logical decoding:

   → WrapCopy

   wal_level = logical

2. Set max_wal_senders to at least 1. If other streaming clients connect to your database, consult your DBA for the appropriate value:

   → WrapCopy

   max_wal_senders = 1

3. Set max_replication_slots to at least 1. This can be higher if your organization uses Postgres replication:

   → WrapCopy

   max_replication_slots = 1

4. Set max_wal_size large enough that production traffic generates mostly timed checkpoints rather than requested checkpoints based on WAL size. We recommend at least 8 GB:

   → WrapCopy

   max_wal_size = 8GB

   Setting max_wal_size too small can allow Postgres to drop WAL entries before the DMS agent can stream them, interfering with or slowing down the migration. Consult your DBA to determine the appropriate size for your environment.

5. Restart the database server to apply the changes.

Creating the replication group role

The migration user must own the source tables to auto-create Postgres publications. Because those tables are already owned by another role, create a replication group role that both the current table owner and the migration user belong to:

CREATE ROLE <replication_group>;
GRANT <replication_group> TO <migration_user>;
GRANT <replication_group> TO <original_owner>;
ALTER TABLE <table_name> OWNER TO <replication_group>;

Where:

• <migration_user> is the Postgres user you created for migration.
• <original_owner> is the original owner of the tables.
• <replication_group> is the shared role that owns the source tables for publication auto-creation.
• <table_name> is the name of a table to migrate (case-sensitive).

Configuring SSL

Configure your source database server to accept SSL connections. The DMS agent requires SSL to connect to the source. Create a server certificate and server private key — you can generate the private key with a tool like OpenSSL.

Validating your configuration

The DMS agent includes a validation script that checks whether your source database meets all requirements for CDC migration.

1. Navigate to the DMS agent folder:

   → WrapCopy

   cd /opt/cdcagent/reader/

2. Create an array of all tables to migrate in <schema_name>.<table_name> format:

   → WrapCopy

   arr=(schema1.table1 schema1.table2 schema2.table1)

3. Run the validation script:

   → WrapCopy

   PG_USERNAME=postgres PG_PASSWORD=<password> PG_HOST=<host> PG_PORT=5432 DB_NAME=<db_name> DBZ_USERNAME=<migration_user> DBZ_PASSWORD=<migration_user_password> ./postgresConfigValidation.sh "${arr[@]}"

   A passing result looks like this:

   → WrapCopy

    *** [Transporter] - Get PostgreSQL Major Version
   PostgreSQL major version: 17
    *** [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: 8 GB
   [Pass] max_wal_size is set to the recommended value of 8GB or higher.
    *** [Transporter] - Validate checkpoints
   checkpoints_timed: 100
   checkpoints_req: 5
   [Pass] Timed checkpoints are more frequent than requested checkpoints.
    *** [Transporter] - Check <migration_user> user role
   [Pass] User '<migration_user>' is present
   [Pass] User '<migration_user>' has replication permission
    *** [Transporter] - Check SELECT privilege on the tables to be migrated
   [Pass] User <migration_user> has select privilege on all tables to be migrated.
    *** [Transporter] - Check CREATE privilege on database
   [Pass] Role '<migration_user>' has CREATE privilege on database '<db_name>'.

Next step

Configure the EDB Postgres AI agent to assess your database