Use rds_online_migrate to convert a standard table to a partitioned table in PostgreSQL - ApsaraDB RDS

The rds_online_migrate extension converts a standard PostgreSQL table into a partitioned table — or re-partitions an existing partitioned table — while the database stays online. It works by streaming live changes from the source table to a new partitioned table using logical replication, then atomically renaming both tables when data is fully synchronized.

Decide if this approach fits your situation

Before you begin, confirm that online partitioning is the right approach for your case:

Condition	Guidance
Your table has no `PRIMARY KEY` and no `REPLICA IDENTITY`	This feature cannot work. Logical replication requires a row identity.
The source table has associated views, triggers, or foreign key constraints	After the rename step, you must manually rebuild these objects. Plan for this additional work before proceeding.
The source table is in an existing logical replication publication	The rename step breaks that replication task. You must add the renamed table back to the publication after conversion.
None of the above apply	Online partitioning is suitable. Proceed with the prerequisites.

Prerequisites

Before you begin, ensure that you have:

An ApsaraDB RDS for PostgreSQL instance running major version 13 or later and minor engine version 20251130 or later
The wal_level parameter set to logical in the ApsaraDB RDS console — required by Logical Decoding, which this feature depends on
Free storage space of at least twice the total size of the source table and all its indexes — the process creates a full copy of the data during migration
A user account with permissions to create a publication, subscription, and replication slot, and to RENAME the source and destination tables
The business account granted the same permissions on the destination partitioned table as it has on the source table, to prevent access disruptions after the rename step
A verified full backup of the database

Important

Test the procedure in a non-production environment before running it on production data.

Note

To run multiple partitioning tasks in parallel, you might need to increase the value of the max_worker_processes parameter accordingly.

Convert a standard table to a partitioned table

The following example converts a standard table named public.test into a hash-partitioned table with four partitions.

Step 1: Set the WAL level

In the ApsaraDB RDS console, set the wal_level parameter to logical. This enables Logical Decoding, which powers the live data sync. Without this setting, the extension cannot create the replication publication.

Step 2: Create the source table and test data

-- Create the source table
CREATE TABLE public.test (id int4 PRIMARY KEY, info text);

-- Insert 1,000,000 rows of test data
INSERT INTO public.test
SELECT x, repeat(x::text, 2)
FROM generate_series(1, 1000000) AS x;

Step 3: Install the extension

CREATE EXTENSION rds_online_migrate;

Step 4: Create the destination partitioned table

Create the partitioned table in the same schema as the source table. The example below uses hash partitioning on the id column with four partitions (MODULUS 4):

CREATE TABLE public.test_p (LIKE test INCLUDING ALL) PARTITION BY HASH(id);

CREATE TABLE public.test_p_0 PARTITION OF public.test_p FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE public.test_p_1 PARTITION OF public.test_p FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE public.test_p_2 PARTITION OF public.test_p FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE public.test_p_3 PARTITION OF public.test_p FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Step 5: Run the conversion

Call rds_online_migrate.rewrite_table to start the process:

SELECT rds_online_migrate.rewrite_table('public.test', 'public.test_p');

Parameter	Description	Example
First	Fully qualified name of the source table	`'public.test'`
Second	Fully qualified name of the destination partitioned table	`'public.test_p'`

A return value of t means the conversion completed successfully:

 rewrite_table
---------------
 t
(1 row)

Step 6: Verify the result

After conversion, the extension automatically renames the tables:

public.test is renamed to public.test_rds_bkp — a backup copy you can delete after verification
public.test_p is renamed to public.test — now the active partitioned table

Check the table schema

testdb=> \d

Expected output:

                   List of relations
 Schema |     Name     |       Type        |   Owner
--------+--------------+-------------------+-----------
 public | test         | partitioned table | rds_super
 public | test_p_0     | table             | rds_super
 public | test_p_1     | table             | rds_super
 public | test_p_2     | table             | rds_super
 public | test_p_3     | table             | rds_super
 public | test_rds_bkp | table             | rds_super
(6 rows)

Verify data integrity

Confirm the total row count matches the original, and that rows are distributed across partitions:

-- Backup table row count (should equal the original count before migration)
SELECT count(*) FROM public.test_rds_bkp;
-- Expected output: 1000000

-- New partitioned table row count (should match the backup)
SELECT count(*) FROM public.test;
-- Expected output: 1000000

-- Per-partition counts (should sum to 1000000)
SELECT count(*) FROM public.test_p_0;  -- Expected: 249589
SELECT count(*) FROM public.test_p_1;  -- Expected: 250376
SELECT count(*) FROM public.test_p_2;  -- Expected: 249786
SELECT count(*) FROM public.test_p_3;  -- Expected: 250249

Post-conversion tasks

After the rename step, the following objects are no longer associated with the new partitioned table and must be rebuilt manually:

Views that reference the original table
Triggers defined on the original table
Foreign key constraints that reference the original table

If the original table was part of a logical replication publication, add the new table back to that publication:

ALTER PUBLICATION <publication_name> ADD TABLE public.test;

Exception handling

This feature is non-atomic: if the process is interrupted, internal objects may remain and need manual cleanup. Check the error log to identify which objects are affected, then run the relevant cleanup commands:

-- Clean up the metadata record
DELETE FROM rds_online_migrate.internal_map
WHERE src_relname = 'source_table' AND dst_relname = 'destination_table';

-- Remove the residual publication (if one was created)
DROP PUBLICATION publication_name;

-- Remove the residual subscription (if one was created)
DROP SUBSCRIPTION subscription_name;

-- Remove the residual replication slot (if one was created)
SELECT pg_drop_replication_slot('slot_name');