Preparing your self-managed destination database v1.4.1 (LTS)

Prepare your self-managed Postgres database to serve as the destination for your migration.

If you already have a self-managed database you want to use as the destination, you can skip to Configuring SSL. Before proceeding, ensure that the tables you migrate and the load generated during migration won't interfere with existing workloads on the server.

Creating the destination database

If you don't have a destination database yet, create one. This example assumes you have installed the Postgres database distribution of your choice and initialized the server.

1. Connect to the destination Postgres server:

   → WrapCopy

   psql -p <PG_PORT> -U <PG_USERNAME>

2. Create an empty database to use as the migration destination. In this example the destination database is tr_target:

   → WrapCopy

   CREATE DATABASE tr_target;

Configuring SSL

Configure your destination Postgres server to accept SSL connections so the EDB DMS agent in writer mode can connect to it securely. To enable SSL, create a server certificate and a server private key (for example, with OpenSSL).

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

   → WrapCopy

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

3. Set the required permissions:

   → 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. Enable SSL in postgresql.conf:

   → WrapCopy

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

5. Allow local SSL connections in pg_hba.conf:

   → 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

Creating a migration role

The EDB DMS agent in writer mode requires write access to the destination database. You can use an existing superuser or create a dedicated migration role. This example creates a dedicated role:

1. Create a role with login access:

   → WrapCopy

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

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

2. Grant control over replication sessions:

   → WrapCopy

   GRANT SET ON PARAMETER session_replication_role TO <MIGRATION_ROLE_WRITER>;

   Note

   Granting control over replication sessions is supported only on Postgres 15 and later. If your destination database is on Postgres 14 or earlier, use a superuser instead.

Supply this role to the DMS writer agent via the DBCONFIG_DATABASES_0__USERNAME and DBCONFIG_DATABASES_0__PASSWORD values when you configure it in the next steps.

Next step

Migrate schemas