Migrate data from a self-managed PostgreSQL 9.x database to an ApsaraDB RDS for PostgreSQL instance

Create a superuser account

Create a superuser account on the self-managed PostgreSQL database to run the migration. The following example creates an account named migrate_test.

CREATE USER migrate_test WITH superuser password 'your_password';

Verify that the account was created:

\du+

Expected output:

                                                List of roles
  Role name   |                         Attributes                         | Member of | Description
--------------+------------------------------------------------------------+-----------+-------------
 migrate_test | Superuser                                                  | {}        |
 postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |

Configure replication parameters

DTS requires specific PostgreSQL parameters to enable logical replication. Check and update each parameter as needed, then restart the PostgreSQL service once for all changes to take effect.

1. Check wal_level:

   SHOW wal_level;

   If the result is not logical, set it:

   ALTER SYSTEM SET wal_level='logical';

2. Check max_wal_senders:

   SHOW max_wal_senders;

   If the result is 0, set it to a value greater than 0. We recommend that the value be greater than or equal to 64:

   ALTER SYSTEM SET max_wal_senders=64;

3. Check max_replication_slots:

   SHOW max_replication_slots;

   If the result is 0, set it to a value greater than 0. We recommend that the value be greater than or equal to 64:

   ALTER SYSTEM SET max_replication_slots=64;

4. Restart the PostgreSQL service for the changes to take effect.

Allow DTS to connect to the source database

1. Add the following rules to pg_hba.conf to allow DTS to connect:

   host all all 100.104.0.0/16 md5
   host replication all 100.104.0.0/16 md5

2. Reload the configuration without restarting the service:

   SELECT pg_reload_conf();

   Expected output:

    pg_reload_conf
   ----------------
    t
   (1 row)

Install the ali_decoding extension

The ali_decoding extension enables DTS to parse incremental data from PostgreSQL 9.x. Install it by compiling it from source alongside the matching PostgreSQL version.

This example uses PostgreSQL 9.4.14 on CentOS 7.9. Replace version numbers with those from your environment.

Step 1: Download and compile the PostgreSQL source code

1. Download the PostgreSQL source code that matches your database version from the PostgreSQL FTP site:

   wget https://ftp.postgresql.org/pub/source/v9.4.14/postgresql-9.4.14.tar.gz

2. Create a working directory and move the package:

   mkdir postgres
   mv ~/postgresql-9.4.14.tar.gz postgres

3. Extract the source:

   cd postgres/
   tar -zxvf postgresql-9.4.14.tar.gz

4. Create the installation directory:

   cd postgresql-9.4.14/
   mkdir -p /usr/local/pgsql94

5. Configure, compile, and install:

   Important

   The --prefix path must differ from the installation path of your existing PostgreSQL service. The GCC version must be compatible with the PostgreSQL version you are compiling. If sudo ./configure fails with readline library not found, use sudo ./configure --without-readline instead.

   sudo ./configure --prefix=/usr/local/pgsql94
   sudo make
   sudo make install

   After each command, run echo $? to verify success. A return value of 0 means the step succeeded. If a non-zero value is returned, fix the reported error before continuing.

Step 2: Download and install the ali_decoding extension

1. Download the ali_decoding_94 branch:

   Important

   Use the ali_decoding_94 branch. Other branches are not compatible with PostgreSQL 9.x.

   sudo wget https://github.com/YeFuner/rds_dbsync/archive/refs/heads/ali_decoding_94.zip
   unzip ali_decoding_94.zip

2. Copy the ali_decoding directory into the contrib directory of the compiled source:

   cd rds_dbsync-ali_decoding_94/
   cp -r ali_decoding/ /home/postgres/postgresql-9.4.14/contrib/
   cd /home/postgres/postgresql-9.4.14/contrib/ali_decoding/

   Replace /home/postgres/postgresql-9.4.14/contrib/ with the actual path where you extracted the PostgreSQL source.

3. Replace the content of the Makefile in the ali_decoding directory with:

   # contrib/ali_decoding/Makefile
   MODULE_big = ali_decoding
   MODULES = ali_decoding
   OBJS    = ali_decoding.o
   
   DATA = ali_decoding--0.0.1.sql ali_decoding--unpackaged--0.0.1.sql
   
   EXTENSION = ali_decoding
   
   NAME = ali_decoding
   
   ifdef USE_PGXS
   PG_CONFIG = pg_config
   PGXS := $(shell $(PG_CONFIG) --pgxs)
   include $(PGXS)
   else
   subdir = contrib/ali_decoding
   top_builddir = ../..
   include $(top_builddir)/src/Makefile.global
   include $(top_srcdir)/contrib/contrib-global.mk
   endif

4. Compile and install the extension:

   sudo make
   sudo make install

   After each command, verify with echo $?.

5. Install the compiled files into the running PostgreSQL instance:

   # Find the lib directory and install the shared library
   /usr/local/pgsql94/bin/pg_config --pkglibdir
   # Returns: /usr/local/pgsql94/lib
   
   /usr/bin/install -c -m 755 ali_decoding.so '/usr/local/pgsql94/lib/ali_decoding.so'
   /usr/bin/install -c -m 755 ali_decoding.so '/usr/local/pgsql94/lib/'

   # Find the share directory and install the extension control files
   /usr/local/pgsql94/bin/pg_config --sharedir
   # Returns: /usr/local/pgsql94/share
   
   /usr/bin/install -c -m 644 ali_decoding.control '/usr/local/pgsql94/share/extension/'
   /usr/bin/install -c -m 644 ali_decoding--0.0.1.sql ali_decoding--unpackaged--0.0.1.sql '/usr/local/pgsql94/share/extension/'

Create an RDS instance, account, and database

1. Create an RDS instance. For more information, see Create an ApsaraDB RDS for PostgreSQL instance.

2. Create a privileged account on the RDS instance. This account runs the migration. For more information, see Create an account.

3. Create a database on the RDS instance. For more information, see Create a database.

The database name on the RDS instance must match the source database name exactly, and the database owner must be the privileged account used for the migration.

Create matching extensions on the RDS instance

Run \dx on the source database to list the extensions in use, then create the same extensions on the destination database.

If PostGIS is installed and the versions differ between source and destination, run the following on the destination database to match the source version:

CREATE EXTENSION IF NOT EXISTS postgis_raster;

Validate compatibility before cutover

Before stopping traffic to the source database, create a full backup of the destination RDS instance and clone it to a test environment. Verify application compatibility against the cloned instance and resolve any issues before proceeding with the cutover.

For more information on cloning an RDS instance, see Restore data of an ApsaraDB RDS for PostgreSQL instance.

Stop traffic and switch connections

1. In the DTS console, wait until the task enters the Incremental Data Migration phase and the data synchronization latency drops below 1 second. Stop the application and stop writing data to the source database.

2. Stop the application and block the service account from connecting to the source database. Add the following rules at the top of pg_hba.conf (this example uses test_acc1 as the service account):

   host all test_acc1 0.0.0.0/0 reject
   host replication test_acc1 0.0.0.0/0 reject

   Reload the configuration:

   SELECT pg_reload_conf();

3. Terminate all remaining connections to the source database, preserving the migration account:

   SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND usename NOT IN ('migrate_test');

4. Verify that the service account is blocked by attempting to connect with it. The following error confirms the block is active:

   FATAL:  pg_hba.conf rejects connection for host "127.0.0.1", user "test_acc1", database "postgres"

5. Synchronize sequence values from the source to the destination. Run the following PL/pgSQL block on the source database to generate the setval statements:

   DO LANGUAGE plpgsql $$
   DECLARE
     nsp name;
     rel name;
     val int8;
   BEGIN
     FOR nsp,rel IN SELECT nspname,relname FROM pg_class t2 , pg_namespace t3 WHERE t2.relnamespace=t3.oid AND t2.relkind='S'
     LOOP
       EXECUTE format($_$SELECT last_value FROM %I.%I$_$, nsp, rel) INTO val;
       raise notice '%',
       format($_$SELECT setval('%I.%I'::regclass, %s);$_$, nsp, rel, val+1);
     END LOOP;
   END;
   $$;

   The output contains one setval statement per sequence, for example:

   NOTICE:  select setval('public.my_sequence'::regclass, 2);

   Run each returned statement on the destination RDS instance.

6. Configure a data backflow task in DTS to send any new incremental data from the RDS instance back to the self-managed PostgreSQL database. Set the source as the RDS instance and the destination as the self-managed database. If the task type is incremental data migration only, do not select schema synchronization or full data synchronization.

   Important

   Complete this step before starting the application.

7. Update all application connection strings to the RDS instance endpoint and start the applications. For more information on finding the RDS endpoint, see View and change the endpoints and port numbers.

8. Monitor the applications and confirm they run as expected.