Preparing for the transfer

Make sure the settings for the network hosting the cluster allow public connections from IP addresses used by Data Transfer.

Create a user account the transfer will use to connect to the source. To do this, run the following command:

CREATE ROLE <username> LOGIN ENCRYPTED PASSWORD '<password>';

Configure the source cluster to enable the user you created to connect to all the cluster master hosts.

If you are going to use parallel copy, configure the source cluster to enable the user you created to connect to all the cluster segment hosts in utility mode.

Grant the user you created the SELECT privilege for the tables to be transferred and the USAGE privilege for the schemas these tables belong to.

Privileges must be granted to entire tables. Access to certain table columns only is not supported.

Tables without the required privileges are unavailable to Data Transfer. These tables are processed as if they did not exist.

This example grants privileges to all the database tables:

GRANT SELECT ON ALL TABLES IN SCHEMA <schema name> TO <username>;
GRANT USAGE ON SCHEMA <schema name> TO <username>;

Estimate the total number of databases for transfer and the total MongoDB workload. If the workload on the database exceeds 10,000 writes per second, create multiple endpoints and transfers. For more information, see Configuring a MongoDB source endpoint.

Make sure the settings for the network hosting the cluster allow public connections from IP addresses used by Data Transfer.

Make sure that the MongoDB version on the target is 4.0 or higher.

Make sure the MongoDB cluster is configured so that it returns correctly resolving IP addresses or FQDNs (fully qualified domain names) in response to requests.

Configure access to the source cluster from Nebius Israel. To configure a source cluster for connections from the internet:

1. In the configuration file, change the net.bindIp setting from 127.0.0.1 to 0.0.0.0:

   # network interfaces
   net:
     port: 27017
     bindIp: 0.0.0.0
   

2. Restart the mongod service:

   sudo systemctl restart mongod.service
   

If the source cluster does not use replication, enable it:

1. Add replication settings to the /etc/mongod.conf configuration file:

   replication:
     replSetName: <replica set name>
   

2. Restart the mongod service:

   sudo systemctl restart mongod.service
   

3. Connect to MongoDB and initialize the replica set with this command:

   rs.initiate({
       _id: "<replica set name>",
       members: [{
           _id: 0,
           host: "<IP address listening to mongod>:<port>"
       }]
   });
   

Create a user with the readWrite role for all source databases to be replicated:

use admin
db.createUser({
    user: "<username>",
    pwd: "<password>",
    mechanisms: ["SCRAM-SHA-1"],
    roles: [
        {
            db: "<name of source database 1>",
            role: "readWrite"
        },
        {
            db: "<name of source database 2>",
            role: "readWrite"
        },
        ...
    ]
});

Once started, the transfer will connect to the source on behalf of this user. The readWrite role is required so that a transfer can write data to the __data_transfer.__dt_cluster_time service collection.

When using MongoDB versions 3.4 and 3.6 for a transfer, the user must have read access to the local.oplog.rs collection and read and write access to the __data_transfer.__dt_cluster_time collection. To assign a user the clusterAdmin role granting these privileges, connect to MongoDB and run the following commands:

use admin;
db.grantRolesToUser("<username>", ["clusterAdmin"]);

To issue more granular privileges, you can assign the clusterMonitor role required for read access to the local.oplog.rs and grant read and write access to the __data_transfer.__dt_cluster_time system collection.

Enable full binary logging on the source by setting the Binlog row image parameter to FULL or NOBLOB.

(Optional) Set a limit on the size of data chunks to be sent using the Max allowed packet parameter.

Create a user for connecting to the source.

1. Grant the user the ALL_PRIVILEGES privilege for the source database.

2. Grant the user the REPLICATION CLIENT and REPLICATION SLAVE administrative privileges.

For Replication and Snapshot and increment transfers, tables without primary keys are not transferred. To make sure the transfer is running properly when migrating a database with such tables:

• Do not transfer tables without primary keys. For this purpose, add them to the list of excluded tables in source endpoint settings.

• Create PRIMARY KEYS in those tables to be migrated where there are no keys.

  1. To get a list of tables without a primary key, run the query:

     SELECT
         tab.table_schema AS database_name,
         tab.table_name AS table_name,
         tab.table_rows AS table_rows
     FROM information_schema.tables tab
         LEFT JOIN information_schema.table_constraints tco
             ON (tab.table_schema = tco.table_schema
                 AND tab.table_name = tco.table_name
                 AND tco.constraint_type = 'PRIMARY KEY')
     WHERE
         tab.table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
         AND tco.constraint_type IS NULL
         AND tab.table_type = 'BASE TABLE';
     

  2. Study the structure of tables without a primary key that need to be transferred to the target:

     SHOW CREATE TABLE <database name>.<table name>;
     

  3. Add a simple or complex primary key to the tables that need to be transferred to the target:

     ALTER TABLE <table name> ADD PRIMARY KEY (<column or group of columns>);
     

  4. If the table being transferred to the target has no column or group of columns suitable for the role of the primary key, create a new column:

     ALTER TABLE <table name> ADD id BIGINT PRIMARY KEY AUTO_INCREMENT;
     

Deactivate trigger transfer at the transfer initiation stage and reactivate it at the completion stage (for the Replication and the Snapshot and increment transfer types). For more information, see the description of additional endpoint settings for the MySQL source.

Configure the user the transfer will use to connect to the source:

1. Create a user.

2. For the Replication and Snapshot and increment transfer types, assign the mdb_replication role to this user.

3. Connect to the database you want to migrate as the database owner and configure privileges:

   • SELECT for all the database tables to be transferred.
   • SELECT for all the database sequences to be transferred.
   • USAGE for the schemas of these tables and sequences.
   • ALL PRIVILEGES (CREATE and USAGE) to the __consumer_keeper and __data_transfer_mole_finder housekeeping table schema defined by the endpoint parameter if the endpoint is going to be used for the Replication or Snapshot and increment transfer types.

If the replication source is a cluster, enable the pg_tm_aux extension for it. This will allow replication to continue even after changing the master host. In certain cases, a transfer may return an error when you change masters in a cluster. For more information, see Troubleshooting.

For Replication and Snapshot and increment transfers, tables without primary keys are not transferred. To make sure the transfer is running properly when migrating a database with such tables:

• Do not transfer tables without primary keys. For this purpose, add them to the list of excluded tables in source endpoint settings.
• Add the replica ID to tables without primary keys:

  • For tables with an index, set REPLICA IDENTITY by unique key:

    ALTER TABLE MY_TBL REPLICA IDENTITY USING INDEX MY_IDX;
    

  • For tables without an index, change REPLICA IDENTITY:

    ALTER TABLE MY_TBL REPLICA IDENTITY FULL;
    

If there are no primary keys in a table, logical replication will not include any changes in the rows (UPDATE or DELETE).

• If, while trying to transfer tables from PostgreSQL to PostgreSQL, you do not exclude a table without primary keys on the transfer source, the following error message will be returned:

   failed to run (abstract1 source): Cannot parse logical replication message: failed to reload schema: primary key check failed: Tables: n / N check failed: "public"."MY_TBL": no key columns found
  

• If, while trying to transfer tables from PostgreSQL to a different database, you add a table without primary keys, the following error message will be returned:

  failed to run (abstract1 source): Cannot parse logical replication message: failed to reload schema: primary key check failed: Tables: n / N check failed:
  "public"."MY_TBL": no key columns found
  

Disable the transfer of external keys when creating a source endpoint. Recreate them once the transfer is completed.

Find and terminate DDL queries that are running for too long. To do this, make a selection from the PostgreSQL pg_stat_activity housekeeping table:

SELECT NOW() - query_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle' ORDER BY 1 DESC;

This will return a list of queries running on the server. Pay attention to queries with a high duration value.

Deactivate trigger transfer at the transfer initiation stage and reactivate it at the completion stage (for the Replication and the Snapshot and increment transfer types). For more information, see the description of additional endpoint settings for the PostgreSQL source.

To enable parallel data reads from the table, set its primary key to serial mode.

Then specify the number of jobs and threads in the transfer parameters under Runtime environment.

Configure WAL monitoring. For Replication and Snapshot and increment transfers, logical replication is used. To perform it, the transfer creates a replication slot with the slot_name equal to the transfer ID that you can get by selecting the transfer from the list of your transfers. The WAL size may increase for different reasons: due to a long-running transaction or a transfer issue. Therefore, we recommend setting up WAL monitoring on the source side.

1. To monitor the size of the used storage or disk, set up an alert using the monitoring tools (see the disk.used_bytes description).

2. Set the maximum WAL size for replication in the Max slot wal keep size setting. The value of this setting can be edited as of PostgreSQL ver. 13. To urgently disable a transfer to perform data reads, delete the replication slot.

   Warning

   If set to -1 (unlimited size), you will not be able to delete WAL files due to open logical replication slots that information is not read from. As a result, the WAL files will take up the entire disk space and you will not be able to connect to the cluster.

3. Set up an alert with the Monitoring tools for the metric used for Total size of WAL files. Make sure the threshold values are less than those specified for the disk.used_bytes metric because, apart from the data, the disk stores temporary files, the WAL, and other types of data. You can monitor the current slot size by making a DB request with the correct slot_name equal to the transfer ID:

   SELECT slot_name, pg_size_pretty(pg_current_wal_lsn() - restart_lsn), active_pid, catalog_xmin, restart_lsn, confirmed_flush_lsn
   FROM pg_replication_slots WHERE slot_name = '<идентификатор_трансфера>'
   

Create a target database.

If you need to transfer multiple databases, create a separate transfer for each of them.

Create a user with access to the target database.

Once started, the transfer will connect to the target on behalf of this user.

Create a security group and configure it.

Assign the created security group to the Managed Service for ClickHouse cluster.

Make sure the settings for the network hosting the cluster allow public connections from IP addresses used by Data Transfer.

Disable the following settings on the target:

• Integrity checks for foreign keys.
• Triggers.
• Other constraints.

Create a user:

CREATE ROLE <username> LOGIN ENCRYPTED PASSWORD '<password>';

Grant the user all privileges for the database, schemas, and tables to be transferred:

GRANT ALL PRIVILEGES ON DATABASE <database name> TO <username>;

If the database is not empty, the user must be its owner:

ALTER DATABASE <database name> OWNER TO <username>;

Once started, the transfer will connect to the target on behalf of this user.

Make sure the settings for the network hosting the cluster allow public connections from IP addresses used by Data Transfer.

Make sure that the MongoDB version on the target is not lower than that on the source.

Make sure the MongoDB cluster is configured so that it returns correctly resolving IP addresses or FQDNs (fully qualified domain names) in response to requests.

Configure the target cluster to allow connections from the internet:

1. In the configuration file, change the net.bindIp setting from 127.0.0.1 to 0.0.0.0:

   # network interfaces
   net:
     port: 27017
     bindIp: 0.0.0.0
   

2. Restart the mongod service:

   sudo systemctl restart mongod.service
   

If the target cluster does not use replication, enable it:

1. Add replication settings to the /etc/mongod.conf configuration file:

   replication:
     replSetName: <replica set name>
   

2. Restart the mongod service:

   sudo systemctl restart mongod.service
   

3. Connect to MongoDB and initialize the replica set with this command:

   rs.initiate({
       _id: "<replica set name>",
       members: [{
           _id: 0,
           host: "<IP address listening to mongod>:<port>"
       }]
   });
   

Connect to the cluster and create a target database with the same name as the source database:

use <database name>

Create a user with the readWrite role for the target database:

use admin;
db.createUser({
    user: "<username>",
    pwd: "<password>",
    mechanisms: ["SCRAM-SHA-1"],
    roles: [
        {
            db: "<target database name>",
            role: "readWrite"
        }
    ]
});

Once started, the transfer will connect to the target on behalf of this user.

To shard the migrated collections in the target cluster:

1. Prepare the database and create blank collections with the same names as in the source database.

   Data Transfer does not automatically shard the migrated collections. Sharding large collections may take a long time and slow down the transfer.

2. Enable target database sharding:

   sh.enableSharding("<target database name>")
   

3. Shard every collection based on its namespace:

   sh.shardCollection("<target database name>.<collection name>", { <field name>: <1|"hashed">, ... });
   

   For more information about the shardCollection() function, see the MongoDB documentation.

4. To verify that sharding is set up and enabled, get a list of available shards:

   sh.status()
   

5. If sharding is performed by any key other than the default _id, assign the clusterManager system role to the user Data Transfer will use for connection to the target cluster.

   use admin;
   db.grantRolesToUser("<username>", ["clusterManager"]);
   

6. When creating a target endpoint, select the DISABLED or TRUNCATE clean policy.

   Selecting the DROP policy will result in the service deleting all the data from the target database, including sharded collections, and replacing them with new unsharded ones when a transfer is activated.

Learn more about sharding in the MongoDB documentation.

Make sure that the major version of MySQL on the target is not lower than that on the source.

Set SQL Mode, which matches the source.

Create a user for connecting to the source.

1. Assign the userthe ALL_PRIVILEGES role for the source database.

Make sure that the major version of PostgreSQL on the target is not lower than that on the source.

Disable the following settings on the target:

• Integrity checks for foreign keys.
• Triggers.
• Other constraints.

In the target database, enable the same extensions that are enabled in the source database.

Make sure the target has the DROP transfer tables cleanup policy selected.

Create a user with access to the target database.

Grant the user all privileges for the database, schemas, and tables to be transferred:

GRANT ALL PRIVILEGES ON DATABASE <database name> TO <username>;

If the database is not empty, the user must be its owner:

ALTER DATABASE <database name> OWNER TO <username>;

Once started, the transfer will connect to the target on behalf of this user.

If the target has the Save transaction boundaries option enabled, grant the created user all privileges to create the __data_transfer_lsn housekeeping table in the current schema (usually it's public) on the target:

GRANT ALL PRIVILEGES ON SCHEMA <schema name> TO <username>;