Configure Migration Prerequisites for PostgreSQL

On this page

Before you Begin

Steps
Learn More

To run migration jobs from an PostgreSQL source database, the database may require some configuration changes. If Relational Migrator determines the database needs configuration changes, it automatically generates a SQL script with the required changes. It is recommended to have a Database Administrator (DBA) review the commands in this script and perform their execution on the database server. These instructions configuration PostgreSQL for both types of migration jobs:

Snapshot migration jobs migrate all the data and then stops.
Continuous migration job run a snapshot and then enter a CDC stage to continuously replicate data changes.

Before you Begin

If PostgreSQL is configured as a cluster, Relational Migrator must connect to the master server.

Steps

For snapshot jobs against PostgreSQL, the service account requires schema USAGE and table SELECT permissions.

GRANT USAGE ON SCHEMA <schema_name> TO <database_user_name>;
GRANT SELECT ON TABLE <schema_name>.<table_name> TO <database_user_name>;

Enable logical replication

Logical replication may not be enabled by default. To enable logical replication, change the wal_level configuration in the postgresql.conf configuration file. You must restart the database instance after changing the configuration file.

wal_level = logical

If you are using PostgreSQL hosted on AWS RDS, you must set the rds.logical_replication parameter to 1. For details, see Enable Logical Replication on AWS. You must restart the database instance after setting the parameter.

Tip

You can use the following query to check if your AWS RDS instance has logical replication enabled:

SELECT name,setting
FROM pg_settings
WHERE name IN ('wal_level','rds.logical_replication');

Create a SQL replication role

Create a role with REPLICATION and LOGIN database permissions:
```
CREATE ROLE <role> REPLICATION LOGIN;
```
Grant the table SELECT and schema USAGE permissions to the role. Each table in the migration requires a GRANT SELECT statement:
```
GRANT USAGE ON SCHEMA <schema> TO <role>;
GRANT SELECT ON <schema>.<table> TO <role>;
-- ADDITIONAL GRANT SELECT STATEMENTS...
```
Grant the role to the service account
Replace <original_owner> with the owner of the participating tables.
```
GRANT <role> TO <original_owner>;
GRANT <role> TO <database_user_name>;
```

Grant ownership of each table to the role

Each table in the migration requires a ALTER TABLE statement:

ALTER TABLE <schema>.<table> OWNER TO <role>;
-- ADDITIONAL ALTER TABLE STATEMENTS...

Create a publication

Create a publication each table in the migration must be specified in the FOR statement separated by commas:

CREATE PUBLICATION "MIGRATOR_<name>_PUBLICATION"
FOR TABLE "<schema>"."<table1>","<schema>"."<table2>";

Set replica identity to full

Each table in the migration requires a ALTER TABLE statement:

ALTER TABLE <schema>.<table> REPLICA IDENTITY FULL;
-- ADDITIONAL ALTER TABLE STATEMENTS...

Learn More

Relational Migrator relies on the open-source Debezium connector to capture row-level changes. For more details, see Debezium PostgreSQL.

Back

Oracle

SQL Server