DocsICP FilingConsole
Documentation
Home Page Data Transmission Service Use Cases Synchronize vector data in a PostgreSQL database

Synchronize vector data in a PostgreSQL database

Updated at:
Copy as MD
Product
My Favorites

In artificial intelligence (AI) applications that use large language models (LLMs), retrieval-augmented generation (RAG) technology retrieves information from external knowledge bases. This technology significantly improves the accuracy and timeliness of the model's output. It solves problems in LLMs, such as slow knowledge updates and hallucinations, which are inaccurate answers. Compared to traditional keyword retrieval, vector retrieval provides semantic similarity search capabilities. It also supports the retrieval of unstructured and multimodal data. The pgvector extension for PostgreSQL adds vector search capabilities while retaining the original structured data management features of PostgreSQL. The data synchronization feature of Data Transmission Service (DTS) can quickly update vector data from a PostgreSQL database to other databases in different regions. This feature helps you implement cross-region disaster recovery, low-latency queries, real-time decision-making, and multi-region data analytics in data-intensive scenarios.

Prerequisites

Overview

  1. Create a database instance

    Create a destination ApsaraDB RDS for PostgreSQL instance.

  2. Create an account

    Create an account for data synchronization in the destination ApsaraDB RDS for PostgreSQL instance.

  3. Create a database

    In the destination ApsaraDB RDS for PostgreSQL instance, create a database to receive data.

  4. Install the extension

    In the destination ApsaraDB RDS for PostgreSQL instance, install the pgvector extension for the destination database to support writing vector data.

  5. Create a data synchronization instance

    Use DTS to perform data synchronization.

Preparations

Note

In this example, a self-managed PostgreSQL database that runs on a server of the Linux operating system is used.

  1. Log on to the server on which the self-managed PostgreSQL database resides.

  2. Run the following command to query the number of used replication slots in the self-managed PostgreSQL database: 

    select count(1) from pg_replication_slots;

  3. Modify the postgresql.conf configuration file. Set the wal_level parameter to logical, and make sure that the values of the max_wal_senders and max_replication_slots parameters are greater than the sum of the number of used replication slots in the self-managed PostgreSQL database and the number of DTS instances whose source database is the self-managed PostgreSQL database. 

    # - Settings -

wal_level = logical			# minimal, replica, or logical
					# (change requires restart)

......

# - Sending Server(s) -

# Set these on the master and on any standby that will send replication data.

max_wal_senders = 10		# max number of walsender processes
				# (change requires restart)
#wal_keep_segments = 0		# in logfile segments, 16MB each; 0 disables
#wal_sender_timeout = 60s	# in milliseconds; 0 disables

max_replication_slots = 10	# max number of replication slots
				# (change requires restart)
    Note

    After you modify the configuration file, restart the self-managed PostgreSQL database for the parameter settings to take effect.

  4. Add the CIDR blocks of DTS servers to the pg_hba.conf configuration file of the self-managed PostgreSQL database. Add only the CIDR blocks of the DTS servers that reside in the same region as the destination database. For more information, see Whitelist DTS server IP addresses.

    Note

    • After you modify the configuration file, execute the SELECTpg_reload_conf(); statement or restart the self-managed PostgreSQL database for the parameter to take effect.

    • For more information about the pg_hba.conf configuration file, see The pg_hba.conf File. Skip this step if the IP address in the pg_hba.conf file is set to 0.0.0.0/0. The following figure shows the configurations.

    IP

  5. Create a database and schema in the RDS instance based on the database and schema information of the objects to be synchronized. The schema names in the source and destination databases must be the same. For more information, see Create a database and Manage accounts by using schemas.

Step 1: Create a database instance

  1. Go to the RDS instance purchase page.

  2. Select the configuration parameters for the instance.

    Set Engine to PostgreSQL version 14, 15, or 16. You can select other parameters as needed. For more information, see Create an ApsaraDB RDS for PostgreSQL instance.

  3. Review the order information, quantity, and subscription duration (for subscription instances only). Click Confirm Order, and then complete the payment. A message is displayed in the console indicating that the instance is successfully created.

    Note

    For subscription instances, we recommend that you Enable Auto-renewal to prevent business interruptions caused by an expired subscription.

    Auto-renewal aligns with the subscription term (monthly or yearly) and can be disabled at any time. For details, see Renew an expired resource and Auto-renewal.

  4. View the instance.

    Go to the Instances page. In the top navigation bar, select the region where the instance is located. Find the instance that you created based on its Creation Time.

    Note

    It takes 1 to 10 minutes to create the instance. You may need to refresh the page to view the instance.

  5. View the minor engine version of the destination ApsaraDB RDS for PostgreSQL instance.

    1. After the instance is created, click the ID of the destination instance.

    2. On the Basic Information page of the destination instance, view the Minor Version Information in the Configuration Information section.

    3. Ensure that the minor engine version of the destination instance is 20230430 or later.

      Note

      If the minor engine version of the destination ApsaraDB RDS for PostgreSQL instance does not meet the requirement, you must upgrade the version. For more information, see Upgrade the minor engine version.

Step 2: Create an account

  1. Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.

  2. In the left-side navigation pane, click Accounts.

  3. Click Create Account.

  4. Set the account parameters.

    Enter a Database Account. Set Account Type to Privileged Account. Set and confirm the account password in the New Password and Confirm Password fields. For more information, see Create an account.

  5. Click OK.

Step 3: Create a database

  1. Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.

  2. In the left-side navigation pane, click Databases.

  3. Click Create Database.

  4. Set the database parameters.

    Enter a Database Name. For Authorized Account, select the account that you created in Step 2: Create an account. You can select other parameters as needed. For more information, see Create a database.

  5. Click Create.

Step 4: Install the extension

  1. Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.

  2. Click Plugin Management in the left navigation bar.

  3. On the Plugin Marketplace tab, click AIGC, and then click Install on the vector card.

    image

  4. In the dialog box that appears, select a Database Name and a Database Account.

    Note

    For Database Name, select the database that you created in Step 3: Create a database. For Database Account, select the account that you created in Step 2: Create an account.

  5. Click Install.

    You can view the plugin installation status on the Plug-ins > Extension Management > Installed Extensions page.

    image

Step 5: Create a data synchronization instance

Note

This section uses the simple mode as an example to describe how to create a data synchronization instance. For more information, see Synchronize data from a self-managed PostgreSQL database to an ApsaraDB RDS for PostgreSQL instance.

  1. Go to the data synchronization task list page in the destination region. You can do this in one of two ways.

    DTS console

    1. Log on to the DTS console.

    2. In the navigation pane on the left, click Data Synchronization.

    3. In the upper-left corner of the page, select the region where the synchronization instance is located.

    DMS console

    Note

    The actual steps may vary depending on the mode and layout of the DMS console. For more information, see Simple mode console and Customize DMS console layout and style.

    1. Log on to the DMS console.

    2. In the top menu bar, choose Data + AI > DTS (DTS) > Data Synchronization.

    3. To the right of Data Synchronization Tasks, select the region of the synchronization instance.

  2. Click Create Task to open the task configuration page.

  3. Optional: In the upper-right corner of the page, click New Configuration Page.

    Note

    • If you are already on the new configuration page (the button in the upper-right corner is Back to Previous Version), you can skip this step.

    • Some parameters differ between the new and old configuration pages. We recommend using the new version.

  4. Configure the source and destination databases.

    Category

    Configuration

    Description

    Source Database

    Database Type

    Select PostgreSQL.

    Access Method

    Select a connection type based on the deployment location of the source database. This example uses Express Connect, VPN Gateway, or Smart Access Gateway.

    Note

    If the source database is self-managed, perform the required preparations. For more information, see Preparations overview.

    Instance Region

    Select the region of the VPC where the self-managed PostgreSQL database is located.

    Replicate Data Across Alibaba Cloud Accounts

    For this example, select No, as the database instance belongs to the current Alibaba Cloud account.

    Connected VPC

    Select the VPC connected to the self-managed PostgreSQL database.

    Domain Name or IP

    Enter the IP address of the server for the self-managed PostgreSQL database.

    Port Number

    Enter the port used by the self-managed PostgreSQL database. The default port is 3433.

    Database Name

    Enter the name of the database that contains the objects to be synchronized in the self-managed PostgreSQL database.

    Database Account

    Enter an account with superuser permissions for the self-managed PostgreSQL database.

    Database Password

    Enter the password for the specified database account.

    Encryption

    Select a method as needed. This example uses the default Non-encrypted connection.

    Destination Database

    Database Type

    Select PostgreSQL.

    Access Method

    Select Alibaba Cloud Instance.

    Instance Region

    Select the region of the ApsaraDB RDS for PostgreSQL instance that you created in Step 1: Create a database instance.

    Instance ID

    Select the ID of the ApsaraDB RDS for PostgreSQL instance that you created in Step 1: Create a database instance.

    Database Name

    Enter the name of the database that you created in Step 3: Create a database.

    Database Account

    Enter the name of the account that you created in Step 2: Create an account.

    Database Password

    Enter the password for the specified database account.

    Encryption

    Select a method as needed. This example uses the default Non-encrypted connection.

  5. After completing the configuration, click Test Connectivity and Proceed at the bottom of the page.

    Note

    • Ensure that you add the CIDR blocks of the DTS servers (either automatically or manually) to the security settings of both the source and destination databases to allow access. For more information, see Add the IP address whitelist of DTS servers.

    • If the source or destination is a self-managed database (i.e., the Access Method is not Alibaba Cloud Instance), you must also click Test Connectivity in the CIDR Blocks of DTS Servers dialog box.

  6. Configure the task objects.

    1. On the Configure Objects page, specify the objects to synchronize.

      Configuration

      Description

      Synchronization Types

      Incremental Data Synchronization is selected by default. This example also selects Schema Synchronization and Full Data Synchronization.

      Synchronization Topology

      This example uses one-way synchronization. Select One-way Synchronization.

      Processing Mode of Conflicting Tables

      Keep the default value, Precheck and Report Errors.

      Source Objects

      In the Source Objects box, select the objects to be synchronized and click 向右 to move them to the Selected Objects box.

      Important

      If a table to be synchronized has a dependent sequence and no sequence with the same name exists in the destination schema, you must also select the sequence in the Source Objects box.

      Selected Objects

      No additional configuration is required for this example. Keep the default settings.

    2. Click Next: Advanced Settings.

      This example does not require any changes. You can keep the default configuration.

    3. Click Next: Data Validation to configure a data validation task.

      This example does not use the data validation feature. You can keep the default configuration.

  7. At the bottom of the page, click Next: Save Task Settings and Precheck.

  8. When the Success Rate reaches 100%, click Next: Purchase Instance.

  9. Purchase the instance.

    1. On the Purchase page, select the billing method and link specification for the data synchronization instance. The following table describes the parameters.

      This example does not require any changes. You can keep the default configuration.

    2. Select Data Transmission Service (Pay-As-You-Go) Terms of Service.

    3. Click Buy and Start, and then click OK in the OK dialog box.

      You can monitor the task progress on the data synchronization page.

Appendix

Notes

Type

Description

Source database limits

  • Tables that you synchronize must have a primary key or a unique constraint. Otherwise, duplicate data can occur in the destination database.

    Note

    If you create the destination table manually (without selecting Synchronization Types as the Schema Synchronization), you must ensure that the destination table has the same primary key or non-null unique constraint as the source table. Otherwise, duplicate data may occur in the destination database.

  • The name of the database to be synchronized cannot contain hyphens (-), for example, dts-testdata.

  • If you synchronize data at the table level and need to edit the objects, such as mapping table or column names, for more than 5,000 tables in a single data synchronization task, we recommend splitting them into multiple tasks or configuring a task to synchronize the entire database. Otherwise, the task may fail upon submission.

  • DTS does not support synchronizing temporary tables, internal triggers (TRIGGER), or certain functions such as C language functions and internal functions for PROCEDURE and FUNCTION from the source database. DTS supports synchronizing some custom data types (TYPE is COMPOSITE, ENUM, or RANGE) and the following constraints: primary key, foreign key, unique, and CHECK.

  • Write-ahead logging (WAL):

    • You must enable WAL by setting the wal_level parameter to logical.

    • For an incremental data synchronization task, DTS requires that the WAL of the source database be retained for more than 24 hours. For a task that performs both full and incremental data synchronization, the WAL must be retained for at least seven days. You can change the retention period to more than 24 hours after the full data synchronization is complete. If a task fails because DTS cannot obtain the WAL due to a shorter retention period, the failure may lead to data inconsistency or loss in extreme cases. Issues caused by an insufficient WAL retention period are not covered by the DTS Service Level Agreement (SLA).

  • If a primary/secondary switchover is performed on the self-managed PostgreSQL database, the synchronization task fails.

  • Ensure that the values of the max_wal_senders and max_replication_slots parameters are both greater than the sum of the number of used replication slots in the database and the number of DTS instances that you want to create with this database as the source.

  • If the source database has long-running transactions and the instance is configured for incremental data synchronization, the write-ahead logging (WAL) data generated before these transactions are committed cannot be cleared. This can cause WAL data to accumulate and may lead to insufficient disk space on the source database.

  • If the source is a Cloud SQL for PostgreSQL instance, you must enter a database account that has the cloudsqlsuperuser permission in the Database Account field. When you select the objects to be synchronized, you must select objects that this account is authorized to manage, or grant the OWNER permission on the objects to this account. For example, run the GRANT <owner_of_objects> TO <source_account_for_task> command to allow the account to perform operations as the owner of the objects.

    Note

    An account that has the cloudsqlsuperuser permission cannot manage data owned by other accounts that also have the cloudsqlsuperuser permission.

  • Due to the inherent limitations of logical subscriptions in the source database, if a single data record to be synchronized exceeds 256 MB after an incremental change, the synchronization instance will fail and must be reconfigured.

  • Do not run DDL operations that change database or table schemas during schema synchronization or full synchronization. Otherwise, the synchronization task fails.

    Note

    During full synchronization, DTS queries the source database. This creates metadata locks that may block DDL operations on the source database.

  • If you perform a major version upgrade on the source database while a synchronization instance is running, it will fail and must be reconfigured.

  • Tables with generated columns in PostgreSQL 18 do not support synchronization. If you configure synchronization for such tables, DML operations on those tables will be blocked.

Other limits

  • A single data synchronization task can synchronize only one database. To synchronize multiple databases, you must configure a separate task for each.

  • DTS does not support synchronizing TimescaleDB plug-in tables, tables with cross-schema inheritance, or tables with expression-based unique indexes.

  • Schemas created by installing plug-ins are not supported for synchronization. You cannot retrieve information about these schemas in the console when you configure a task.

  • If a table to be synchronized contains a SERIAL type column, the source database automatically creates a sequence for that column. Therefore, when you configure Source Objects, if you select Schema Synchronization for Synchronization Types, we recommend that you also select Sequence or synchronize the entire schema. Otherwise, the synchronization instance may fail.

  • In the following three scenarios, you must run the ALTER TABLE schema.table REPLICA IDENTITY FULL; command on the source tables before writing data to them to ensure data consistency. Do not perform table-locking operations while this command is running to prevent deadlocks. If you skip this check during the precheck, DTS automatically runs this command during instance initialization.

    • When the instance runs for the first time.

    • When you select objects at the schema level, and a new table is created in the schema or a table is rebuilt by using the RENAME command.

    • When you use the feature to modify synchronization objects.

    Note

    • Replace schema and table in the command with the names of the schema and table to be synchronized.

    • Perform this operation during off-peak hours.

  • DTS verifies data content but does not support verifying metadata such as sequences. You must verify such metadata yourself.

  • After you switch your business to the destination database, sequences do not automatically continue from the maximum value of the corresponding sequences in the source database. Before the switchover, you must update the sequence values in the destination database. For more information, see Update the sequence values in the destination database.

  • DTS creates the following temporary tables in the source database to obtain DDL statements for incremental data, the schema of incremental tables, and heartbeat information. Do not delete these temporary tables during synchronization. Otherwise, the DTS task may fail. The temporary tables are automatically deleted 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, and public.aliyun_dts_instance.

  • To ensure the accuracy of the displayed synchronization latency, DTS adds a heartbeat table named dts_postgres_heartbeat to the source database.

  • During data synchronization, DTS creates a replication slot with the dts_sync_ prefix in the source database to replicate data. This replication slot allows DTS to obtain incremental logs generated within the last 15 minutes from the source database. When a data synchronization task fails or the instance is released, DTS attempts to automatically remove this replication slot.

    Note

    • If you change the password of the source database account or delete the IP whitelist for DTS during data synchronization, the replication slot will not be cleaned up automatically. Manually clean up the replication slot in the source database to prevent it from consuming disk space and making the source database unavailable.

    • If the source database fails over, log on to the standby database to perform the cleanup manually.

    Run the SQL statement SELECT * FROM pg_replication_slots; to view all replication slots in the source database. In the results, the DTS replication slot is the record where the slot_name value starts with dts_sync_ and the active value is true.

  • Before you start data synchronization, evaluate the performance of the source and destination databases. Perform the synchronization during off-peak hours. Full data synchronization consumes read and write resources on both databases, which can increase their load.

  • Full data synchronization performs concurrent INSERT operations, which can cause table fragmentation in the destination database. As a result, the tablespace of the destination instance will be larger than that of the source instance after full data synchronization is complete.

  • For table-level data synchronization, if no data from sources other than DTS is written to the destination database, you can use DMS to perform online DDL changes. For more information, see Change schemas without locking tables.

  • During DTS synchronization, do not write data to the destination database from other sources. This can cause data inconsistency between the source and destination databases. For example, if you use DMS to perform online DDL changes while data is being written to the destination database from other sources, data loss may occur.

  • For full or incremental data synchronization tasks, if the source tables to be synchronized contain foreign keys, triggers, or event triggers, DTS temporarily sets the session_replication_role parameter to replica at the session level. If the destination database account does not have the required permissions, you must manually set the session_replication_role parameter to replica in the destination database. During this period, if cascade update or delete operations occur in the source database while session_replication_role is set to replica, data inconsistency may occur. After the DTS task is released, change the session_replication_role parameter back to origin.

  • If a task fails, DTS support staff will attempt to restore it within eight hours. During restoration, they may restart the task or adjust its parameters.

    Note

    Only DTS task parameters are modified—not database parameters. Parameters that may be adjusted include those listed in Modify instance parameters.

  • When you synchronize partitioned tables, you must include both the parent table and its child partitions as synchronization objects. Otherwise, data in the partitioned table may become inconsistent.

    Important

    • The parent table of a PostgreSQL partitioned table does not store data directly. All data is stored in the child partitions. A data synchronization task must include the parent table and all its child partitions. Otherwise, data from the child partitions may be missed, causing data inconsistency between the source and destination.

    • Synchronization of partitioned tables and inheritance tables (parent-child tables) across different databases is not supported. Ensure that the partitioned tables and all their partitions, and the parent tables and all their child tables, are in the same database.

Billing

Synchronization type

Pricing

Schema synchronization and full data synchronization

Free of charge.

Incremental data synchronization

Charged. For more information, see Billing overview.

SQL operations supported by incremental synchronization

Operation type

SQL statement

DML

INSERT、UPDATE、DELETE

DDL

  • Only data synchronization tasks created after October 1, 2020 support DDL synchronization.

    Important

  • When the self-managed PostgreSQL source database uses a privileged account and the minor version is 20210228 or later, the synchronization task supports the following DDL operations:

    • CREATE TABLE, DROP TABLE

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

    • TRUNCATE TABLE (The source PostgreSQL database must be PostgreSQL 11 or later.)

    • CREATE INDEX ON TABLE

    Important

    • Additional information contained in DDL statements, such as CASCADE or RESTRICT, is not synchronized.

    • DDL statements executed in sessions that use theSET session_replication_role = replica command are not synchronized.

    • DDL statements executed by calling FUNCTION or similar methods are not synchronized.

    • If a single commit in the source database contains both DML and DDL statements, the DDL statements are not synchronized.

    • If a single commit in the source database contains DDL statements for objects that are not being synchronized, those DDL statements are not synchronized.

Previous：Use the create reverse task featureNext：Security and Compliance