Synchronizing PostgreSQL Vector Data to RDS for PostgreSQL

Scenarios

Semantic search plays a crucial role in scenarios such as e-commerce recommendation, content search, LLM-based knowledge base Q&A, and multimodal retrieval. The foundation of semantic search is vector data. An embedding model converts data such as text and images into high-dimensional vectors, and then measures semantics by calculating the similarity between vectors. With the vector extension, PostgreSQL can support vector types such as vector, halfvec, and sparsevec, as well as vector indexes such as IVFFlat and HNSW. This enables enterprises to manage both service data and vector data in a single database and directly perform vector search, without the need to build a dedicated vector database.

Data Replication Service (DRS) can synchronize vector data from PostgreSQL databases in on-premises IDCs, ECS-hosted PostgreSQL databases, and cloud-based PostgreSQL databases to Huawei Cloud RDS for PostgreSQL. This enables enterprises to benefit from managed high availability, low-latency collaboration with cloud-based AI applications, cross-region nearby access, and remote disaster recovery.

In this practice, DRS is used to migrate vector data from PostgreSQL databases in on-premises IDCs to Huawei Cloud RDS for PostgreSQL. Full+incremental synchronization implements long-term real-time synchronization of vector data between the source and destination databases, supporting smooth workload cutover. If the source database is a PostgreSQL database built on another cloud or Huawei Cloud RDS for PostgreSQL, the operations are similar. For details about the differences, see "Differences in Other Source Database Scenarios."

How Migration Works

This practice uses full+incremental synchronization. It includes the following operations:

• Before full synchronization, DRS starts incremental data extraction (by default, the source database test_decoding is used to obtain real-time incremental data) to ensure that the incremental data generated during full synchronization is completely extracted to the DRS instance.
• During full synchronization, DRS migrates table structures (including vector column types and dimensions) and data to the destination database. Vector indexes are migrated together with the table structures.
• After the full synchronization is complete, incremental synchronization automatically starts. The system replays data from the full extraction position and continuously synchronizes changes, applying INSERT, UPDATE, and DELETE operations on vector data in the source database to the destination database in real time to ensure data consistency between the source and destination databases.
• After the incremental synchronization latency approaches 0 (data catch-up), you can start a data comparison task to verify the consistency between the source and destination databases.
• After verifying that the data is consistent, you can start workload cutover (you are advised to perform the cutover during off-peak hours).

Figure 1 Migration principle flowchart

DRS processes the vector type as a common PostgreSQL data type. No additional configuration is required for the vector type in the task. The vector extension must be installed in the destination database in advance to receive vector data. If you select table-level synchronization, DRS does not automatically install the vector extension in the destination database. You need to manually install it before the synchronization (see "Preparing the Source and Destination Databases").

Service List

• Virtual Private Cloud (VPC)
• RDS for PostgreSQL
• Data Replication Service (DRS)
• Data Admin Service (DAS)

Prerequisites

• You have registered with Huawei Cloud and completed real-name authentication and your account balance is not below zero.
• You have obtained the IP address, port number, username, and password of the source database.
• Logical replication has been configured for the source database: wal_level=logical, the test_decoding logical decoding extension is available, and the account has the REPLICATION permission.

Planning and Selection Recommendations

Complete the planning below before migration. Recommendations and selection criteria are provided for each item. You can adjust them based on your actual business requirements.

Procedure

The procedure is as follows:

1. Create a VPC.
2. Create a security group.
3. Create an RDS for PostgreSQL instance.
4. Prepare the source and destination databases.
5. Create a DRS synchronization task.
6. Verify data after synchronization.

Creating a VPC

Create a VPC to prepare network resources for the RDS instance and DRS synchronization instance.

1. Log in to the Huawei Cloud management console and select a region in the upper left corner.
2. Click the service list icon on the left and choose Networking > Virtual Private Cloud.
3. Click Create VPC and configure the following basic information: region, AZ, VPC name, VPC CIDR block (for example, 192.168.0.0/16), subnet name, and subnet CIDR block (for example, 192.168.0.0/24). Click Create Now.
   Figure 2 Creating a VPC
   

4. Return to the VPC list. If the VPC status is available, the VPC has been created.

Creating a Security Group

Create a security group and configure access control rules for the RDS instance to allow access from DRS and applications.

1. Log in to the Huawei Cloud console and select a region.
2. In the service list, choose Networking > Virtual Private Cloud.
3. Choose Access Control > Security Groups, click Create Security Group, enter the required details like the security group name, and click Create Now.
4. Return to the security group list, click the security group name, click the Inbound Rules tab, and click Add Rule. Configure an inbound rule to allow DRS access (protocol: TCP; port: 5432; source: DRS instance IP address or subnet CIDR block).
   Figure 3 Configuring an inbound rule for the security group
   

Creating an RDS for PostgreSQL Instance

1. Log in to the Huawei Cloud console and select a region.
2. In the service list, choose Databases > Relational Database Service.
3. Click Buy DB Instance.
4. Configure basic instance information. Select PostgreSQL as the DB engine, and ensure that the version is no lower than the source database version and at least PostgreSQL 12 (vector support required). Select the primary/standby instance type, and specify the specifications, storage type and capacity, and AZ. Select the VPC and security group created earlier. Configure the database port (default: 5432, which can be changed) and administrator password. Select a default or custom parameter template.
   Figure 4 Buying an RDS for PostgreSQL instance
   

5. Click Buy, confirm the information, and click Submit.
6. Return to the instance list. If the instance status is Available, the instance has been created. To view the floating IP address and port number of the instance, click the instance name on the Instances page and choose Connectivity & Security > Connection Information. The information can be used to configure the destination database on DRS.

   For details about how to buy and configure an instance, see Buying an RDS for PostgreSQL DB Instance.

Preparing the Source and Destination Databases

• Configuring Logical Replication for the Source Database
  1. Configure the following parameters in the postgresql.conf file of the source database and restart PostgreSQL:

     wal_level = logical
     max_replication_slots = 10   # The value must be greater than the number of currently used replication slots. Adjust it based on the actual situation.
     max_wal_senders = 10         # The value must be greater than or equal to the value of max_replication_slots.

  2. Add a line before all configurations in the pg_hba.conf file to allow DRS to connect to the database in replication mode. (Select scram-sha-256 or md5 as the authentication mode based on the value of password_encryption in the source database.)

     host replication <account-name> <DRS-instance-IP-address>/32 md5

     The IP address of the DRS instance is allocated by DRS when you create a synchronization task (see "Creating a DRS Synchronization Task"). You are advised to first create a synchronization task and obtain the IP address of the DRS instance from the Create Synchronization Instance step. Then, enter the IP address back here and run the select pg_reload_conf(); command as user SUPERUSER to reload (or restart PostgreSQL). After that, return to the task and continue to configure the source database connection test.

  3. Check whether test_decoding is available for logical decoding. (Create a temporary replication slot for verification. If no error indicating that test_decoding cannot be accessed is reported, logical decoding is supported. Delete the temporary replication slot after the verification.)

     SELECT * FROM pg_create_logical_replication_slot('temp_decoding_slot', 'test_decoding', true);
     SELECT * FROM pg_drop_replication_slot('temp_decoding_slot');

  4. Set replica identity for the table to be synchronized that does not have a primary key.

     ALTER TABLE <schema>.<table-name> REPLICA IDENTITY FULL;

• Source Database Accounts and Permissions

  Create or specify an account with the REPLICATION permission, and grant it the permissions required for synchronization.

  CREATE USER <account-name> WITH LOGIN REPLICATION PASSWORD '<password>';
  GRANT CONNECT ON DATABASE <database-name> TO <account-name>;
  GRANT USAGE ON SCHEMA <schema> TO < account-name >;
  GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO < account-name >;

  Replace <account-name>, <password>, <database-name>, and <schema> in the example commands with your actual values.

• Checking Source Database Objects
  • It is recommended that tables to be synchronized have primary keys or non-null unique constraints. If a table has no primary key, set replica identity=full as described in step 4 above to support incremental UPDATE/DELETE.
  • The dimensions (typmod, such as vector(3)) of vector columns are correct.
  • The operator classes of vector indexes are complete (such as vector_cosine_ops and halfvec_l2_ops).
  • If a table has a dependent sequence and the destination database does not have a sequence with the same name, the sequence must be included in the synchronization objects.

  The following example demonstrates how to create a table that contains vector types and vector indexes. You can adjust the table structure based on your requirements.

  CREATE TABLE vec_test (
    id int PRIMARY KEY,
    v vector(3),
    hv halfvec(4),
    sv sparsevec(5)
  );
  CREATE INDEX idx_vec_l2 ON vec_test USING ivfflat (v vector_l2_ops) WITH (lists = 100);
  CREATE INDEX idx_vec_cos ON vec_test USING hnsw (v vector_cosine_ops);

• Installing the vector Extension on the Destination Database

  The vector extension must be installed on the destination database, which is the prerequisite for successful vector synchronization. You can run the SQL statement below to query the list of vector extension versions supported by the destination database. If the result is empty, the vector extension is not supported.

  SELECT v.name, v.version, (v.version = e.default_version) AS is_default FROM pg_available_extension_versions v JOIN pg_available_extensions e ON v.name = e.name WHERE v.name = 'vector';

  For details about the kernel versions that support the vector extension, see Supported Extensions.

  Huawei Cloud RDS for PostgreSQL comes with the vector extension pre-installed. You can install it on the console: Log in to the RDS console, click the target instance name, choose Plugins on the left, select the target database, locate the vector extension and click Install. After the installation is complete, you can view and manage it on the Installed page.

  • Table-level synchronization: DRS does not involve extension objects. You need to manually install the vector extension before synchronization.
  • Database-level synchronization: DRS automatically installs the vector extension of the default version on the destination database.
  Figure 5 Installing the vector extension on the destination database (console installation example for table-level synchronization)
  

Creating a DRS Synchronization Task

1. Log in to the Huawei Cloud console and select a region.
2. In the service list, choose Databases > Data Replication Service.
3. In the navigation pane on the left, choose Data Synchronization Management. On the displayed page, click Create Synchronization Task.
4. Configure synchronization instance information. Select a region and project, and enter a task name. Set Data Flow to To the cloud, Source DB Engine to PostgreSQL, and Destination DB Engine to PostgreSQL. Set Network Type to Public network, Destination DB Instance to the RDS instance created in the previous step, Synchronization Instance Subnet to a subnet with available IP addresses, and IP Address Type to IPv4 (or IPv4/IPv6 dual stack). Set Synchronization Mode to Full+Incremental, Public Network Access Policy to EIP, and Specify EIP to an available EIP. Retain the default settings for other parameters. Click Create Now.
   Figure 6 Configuring synchronization instance information
   

5. Configure source and destination database information. Enter the IP address, port number, username, and password of the source database, and the username and password of the destination database. Click Test Connection to test the connectivity. After the connection is successful, click Next. Read the message and click Agree.
   Figure 7 Configuring source and destination database information
   

6. Configure the synchronization task and click Next.
   • Incremental Conflict Policy: Select Overwrite to ensure that the source and destination databases are eventually consistent.
   • Synchronize DML: By default, INSERT, UPDATE, and DELETE are selected.
   • Flow Control: By default, the flow is not controlled. You can enable flow control as required (this parameter takes effect only in the full synchronization phase).
   • Synchronize: To synchronize incremental DDLs, select Incremental DDLs. (You need to pre-create an event trigger or the rds_hwdrs_ddl extension on the source database. For details, see Creating Tables, Event Triggers, and Functions to Implement Incremental DDL Synchronization for PostgreSQL.)

     You can select Databases or Tables for Synchronization Object. If you want to migrate all objects in a single database, select database-level synchronization and move the database to be synchronized to the selected objects. If you only need to synchronize specified tables, select table-level synchronization, select the vector tables and dependent sequences (if any) to be synchronized from the source database objects, and move them to the selected objects. For details about the objects supported by database-level and table-level synchronizations, see Supported Synchronization Objects. In this example, database-level synchronization is selected.

     Figure 8 Configuring the synchronization task
     

7. Pre-check: DRS automatically checks the permissions, versions, parameters, objects, and network of the source and destination databases. Rectify the failed items as prompted and click Check Again until the pass rate reaches 100%. After all items pass the check, click Next.
8. Task confirmation: Set the synchronization task start time (Start upon task creation or Start at a specified time; off-peak hours are recommended), task exception notification, SMN topic, delay threshold, and automatic task termination time for exceptions. Check all configuration items, click Submit, read and select I have read the precautions, and click Submit.
   Figure 9 Confirming and starting the task
   

9. After the task is created, return to the task list and view the task status.

Verifying Data After Synchronization

When the task status changes to Incremental, the full synchronization is complete. You can log in to the destination database to view the migration result.

1. Wait until the task status changes to Incremental.
2. Click the task name to go to the task details page.
3. Choose Synchronization Comparison > Object-Level Comparison and click Compare. After the comparison is complete, view the migration results of tables and indexes.
   Figure 10 Object-level comparison
   

4. Choose Synchronization Comparison > Data-Level Comparison and create a value comparison task to view the data migration result. You are advised to perform data comparison during off-peak hours of the source database. Due to synchronization delay, a few inconsistencies may occur during continuous data writing, making the comparison results unreliable. Before starting a comparison, check that the incremental delay is close to 0 on the task details page. When creating a data comparison task, set Comparison Type to Value and Comparison Method to Dynamic. Dynamic comparison continuously compares newly added, modified, and deleted data in the source database after the existing data comparison is complete. After the comparison starts, click View Results to view the latest comparison result.
   Figure 11 Creating a value comparison task
   
   Figure 12 Viewing the comparison report
   

5. After all workloads are migrated to the destination database, click Stop in the Operation column of the task, read the message, and click Yes to stop the task. Before stopping the task, ensure that the source and destination databases are connected and pay attention to the status of synchronization logs. Do not select Force stop task.
   Figure 13 Stopping a task
   

Precautions

Pay attention to the following before synchronization:

• Before starting a task, ensure that no long-running transaction is started in the source database. Long-running transactions will block the creation of a logical replication slot and cause the task to fail.
• During synchronization, do not modify the port, connection account, password, or permissions of the source or destination database, or directly write data to the destination database. If incremental DDL synchronization is enabled, ensure that the DDL operations executed on the source database are compatible with the destination database. Otherwise, the task may fail or data may be inconsistent.
• During database-level synchronization, if a table without a primary key is added to the source database, set replica identity to full for the table before writing data to it. Otherwise, data may be inconsistent or the task may fail.
• During table-level synchronization, if the destination table already exists, its primary key, vector column type, and dimensions must be the same as those of the source table. The destination table can have more columns than the source table, but the additional columns must be allowed to be empty or have default values. Otherwise, the synchronization may fail.
• By default, PostgreSQL-to-PostgreSQL synchronization supports cascading synchronization, but does not support loopback synchronization (data cannot be synchronized from instance A to instance B and then from instance B to instance A). Multi-instance cascading must avoid loops.

For details, see Precautions.

Billing

The key points of DRS billing rules are as follows:

• Both yearly/monthly and pay-per-use billing are supported. Different specifications correspond to different performance upper limits. For details, see Real-Time Synchronization.
• Tasks using a public network (EIPs) incur data transfer fees. For details, see the EIP price calculator.
• Tasks in the abnormal state are still billed. You can set an automatic stop time for abnormal tasks to avoid unnecessary fees.
• For details about the billing rules, see Billing Overview.

Differences in Other Source Database Scenarios

The differences between the two scenarios below and PostgreSQL databases in on-premises IDCs are mainly in the network access and configuration methods: Managed instances do not allow direct editing of the postgresql.conf or pg_hba.conf file. You need to configure access and parameters based on the cloud provider's management mode. If the database is built on another cloud's ECS, the configuration is the same as that of the on-premises PostgreSQL database.

• PostgreSQL on another cloud as the source
  • Network access: To access Huawei Cloud from another cloud, you can use a public network or VPN/Direct Connect. If a public network is used, the source database must have an EIP bound, and DRS accesses the source database through the public network. If VPN or Direct Connect is used, the source database does not use the public network. You need to establish a private network connection (using VPN, Direct Connect, Cloud Connect, or VPC peering connection) between the source database network and the Huawei Cloud VPC. DRS accesses the private IP address of the source database through the private network connection.
  • Source database information: Enter the IP address (or domain name), port number, username, and password of the source database on the DRS source database information page.
  • Logical replication parameter: Set wal_level to logical via the source cloud's parameter group or parameter management page (corresponding to postgresql.conf in an on-premises database).
  • Access configuration: Allow access from the IP address and port used by DRS via the source cloud's IP address whitelist or security group (corresponding to allowing replication in pg_hba.conf of an on-premises database).
  • Account replication permissions: Grant the REPLICATION permission to the account via the console or the corresponding role.
  • test_decoding: a native PostgreSQL extension that is supported by most enterprise-level PostgreSQL databases (including other cloud-hosted instances). It is a logical decoding extension for incremental synchronization and must be enabled in the source database.
• Huawei Cloud RDS for PostgreSQL as the source
  • Network access and source database information: On the DRS source database information page, set Database Type to RDS DB instance (not Self-built on ECS), select the source DB instance, and enter the username and password. (No VPC, subnet, IP address, port, or SSL is required. RDS is automatically connected through the project intranet.)
  • Logical replication parameter: Log in to the RDS console. On the Instances page, click the target instance name, choose Parameters, and set wal_level to logical. (Follow the prompt regarding whether a reboot is required, as static parameters require an instance reboot.)
  • Access configuration: Click the source instance name, choose Connectivity & Security > Security Group Rules, and add an inbound rule to allow access from DRS (port 5432).
  • Replication permission: When creating an account on the console, select REPLICATION. Alternatively, run the select control_user_privilege('replication', '<account>'); command as the root user.
  • test_decoding: a native logical decoding plugin of PostgreSQL, which is supported by Huawei Cloud RDS for PostgreSQL. DRS requires that test_decoding has been installed in the source database. Ensure that it is available before synchronization.