All Products
Search
Document Center

Migrate data from on-premises PostgreSQL to RDS for PostgreSQL

Last Updated: Jun 06, 2019

Data Transmission Service (DTS) helps to migrate data from a local PostgreSQL instance to an RDS for PostgreSQL instance. The incremental migration is supported so that you can migrate PostgreSQL databases without stopping local applications.

This topic describes how to use DTS to migrate data from PostgreSQL to RDS for PostgreSQL instances.

Migration type

  • Schema migration

    DTS migrates the schema definition of the migration objects to the destination instance. The PostgreSQL objects that DTS supports for schema migration includetable, trigger, view, sequence, function, user defined type, rule, domain, operation and aggregate.

  • Full migration

    DTS migrates all the object data of the source database to the target instance.

  • Incremental migration

    During an incremental migration, data changes of local PostgreSQL instances are synchronized to the target RDS instance, and the local PostgreSQL instance and target RDS instance enter the dynamic synchronization. By using incremental migration, you can have a non-disruptive transition from PostgreSQL to RDS for PostgreSQL, with PostgreSQL working normally in your local environment.

Migration Restrictions

  • The PostgreSQL versions supported by full migration are 9.2, 9.3, 9.4, and 9.5. The PostgreSQL versions supported by incremental migration are 9.4.8 and 9.5.
  • DDL operations are not supported during the migration.
  • Functions that are written in the C programming language are not supported during migration.
  • If object name mapping is enabled, other objects dependent on this object may fail to be migrated.

Permission requirements for migration

When DTS is used for PostgreSQL migration, the migration accounts of source and destination databases must have the following permissions if different migration types are involved:

Migration type Schema migration Full migration Incremental migration
Local PostgreSQL instance The usage permission on pg_catalog The select permission of the migration object superuser
Target RDS instance The create and usage permissions of the migration object Schema owner Schema owner

Migration sequence

When DTS migrates data from PostgreSQL to RDS for PostgreSQL, to resolve the dependencies between objects and improve the success rate of migration, DTS defines the migration sequence of schema objects and data as follows:

  1. Migrate schema objects: Table, view, sequence, function, user defined type, rule, domain, operation, and aggregate.
  2. Full migration
  3. Migrate schema objects: triggers and foreign keys.

After a full migration is complete, the migration progress in the task list is Schema migration 100%, Full migration 100%. The migration status is In progress. The migration task is in progress of step 3. Do not end the task manually in this status. Otherwise, the migrated data may be inconsistent.

Migration procedure

The following steps describe how to configure DTS migration tasks to migrate the local PostgreSQL database to the RDS for PostgreSQL database.

Create a database in the RDS instance

In the data migration process, if the database to be migrated does not exist in the target RDS instance, DTS automatically creates one. However, in the following two scenarios, you need to manually create the database before configuring the migration task.

  1. The database name does not conform to RDS definition specification (consists of lower case letters, numbers, underscores, and dashes, beginning with the letter, ending with letter or number, up to 64 characters ).
  2. The database to be migrated has different names in the local PostgreSQL and target RDS For PostgreSQL instances.

Under both circumstances, you need to create the database in the RDS console before configuring the migration task. For more information, see Create RDS Databases.

Create a migration account

When configuring a migration task, you need to provide the migration account of the Oracle database and the target RDS instance. For more information about required permissions for a migration account, see Permission requirements for migration.

If you have not created a migration account for your local PostgreSQL or RDS for PostgreSQL instances, follow these steps to create one:

  1. Create a migration account in PostgreSQL through the PostgreSQL client.

    1. create user username password 'password'

    If you want to use incremental migration, the created account must be a superuser account. Therefore, use the following statement to create an account.

    1. create user username with superuser password 'password'

    Parameter description:

    • Username: The account to be created
    • Password: The logon password for this account
  2. To authorize the migration account, refer to the preceding table for permissions required by migration accounts for local PostgreSQL and RDS for PostgreSQL.

    1. GRANT privileges ON tablename TO username;

    Parameter description:

    • Privileges: The operational permissions of this account, such as select, insert, update. If you want to authorize all the permissions to the account, you can use ALL.
    • Tablename: Table name. If you want to authorize all the table permissions to the account, you can use the wildcard character (*).
    • Username: The name of the account to authorize

Install the logical flow replication plugin

If you want to migrate data changes without stopping services, you need to, before you configure the task, install the logical flow replication plugin provided by DTS in local PostgreSQL.

  1. Download the plugin.

  2. Install the plugin.

    1. Uncompress the downloaded package.
    2. Copy the ali_decoding.so file to the lib directory in the PostgreSQL installation directory.

    If you want to use the rpm package for the installation, the absolute path is: /usr/pgsql-{$version}/lib/, where $version indicates the version number. For example, if the version is 9.5, the absolute path is: /usr/pgsql-9.5/lib.

    1. Copy the ali_decoding.control file to the share/extension directory in the PostgreSQL installation path.

    If you want to use the rpm package for the installation, the absolute path is /usr/pgsql-${version}/share/extension/, where ${version} indicates the PostgreSQL version number. For example, if the version is 9.5, the absolute path is /usr/pgsql-9.5/share/extension/.

    1. Test whether the plugin is successfully installed.

    You can log on to the PostgreSQL client using the superuser account and run the following SQL statements to see whether replication slot can be created successfully. If you can create it successfully, the plugin is successfully installed.

    1. SELECT * FROM pg_create_logical_replication_slot('replication_slot_test', 'ali_decoding');

    If the following output is displayed, it indicates the plugin is successfully installed.

1

If the test is successful, delete the replication slot using the SQL statement as follows:

  1. SELECT pg_drop_replication_slot('replication_slot_test');

Configure a migration task

After all of the preceding prerequisites are met, you can start to configure a migration task. The following describes the procedure of configuring a migration task.

  1. Log on to the DTS console, click Create migration task in the upper-right corner of the console to start migration task configuration.

  2. Configure the connection information between the local PostgreSQL and target RDS For PostgreSQL instance.

    You need to configure the migration task name, the connection information of the PostgreSQL instances, and the target RDS for PostgreSQL instances. Specifically:

    • Task name

    DTS generates a name for every task automatically. The task name is not required to be unique. You can modify the name as needed. We recommend that you configure a name indicating specific services so that the task can be easily identified.

    • Source instance information

      • Instance type: Select External database with a public IP address
      • Database type: Select PostgreSQL.
      • Host name or IP address: Configure the address for accessing the local PostgreSQL instance. The address must support public access.
      • Port: The listening port of the local PostgreSQL instance.
      • Database name: The name of the default database connecting to the PostgreSQL.
      • Database account: The access account of the local PostgreSQL instance.
      • Database password: The password of the preceding database account.
    • Target instance information

      • Instance type: Select RDS Instance.
      • RDS instance ID: ID of the target RDS instance to be migrated - DTS supports RDS instances in classic networks and VPC networks.
      • Database name: The default name of the database connected to the RDS for PostgreSQL.
      • Database account: The access account of the RDS for PostgreSQL instance.
      • Database password: The password of the preceding database account.1

    After you configure the connection information, click Authorize Whitelist and enter into next step to add entries to the whitelist. DTS adds the IP address of its server to the whitelist of the destination RDS instance to avoid migration failures due to the whitelist restrictions.

3.Select the migration object and migration type.

  • Migration type

    Schema migration, full migration, and incremental migration are supported to migrate data from PostgreSQL to RDS for PostgreSQL.

    If you only need to migrate existing data, select Migrate object schema and Migrate existing data as the migration type.

    If you need to migrate without downtime, select Migrate object schema, Migrate existing data, and Migrate data changes as the migration type.

  • Migration object

    Select the objects to be migrated. The migration object can be a database, a table, or a column. By default, after the object is migrated to the RDS for PostgreSQL instance, the object name remains the same with that in the local PostgreSQL instance. If the object you migrate has different names in the source instance and the target instance, you need to use the object name mapping feature provided by DTS. For more information, see Map databases, tables, and columns.44.Pre-check.

    Before the migration task starts, a pre-check is performed. Only after the pre-check is passed, the migration can start successfully. For more information about pre-checks and how to fix errors, see the Pre-check introduction section at the end of this topic.

    If a pre-check fails, you can click the button next to a specific failed check item to view its failure details, and after you fix the errors, perform the pre-check again.4

5.Start the migration task.

After the pre-check is passed, you can start the migration task and view the detailed status of the migration and the migration progress in the task list.

The data migration task configuration from a local PostgreSQL database to an RDS for PostgreSQL instance is complete.

Pre-check

Before DTS starts the migration, a pre-check is performed. This section describes the checks that are performed before you migrate data from PostgreSQL to RDS for PostgreSQL:

Pre-check name Description Notes
Source database connectivity Checks the connectivity between DTS and the local PostgreSQL instance. (1) Checks whether the entered information is correct. If the entered information contains errors, correct the information and rerun the pre-check.
(2) Checks whether you have enabled access from other servers for the port.
Target database connectivity Checks the connectivity between DTS and the target RDS for PostgreSQL instance. Checks whether the entered information is correct. If it is incorrect, correct the information and rerun the pre-check.
Target database connectivity Checks the connectivity between DTS and the target RDS for PostgreSQL instance. Checks whether the entered information is correct. If it is incorrect, correct the information and rerun the pre-check.
Source database version Checks whether the local PostgreSQL version and the target RDS for PostgreSQL version are the same. If the versions are different, pre-check notifies you. You can upgrade or downgrade the local PostgreSQL installation based on suggestions, or continue the migration.
Database availability Checks whether the database to be migrated already exists in the target RDS for PostgreSQL instance. If the name of the database to be migrated does not meet the RDS instance naming requirements, DTS fails to create the database to be migrated in the target RDS instance. In this case, the database availability check fails. For more information about how to rename the database to be migrated, see Map databases, tables, and columns..
Source database privilege Checks whether the local PostgreSQL database account has the required permissions during task configuration. If the check fails, see Create a migration account to authorize the migration account, and pre-check the migration account again.
Target database privilege Checks whether the target RDS for PostgreSQL database account has the required permissions during task configuration. If the check fails, see Create a migration account to authorize the migration account, and pre-check the migration account.
Cognominal objects Checks whether the migration object already exists in the target RDS for PostgreSQL instance. If the check fails, delete those objects in the target database and rerun the pre-check.
Source cognominal objects Checks whether the objects to be migrated to the same database are cognominal. If the check fails, see Map databases, tables, and columns and rename the cognominal objects.
Constraint integrity Checks whether the parent object that is the dependency of the migration object is migrated. If the check fails, modify the migration object, migrate the parent object that is the dependency of the migration object, and then rerun pre-check.
Incremental topology conflict Checks whether the same migration object has other migration links. If a conflicting link exists, delete it and rerun the pre-check.
LC_MONETERY consistency Checks whether the definitions of LC_MONETERY of the source database and target database are consistent. If the check fails, modify the LC_MONETERY definition of the target RDS for PostgreSQL instance, or continue the migration.
Logical flow replication plugin for PostgreSQL Checks whether the logical flow replication plugin is installed in the local PostgreSQL instance. If the check fails, see Install the logical stream replication plugin, and re-check after you install the plugin.
The existence of PostgreSQL logical flow slot Checks whether the local PostgreSQL instance has a replication slot with the same name as the replication slot created by DTS. If the check fails, delete the replication slot in the local PostgreSQL instance and rerun the pre-check.