Migrating to a Google Cloud SQL for PostgreSQL Instance

This topic tells you how to migrate a GCP Relational Database Service (Google Cloud SQL) for PostgreSQL instance from the GCP Service Broker for VMware Tanzu tile to the Tanzu Cloud Service Broker for GCP tile.

About Migrating Data to Google Cloud SQL for PostgreSQL Instance

Because the GCP Service Broker for VMware Tanzu (hereafter referred to as the legacy broker) tile is going out of support, it is important to move from PostgreSQL instances that were created by the legacy broker over to the Cloud Service Broker for GCP.

The Cloud Service Broker for GCP plans are configurable. When migrating, examine the configuration of the plans in use with the legacy broker and create matching plans in the Cloud Service Broker for GCP.

You can use the GCP Database Import and Export tool to migrate data between Google Cloud SQL for PostgreSQL Instances. The tool copies data from one database to a GCP Bucket and from there to another database instance. Because the original database is not modified, you can reverse the migration if you detect any problems.

Matching Configuration

Both the legacy broker and the Cloud Service Broker for GCP allow you to customize service plans. Create plans in the Cloud Service Broker for GCP that match the plans used in the legacy broker. It is mandatory for the new service instances to be created with the same major version of PostgreSQL as the old instances and with the same, or larger, available disk size. It might be useful to create a test service instance and compare the properties in the GCP console. For instructions on how to configure plans, see Configure Services with Cloud Service Broker for GCP.

Migrating Data

You might want to migrate data from instances created with the legacy broker to instances created with the Cloud Service Broker for GCP.

Important
Migration of data might incur app downtime. The amount of downtime depends on the method chosen.

There are many options for performing data migration, including GCP SQL Import and Export, manual data migration, and options available from other vendors. For more information about the data migration process, refer to the documentation for the option that you choose.

In general, the data migration steps are:

Create a backup of the PostgreSQL instance.
Create a Google Cloud SQL for PostgreSQL instance using the Cloud Service Broker for GCP.
Restore a backup of the data from a legacy broker PostgreSQL instance into the newly created instance.
Unbind apps from the legacy broker PostgreSQL instance and bind them to the newly created instance.
Once the migration has been proved to be successful, the legacy instance may be deleted.

For more detailed steps showing the Cloud Foundry and gcloud commands necessary to use GCP SQL Import and Export, see Migrate Data from a GCP Service Broker for VMware Tanzu PostgreSQL Instance.

Migrate Data from a GCP Service Broker for VMware Tanzu PostgreSQL Instance

Important
Because the GCP SQL Import and Export tool does not support a streaming or continuous replication, consider putting your application in read-only mode while the migration is ongoing.

To migrate data from an existing legacy PostgreSQL instance to the Cloud Service Broker for GCP:

Create a bucket to hold the backup files by running:
```
gsutil mb gs://BACKUP-BUCKET-NAME
```
Where BACKUP-BUCKET-NAME is a name you choose, which must be lowercase.
Get the VCAP_SERVICES JSON for the legacy PostgreSQL instance by running:
```
cf env APP-NAME
```
Where APP-NAME is the name of an app that is bound to a service instance from the legacy broker.
From the VCAP_SERVICES JSON, record value of database_name, which is your OLD-INSTANCE-DB-NAME, and instance_name, which is your OLD-INSTANCE-ID.
Create a new PostgreSQL service instance using Cloud Service Broker for GCP by running:
```
cf create-service csb-google-postgres PLAN-NAME NEW-SERVICE-INSTANCE-NAME -b cloud-service-broker-gcp -c '{"db_name":"OLD-INSTANCE-DB-NAME", "database_version": "OLD-INSTANCE-PG-VERSION" }'
```
Where:
- PLAN-NAME is the name of the Cloud Service Broker for GCP plan you want to use for the new instance. Ensure this plan matches the plan used in the legacy broker.
- NEW-SERVICE-INSTANCE-NAME is the name you want for your new PostgreSQL service instance.
- OLD-INSTANCE-DB-NAME is the database_name you recorded in the previous step.
- OLD-INSTANCE-PG-VERSION is the same PostgreSQL version as your old service instance.
Retrieve the GCP database instance ID for the new service instance. The GCP database instance ID is the Cloud Foundry service instance GUID with the prefix csb-postgres- added to it. This is a string similar to csb-postgres-XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX.

To get the Cloud Foundry service instance GUID, run:
```
cf service NEW-SERVICE-INSTANCE-NAME --guid
```

Create a backup of the old service instance with GCP Import and Export by running:

// Give the old database instance service account access to the newly created bucket.

INSTANCE_SERVICE_ACCOUNT_NAME=$(gcloud sql instances describe OLD-INSTANCE-ID --format json | jq -r .serviceAccountEmailAddress)
gsutil acl ch -u "${INSTANCE_SERVICE_ACCOUNT_NAME}:W" gs://BACKUP-BUCKET-NAME

// Run the export
gcloud sql export sql OLD-INSTANCE-ID "gs://BACKUP-BUCKET-NAME/backup.sql" -d OLD-INSTANCE-DB-NAME

Where:

OLD-INSTANCE-ID is the instance_name you recorded in an earlier step.
BACKUP-BUCKET-NAME is the name of the bucket to hold the backup files you created in an earlier step.
OLD-INSTANCE-DB-NAME is the database_name you recorded in an earlier step.

Prepare the database for the import. GCP Import and Export can cause problems with permissions on database objects created by activating extensions.

Create a file called prepare.sql with the following content:

create role "binding_user_group" with login;
grant all privileges on all tables in schema public to "binding_user_group";
grant "cloudsqlsuperuser" to "binding_user_group";
do language plpgsql
$$
    declare
        rec record;
    begin
    for rec in
        select usename from pg_catalog.pg_user where usename not like 'cloud%' and usename not in ( 'postgres', 'binding_user_group')
        loop
            execute format('grant "binding_user_group" to %I', rec.usename);
        end loop;
    for rec in
        select datname from pg_catalog.pg_database where datname not in ('cloudsqladmin', 'postgres')
        loop
            execute format('grant all privileges on database %I to "binding_user_group"', rec.datname);
        end loop;
    end
$$

Copy the prepare.sql file to the bucket:

gsutil cp ./prepare.sql "gs://BACKUP-BUCKET-NAME/prepare.sql"

Create a file called cleanup.sql with a SQL sentence to change the characteristics of the role used to reassign objects.

alter role "binding_user_group" with nologin

Copy the cleanup.sql file to the bucket:

gsutil cp ./cleanup.sql "gs://BACKUP-BUCKET-NAME/cleanup.sql"

Where BACKUP-BUCKET-NAME is the name of the bucket to hold the backup files you created in an earlier step.

(Optional) Verify the contents of the export by inspecting the contents of the created SQL dump file. To do so, download it using gsutil by running:
```
gsutil cp "gs://BACKUP-BUCKET-NAME/backup.sql" PATH-TO-SQL-DUMP-FILE
```
Where PATH-TO-SQL-DUMP-FILE is the filepath where you want to download the local copy of the dump file.

You can inspect the dump file with your text editor or tooling of choice.

Restore the backup of the old service instance into the new service instance. For example:

// Give the new database instance service account access to the newly created bucket.

INSTANCE_SERVICE_ACCOUNT_NAME=$(gcloud sql instances describe NEW-INSTANCE-ID --format json | jq -r .serviceAccountEmailAddress)
gsutil acl ch -u "${INSTANCE_SERVICE_ACCOUNT_NAME}:R" 'gs://BACKUP-BUCKET-NAME/*'

// Run the prepare

gcloud sql import sql NEW-INSTANCE-ID "gs://BACKUP-BUCKET-NAME/prepare.sql" -d OLD-INSTANCE-DB-NAME

// Run the import

gcloud sql import sql NEW-INSTANCE-ID "gs://BACKUP-BUCKET-NAME/backup.sql" -d OLD-INSTANCE-DB-NAME --user binding_user_group

// Run the cleanup

gcloud sql import sql NEW-INSTANCE-ID "gs://BACKUP-BUCKET-NAME/cleanup.sql" -d OLD-INSTANCE-DB-NAME

Where:

NEW-INSTANCE-ID is the GCP database instance ID for the new service instance you retrieved in an earlier step.
BACKUP-BUCKET-NAME is the name of the bucket to hold the backup files you created in an earlier step.
OLD-INSTANCE-DB-NAME is the database_name you recorded in an earlier step.

Disconnect the app from the legacy service binding by running:
```
cf unbind-service APP-NAME LEGACY-SERVICE-INSTANCE-NAME
```
Where:
- APP-NAME is an app using the legacy PostgreSQL instance.
- LEGACY-SERVICE-INSTANCE-NAME is the name of the GCP Service Broker for VMware Tanzu-brokered PostgreSQL instance.
For example:
```
$ cf unbind-service my-app my-old-instance
```
Bind the app to the new service instance by running:
```
cf bind-service APP-NAME NEW-SERVICE-INSTANCE
```
Where:
- APP-NAME is an app that you unbound in the previous step.
- NEW-SERVICE-INSTANCE is the name of the Cloud Service Broker for GCP service instance that you created in an earlier step.
For example:
```
$ cf bind-service my-app my-csb-gcp-instance
```
Because Cloud Service Broker for GCP creates new credentials at bind time, this creates new binding credentials for the app.
Restage the app:
```
cf restage APP-NAME
```
After the migration is successful, you can remove the legacy service instance.