PolarDB:Overview of upgrading an ApsaraDB RDS for PostgreSQL instance to a PolarDB for PostgreSQL cluster

General limitations

• Cross-region migration is not supported.

• Source instance parameters cannot be changed during migration.

• During migration, DTS automatically creates an account in the destination cluster with a name in the dts_clone% format and a randomly generated password. Do not change or delete this account until migration is complete.

Source instance requirements

Tables that you want to synchronize must have a PRIMARY KEY or UNIQUE constraint with all fields unique. Without this, the destination database may contain duplicate records.

If you select individual tables as the sync scope and need to rename them or their columns in the destination, a single task can sync up to 5,000 tables. For more than 5,000 tables, configure multiple tasks or sync the entire database instead.

Write-ahead logging (WAL) requirements:

After full data synchronization completes, you can reduce the retention period to more than 24 hours.

Do not modify the endpoints or zone of the source instance during synchronization. Doing so causes the synchronization task to fail.

Long-running transactions can cause WAL log accumulation in the source database, potentially exhausting disk space.

SQL statement support

DML: INSERT, UPDATE, and DELETE are supported.

DDL: The following DDL statements are supported if you use a privileged account on the source instance and the instance runs minor engine version 20210228 or later. For information about updating the minor engine version, see Update the minor engine version.

• CREATE TABLE and DROP TABLE

• ALTER TABLE (including RENAME TABLE, ADD COLUMN, ADD COLUMN DEFAULT, ALTER COLUMN TYPE, DROP COLUMN, ADD CONSTRAINT, ADD CONSTRAINT CHECK, and ALTER COLUMN DROP DEFAULT)

• TRUNCATE TABLE (source instance runs PostgreSQL 11 or later)

• CREATE INDEX ON TABLE

DDL statements not synced:

• DDL statements containing CASCADE or RESTRICT

• DDL statements from sessions that have run SET session_replication_role = replica

• DDL statements committed in the same transaction as DML statements

• DDL statements for objects outside the sync scope

Other limitations

• Each data synchronization task syncs only one database. For multiple databases, create a separate task for each.

• If you create a table or use RENAME during schema-level synchronization, run the following statement before writing data to that table. Run this during off-peak hours without locking the table to avoid a deadlock.

  ALTER TABLE schema.table REPLICA IDENTITY FULL;

  Replace schema and table with the actual schema name and table name.

• DTS creates the following temporary tables in the source database. Do not delete them during synchronization — they are automatically removed after the DTS instance is released: public.dts_pg_class, public.dts_pg_attribute, public.dts_pg_type, public.dts_pg_enum, public.dts_postgres_heartbeat, public.dts_ddl_command, public.dts_args_session

• DTS adds a heartbeat table named dts_postgres_heartbeat to the source database to maintain synchronization latency accuracy.

• DTS creates a replication slot prefixed with dts_sync_ in the source database. Historical replication slots are automatically cleared every 120 minutes.

  DTS automatically deletes the replication slot when the instance is released. If you change the database password or delete the DTS IP address whitelist entries during synchronization, the replication slot cannot be deleted automatically — delete it manually from the source database to avoid storage accumulation and potential source instance unavailability. If the synchronization task is released or fails, DTS clears the replication slot automatically. After a primary/secondary switchover in the source PostgreSQL database, log on to the secondary database to clear the replication slot manually.

• During full data synchronization, concurrent INSERT operations cause fragmentation in the tables of the destination database. After full data synchronization is complete, the tablespace of the destination database is larger than that of the source database.

• For table-level data synchronization, if data is written to the destination database only through DTS, you can use Data Management (DMS) to perform online DDL operations. For more information, see Perform lock-free DDL operations.

• Write data to the destination database only through DTS during migration. Writing through other tools risks data inconsistency and potential data loss if you later run online DDL operations through Data Management (DMS).

• DTS does not validate metadata such as sequences. Check sequence validity manually.

• After switching workloads to the destination cluster, new sequence values do not automatically continue from the maximum value in the source. Before switching, query the maximum sequence values in the source and set them as the initial values in the destination. Use the following statement to query the maximum sequence values:

  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;
  $$;