Migrating PostgreSQL Databases to Cloud SQL for PostgreSQL using Google Cloud Platform

Introduction:

Migrating databases can be a complex task, but with the Database Migration Service provided by Google Cloud Platform, it becomes more streamlined and efficient. In this blog, we will explore how to migrate a stand-alone PostgreSQL database to Cloud SQL for PostgreSQL using a continuous migration job and VPC peering for connectivity. We will cover the necessary preparations, execution of the migration job, and promoting the Cloud SQL instance as a stand-alone database.

Preparation Steps:

Before initiating the migration, a few preparatory steps need to be taken to ensure a successful transfer of data. These steps include:

1. Create a dedicated user with replication rights: This user will be responsible for replication and data migration from the source database to the Cloud SQL instance. Granting appropriate permissions is crucial for a smooth migration.

2. Add the pglogical database extension: The pglogical extension enables logical replication, allowing continuous synchronization of data between the source and destination databases. Install and enable this extension on the source database.

3. Grant rights to the schemata and tables: To ensure proper migration, grant the necessary rights to the schemata and tables in the source database that need to be migrated. Additionally, grant rights to the “postgres” database.

4. Migration Steps: Once the preparatory steps are completed, we can proceed with the actual migration process using the Database Migration Service and VPC peering for connectivity. Follow these steps:

5. Set up VPC peering: VPC peering establishes a secure connection between the source database and the Cloud SQL instance. Configure VPC peering to enable secure and efficient data transfer.

6. Create a continuous migration job: Configure the Database Migration Service to create a continuous migration job. This type of job ensures that any updates made to the source database are applied in real-time to the Cloud SQL instance.

7. Start the migration job: Initiate the migration job and monitor the progress. The Database Migration Service provides detailed logs and progress indicators to track the migration process.

8. Verify the initial database migration: After the migration job completes, verify that the initial copy of the database has been successfully migrated to the Cloud SQL for PostgreSQL instance. Confirm the integrity and accuracy of the migrated data.

9. Continuous Migration and Data Updates: One of the key advantages of using the continuous migration job is the ability to synchronize ongoing changes made to the source database. The Database Migration Service automatically applies data updates from the source database to the Cloud SQL instance. This ensures that both databases remain in sync during the migration process.

10. Promoting Cloud SQL Instance: Once the migration is successfully completed and the continuous migration job is running, it is time to promote the Cloud SQL instance as a stand-alone database for reading and writing data.

Follow these steps:

i. Review and test the migrated database:

• Before promoting the Cloud SQL instance, thoroughly review and test the migrated database to ensure data consistency and functionality.

ii. Update the application connection details:

• Modify the application’s connection details to point to the newly migrated Cloud SQL instance. Update relevant configuration files or connection strings accordingly.

iii. Promote the Cloud SQL instance:

• Promote the Cloud SQL instance to be a stand-alone database, allowing it to handle read and write operations independently. This completes the migration process.

Conclusion:

Migrating PostgreSQL databases to Cloud SQL for PostgreSQL using Google Cloud Platform’s Database Migration Service and VPC peering simplifies the process of transferring data between databases. By following the necessary preparatory steps, creating a continuous migration job, and promoting the Cloud SQL instance, you can seamlessly migrate your database and ensure data consistency. The combination of these services and features empowers businesses to take advantage of the scalability and reliability offered by Cloud SQL.

References:

1. Google Cloud Database Migration Service Documentation: Link
2. PostgreSQL Documentation: Link
3. Google Cloud SQL for PostgreSQL Documentation: Link
4. Google Cloud VPC Peering Documentation: Link
5. Google Cloud Platform Blog: Link

About the Author:

Emmanuel Odenyire Anyira is a Senior Data Analytics Engineer at Safaricom PLC. With extensive experience in designing and building data collection systems, processing pipelines, and reporting tools, Emmanuel has established himself as a thought leader in the field of data analytics and infrastructure management. He possesses expertise in various technologies, including Apache NiFi, Informatica PowerCenter, Tableau, and multiple programming languages. Emmanuel’s passion for automation and optimizing workflows has driven him to share his insights and expertise through writing and speaking engagements.