Migrate from PostgreSQL to Zilliz Cloud

PostgreSQL is a robust, open-source, object-relational database engine renowned for its extensibility, data integrity, and performance. By utilizing the pgvector extension, PostgreSQL gains the capability to store and manage vector data. Migrating from PostgreSQL to Zilliz Cloud allows users to leverage Zilliz Cloud's advanced search and analytics features.

The migration process is structured into these steps:

1. Connect to data source: Enter your PostgreSQL database endpoint and username & password to establish a connection.

2. Select source and target:

   • Choose one or more PostgreSQL tables for migration.

   • Select an existing Zilliz Cloud cluster as the target.

3. Configure schema: Verify that field types are correctly mapped between PostgreSQL and Zilliz Cloud. For detailed mapping rules, refer to Mapping rules.

Mapping rules

The following table summarizes how field types in PostgreSQL are mapped to Zilliz Cloud field types, along with details on any customization options.

PostgreSQL Field Type	Zilliz Cloud Field Type	Description
Primary key	Primary key / Auto ID	Single-field primary key: Mapped directly as the target collection's primary key. Absence of primary key: Auto ID is enabled for the target collection to support tables without a primary key. Composite primary key: Auto ID is enabled; composite keys are treated as regular scalar fields. When migrating data, you can enable Auto ID. However, if you do so, the original primary key values from your source collection will be discarded.
vector	FLOAT_VECTOR	Vector dimensions remain unchanged.
text/varchar/date/time	VARCHAR	Stored as a string.
bigint	INT64	-
integer	INT32	-
smallint	INT16	-
double precision	DOUBLE	-
real	FLOAT	-
boolean	BOOL	-
array	ARRAY	-
json	JSON	-

Before you start

• The source PostgreSQL database is accessible from the public internet.

• If you have an allowlist configured in your network environment, ensure that Zilliz Cloud IP addresses are added to it. For more information, refer to Zilliz Cloud IPs.

• You have been granted the Organization Owner or Project Admin role. If you do not have the necessary permissions, contact your Zilliz Cloud administrator.

• Make sure the CU size can of the target cluster can accommodate your source data. To estimate the required CU size, use the calculator.

Migrate from PostgreSQL to Zilliz Cloud

1. Log in to the Zilliz Cloud console.

2. Go to the target project and select Migrations > PostgreSQL.

   

3. In the Connect to Data Source step, enter connection information to the source PostgreSQL database, and click Next.

   📘Notes

   For details on connection information, refer to Connecting to the Database.

   

4. In the Select Source and Target step, configure settings for the source database and target Zilliz Cloud cluster. Then, click Next.

   📘Notes

   Each table you choose to migrate from PostgreSQL must include a vector field.

   

5. In the Configure Schema step, set up field mappings between Zilliz Cloud and PostgreSQL:

   

   1. Confirm field mappings:

      • Zilliz Cloud automatically detects and displays your PostgreSQL fields alongside their corresponding target fields. For details on how these fields are mapped, refer to Mapping rules.

      • Verify that each PostgreSQL field is correctly paired with its corresponding target field. You can rename fields as needed, but note that the data type cannot be changed.

   2. Handle scalar fields:

      For scalar fields, optionally configure the following attributes:

      • Nullable: Decide whether a field can accept null values. This feature is enabled by default. For details, refer to Nullable & Default.

      • Default Value: Specify a default value for a field. For details, refer to Nullable & Default.

      • Partition Key: Optionally designate an INT64 or VARCHAR field as the partition key. Note: Each collection supports only one partition key, and the selected field cannot be nullable. For details, refer to Use Partition Key.

   3. Enable dynamic field:

      • Dynamic fields are enabled by default. This allows you to include any scalar fields that are not defined in the collection schema.

      • If you disable it, you need to explicitly define each field in your entity before inserting data. For more information, refer to Dynamic Field.

   4. (Optional) Adjust shards:

      • Click Advanced Settings to configure the number of shards for your target collection.

      • For datasets of around 200 million rows with 768 dimensions, a single shard is typically sufficient.

      • If your dataset exceeds 1 billion rows, contact us to discuss optimal shard configuration for your use case.

6. Click Migrate.

Monitor the migration process

Once you click Migrate, a migration job will be generated. You can check the migration progress on the Jobs page. When the job status switches from In Progress to Successful, the migration is complete.

Post-migration

After the migration job is completed, note the following:

• Index Creation: The migration process does not automatically create indexes for vector fields when migrating from external data sources. You must manually create the index for each vector field. For details, refer to Index Vector Fields.

• Manual Loading Required: After creating the necessary indexes, manually load the collections to make them available for search and query operations. For details, refer to Load & Release.

📘Notes

Once you have completed indexing and loading, verify that the number of collections and entities in the target cluster matches the data source. If discrepancies are found, delete the collections with missing entities and re-migrate them.

Cancel migration job

If the migration process encounters any issues, you can take the following steps to troubleshoot and resume the migration:

1. On the Jobs page, identify the failed migration job and cancel it.

2. Click View Details in the Actions column to access the error log.