Preparing Postgres destination databases

Prepare your destination database before starting a migration.

Prerequisites

• Ensure you have created a database in your destination Postgres instance.

• Ensure you have prepared and imported the database schema to the destination database.

SSL configuration

You only need to configure SSL if you are migrating to a Postgres instance created outside of the HCP. HCP-managed destinations don't require this configuration.

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

For testing purposes, the following example creates a self-signed certificate and key with OpenSSL for a PostgreSQL 17 server on Ubuntu. The bin and data directories may vary depending on your OS, Postgres distribution, and version.

1. In your terminal, generate an SSL certificate and key:

   → WrapCopy

   openssl req -new -x509 -days 365 -nodes -out server.crt -keyout server.key

   Follow the terminal prompts to create the files.

2. Move the created files to the Postgres data directory, so the server has access to them:

   → WrapCopy

   sudo cp server.crt server.key /var/lib/postgresql/17/<data_directory>

3. Set the required permissions for the files:

   → WrapCopy

   sudo chmod 600 /var/lib/postgresql/17/<data_directory>/server.key 
   sudo chmod 600 /var/lib/postgresql/17/<data_directory>/server.crt 
   sudo chown postgres:postgres /var/lib/postgresql/17/<data_directory>/server.crt /var/lib/postgresql/17/<data_directory>/server.key

4. Modify the postgresql.conf file to enable SSL:

   → WrapCopy

   ssl = on
   ssl_cert_file = 'server.crt'
   ssl_key_file = 'server.key'

5. Update the pg_hba.conf file to allow local SSL connections. Add the following line:

   → WrapCopy

   hostssl all             all             0.0.0.0/0               md5

6. Restart your server to apply the changes:

   → WrapCopy

   /usr/lib/postgresql/17/bin/pg_ctl -D /var/lib/postgresql/17/<data_directory> restart

Create a role for CDC migration

You only need to configure a role for migration if you are migrating to a Postgres instance created outside of the HCP. HCP-managed destinations don't require this configuration.

The EDB DMS Writer requires certain permissions to access and be able to write data during the migration. For this, you can either use an existing superuser or create a new user with the required permissions. In this example we create a new user:

1. Create a new role for CDC migration with LOGIN abilities granted:

   → WrapCopy

   CREATE ROLE <MIGRATION_ROLE_WRITER> WITH LOGIN PASSWORD '<ROLE_PASSWORD>';

   Where <MIGRATION_ROLE_WRITER> is the name of the Postgres role or user to use for destination database access.

2. Grant permissions so the user can control the behavior of replication sessions:

   → WrapCopy

   GRANT SET ON PARAMETER session_replication_role TO <MIGRATION_ROLE_WRITER>

   Note

   Granting control over replication sessions is only supported on Postgres 15 and later versions. If your destination database is on Postgres 14 or earlier, you will have to use a superuser for the migration.

This is the user you will provide to the EDB DMS Writer during configuration via the DBCONFIG_DATABASES_0__USERNAME and DBCONFIG_DATABASES_0__PASSWORD values.