how to transfer data from one azure cosmos db for postgresql to another azure cosmos db for postgresql

Question

I have a working azure cosmos db for postgresql, for some reason we want to delete. I now want to move the data to a new azure cosmos db for postgresql, so I can destroy the old one. How to do this in azure

Answer 1

@Tung Nguyen Xuan
Thank you for the question and for using Microsoft Q&A platform.

To move data from your existing Azure Cosmos DB for PostgreSQL instance to a new one, you can follow these steps. This involves creating a new instance, migrating the data, and then verifying everything is working before destroying the old one.

Step 1: Set Up a New Azure Cosmos DB for PostgreSQL Cluster

1. Create a new Azure Cosmos DB for PostgreSQL cluster:
   • Go to the Azure portal.
   • Click Create a resource and search for Azure Cosmos DB for PostgreSQL.
   • Choose PostgreSQL as the database engine, then set up the cluster (nodes, compute, storage).
   • Select the region and configure other parameters like database name, users, and networking.
2. Configure the New Cluster:
   • Set up the same schema and user roles in the new database as in your old one.
   • You can either manually create the tables and schema or generate schema scripts from the old database.

1. Data Migration Approaches

Azure Database Migration Service (DMS)

1. Set Up Azure Database Migration Service:
   • Go to the Azure portal and create a Database Migration Service instance.
   • Configure the service with the necessary permissions and network settings.
2. Create a Migration Project:
   • Select PostgreSQL as both the source and target types.
   • Set the source as the existing Cosmos DB for PostgreSQL cluster and the target as the new cluster.
3. Run the Migration:
   • Select the tables and databases you want to migrate.
   • DMS will handle the migration, including schema and data transfer, with minimal downtime.

2. Azure Data Factory:

Azure Data Factory (ADF) is a powerful tool for data integration and transformation. It allows you to create data pipelines to move data between various sources and destinations, including Azure Cosmos DB for PostgreSQL.

3. pg_dump and pg_restore:

These are PostgreSQL utilities that can be used to export and import data. You can use pg_dump to create a backup of your existing database and pg_restore to import the data into the new database.

4. Azure Cosmos DB Live Data Migrator:

This tool supports live data migration from one Cosmos DB container to another, allowing for continuous data migration with minimal downtime.

For more information you can refer to the below links:
https://www.cloudiqtech.com/migrating-from-postgresql-to-cosmosdb-using-azure-data-factory/
https://github.com/AzureCosmosDB/data-migration-desktop-tool
https://learn.microsoft.com/en-us/azure/cosmos-db/how-to-migrate-desktop-tool?tabs=azure-cli
https://github.com/Azure-Samples/azure-cosmosdb-live-data-migrator
https://github.com/dimitri/pgcopydb
https://learn.microsoft.com/en-us/answers/questions/1127034/live-migration-to-cosmosdb-for-postgresql

Hope this helps. Do let us know if you have any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.