Performance issues moving data between Cloud SQL instances (PostgreSQL)

[Gaston Abalde]

Hi. I'm having performance issues trying to move a large amount of data between two Cloud SQL instances using Postgre.

Are there any guidelines or best practices to move data between two Cloud SQL instances? Not the entire database, just some tables, directly between instances.

I'm using the Foreign Data Wrapper extention in Postgre to archive the migration.

Thanks in advance.

[cristianrm]

In the Cloud SQL for PostgreSQL documentation are some articles related to importing/exporting data.

Regarding the Best practices for importing and exporting data, here are some of them:

• Don't use Cloud Storage Requester Pays buckets.

• Use serverless export to offload the export operation from the primary instance.

• Use the correct flags when you create a SQL dump file.

• Compress data to reduce cost.

• Reduce long-running import and export processes.

• Verify the imported database.

Also known issues are referred to in this documentation.

As shown in this Stack Overflow answer, you can use pg_dump to extract the table. This page describes exporting and importing data into Cloud SQL instances using pg_dump and pg_restore.

The postgres_fdw is an extension allows tables from other ("foreign") PostgreSQL databases to be exposed as "foreign" tables in the current database. Currently, this extension works for two Cloud SQL private IP instances within the same VPC network, or for cross databases within the same instance.

To connect to databases within the same instance, you cannot set host to localhost or to 127.0.0.1. Instead, you must use the IP address shown for your instance in the Google Cloud console.

Additionally, you cannot choose, in the Google Cloud console, the Allow only SSL connections button for an instance where foreign data is stored. Only cloudsqlsuperuser can be the owner of a postgres_fdw foreign data wrapper.