PostgreSQL Migration API documentation version v1

https://pg-migrate.cf.{landscape}/v1/

• landscape: required (string)

  The Cloud Foundry Landscape.

  Example:

  eu10.hana.ondemand.com

About

The PostgreSQL Migration REST API and the pg-migrate plugin for the Cloud Foundry command line tool can be used to trigger and manage migrations from PostgreSQL service instances (source) to PostgreSQL, hyperscaler option service instances (target) via a background replication mechanism. Refer to the corresponding Quick Start sections below to learn how to perform a migration. A reference documentation for the REST API is available in the section Reference Documentation.

Overview

Migrating PostgreSQL service instances to PostgreSQL, hyperscaler option service instances is a self-service. It must be performed by the owner of the respective databases using the PostgreSQL Migration REST API or the pg-migrate plugin for the CF CLI.

The background replication mechanism is based on an open source tool known as Bucardo. This means that in many, but not all cases, a migration will cause only a short downtime for your applications, since the majority of database content has already been transferred before the migration is actually finalized.

The principle steps of a migration are the following:

1. Grant network access and permissions to the migration service. This will enable the migration service to access the PostgreSQL which is required to be able to transfer the data.
2. Perform a preflight check using the REST API or the CF plugin. This will analyze your source and destination databases for issues that could potentially block a successful migration.
3. You must at least fix the "critical" issues reported by the preflight check before you can continue with the migration. This might require changes to your source and/or destination database, or to your applications.
4. Start the automatic background replication mechanism using the REST API or the CF plugin.
5. Wait till the source and destination databases are in sync. The automatic background replication mechanism will keep this state, until you are ready to finalize the migration.
6. Stop your applications.
7. Finalize the migration using the REST API or the CF plugin. This will transfer the content of tables that could not be replicated in the background (see Limitations below).
8. Wait till the finalization is completed.
9. Bind your applications to the PostgreSQL, hyperscaler option service instance and restart them.
10. Decommission the source database.

Performing the finalization step during a blue-green deployment is in principle possible, but note that you must ensure that none of your application instances writes to either the source or destination database until the finalization is complete.

Limitations

1. Both PostgreSQL and PostgreSQL, hyperscaler option service instances must be in the same Cloud Foundry org and space.

2. The migration of roles and access permissions is not supported.

3. The migration of large objects is not supported.

4. The migration of trial instances is not supported.

5. DDL operations after starting the migration are not supported and could cause errors during the migration.

6. Background replication is not possible for tables without primary key or other suitable primary key constraint. Such tables have to be transferred at the end of a migration in a "finalization" step (see below). This will cause a prolonged downtime for your applications.

7. Write operations during the finalization step are not supported.

8. The destination database just supports TLS connections. If your applications do not support TLS yet, adapt your applications first and migrate the data afterwards.

9. Parallel migrations for the same PostgreSQL service instance are not supported.

10. The migration of a database with more than 1000 schemas or more than 30000 relations is not supported.

11. During the start of the replication, the PostgreSQL migration service must have an opportunity to temporarily obtain a lock for all tables. Please verify, depending on the load on the source database, that there are no long running row/table locking queries until the replication has status RUNNING.

Recommendations

1. The storage size of the PostgreSQL, hyperscaler option instance should be at least 30 % more or 10 GB more, whichever is higher than the current storage consumption of the PostgreSQL instance. The reason is the PostgreSQL, hyperscaler option contains besides the customer data PostgreSQL log files and some temporary files used by the PostgreSQL server for its working.

Troubleshooting

1. In case your migration failed and you want to start all over again delete the finalization and replication and use a new destination database.

Quick Start - CF CLI Plugin

The pg-migrate plugin for the Cloud Foundry command line tool can be used to prepare, perform and finalize PostgreSQL migrations. It is a convenience wrapper for the PostgreSQL Migration REST API (see below).

1. Download PostgreSQL Migration Service (cf_cli_pg_migrate_plugin) and install the pg-migrate plugin for the Cloud Foundry command line tool. Please consider the terminal language has to be English to ensure the plugin works properly.

   cf install-plugin cf_cli_pg_migrate_plugin-linux-amd64 -f (Linux)
   cf install-plugin cf_cli_pg_migrate_plugin-darwin-amd64 -f (MacOS)
   cf install-plugin cf_cli_pg_migrate_plugin-windows-amd64.exe -f (Windows)
   

2. Allow network access from your source PostgresSQL instance to the background replication system by updating the corresponding service instance and providing a custom parameter prepare_migration.

   NOTE: The service instance update to set prepare_migration to true can be executed at any point in time. The recommendation is to execute the service instance update upfront (e. g. during a prior release) to reduce the time of the actual migration process.

   cf update-service <<source instance name>> -c '{"prepare_migration": "true"}'
   

3. Create service keys for both the source and target service instances with the Cloud Foundry cf create-service-key command.

   cf create-service-key <<source instance name>> <<source instance key name>>
   cf create-service-key <<target instance name>> <<target instance key name>>
   

   IMPORTANT: In case you are migrating on GCP landscapes, you must add the following custom parameter during the creation of the service key for the target instance '{"enable_session_replication": true}'. Additionally, this service key must not be used after the migration on GCP.

   cf create-service-key <<target instance name>> <<target instance key name>> -c '{"enable_session_replication": true}'
   

4. Target the source and target database instances with the command cf pg-migrate target providing the service instances and service keys as parameters.

   IMPORTANT: Setting the target again will overwrite the current target and all further cf pg-migrate requests will refer to the new target.

   cf pg-migrate target <<source instance name>> <<source instance key name>>
       <<target instance name>> <<target instance key name>>
   

5. Perform a preflight check to detect potential issues for the migration. These can be critical issues or simply warnings. If there are critical issues, the migration will fail. In such a case, critical issues must be resolved before starting the migration.

   cf pg-migrate preflight-check
   

6. If the result of the preflight check suggests that your database instances are suited for an automatic migration, setup a replication with the command:

   cf pg-migrate create-replication
   

7. Poll the status of the replication with the command:

   cf pg-migrate replication
   

   Possible status values are WAITING, RUNNING, IN-SYNC,FAILED and COMPLETED. In case the status turns to FAILED, contact support.

8. Once the replication is in status IN-SYNC you can start the finalization of the migration any time. The replication mechanism will keep the databases synchronized until the finalization is completed.

9. Before starting the finalization, make sure to stop all applications that are bound to the source database and disconnect all other clients that might have access to it. Read access is still possible, though, through the finalization phase.

10. Trigger the finalization with the command:

    cf pg-migrate create-finalization
    

11. Poll the status of the finalization with the command:

    cf pg-migrate finalization
    

    Possible status values are WAITING, RUNNING, FAILED and COMPLETED.

12. Once the finalization is in status COMPLETED you can rebind your applications to the target service instance and restart them. The migration is complete. In case the status turns to FAILED, restart your applications to resume normal operation using the source PostgreSQL instance and contact support.

13. Validate that your applications are working properly with the PostgreSQL, hyperscaler option.

14. Decommission the source PostgresSQL instance by deleting the corresponding service key and service instance:

    cf delete-service-key <<source instance name>> <<source instance key name>>
    cf delete-service <<source instance name>>
    

Quick Start - PostgreSQL Migration REST API

The PostgreSQL Migration REST API can be used to prepare, perform and finalize PostgreSQL migrations.

1. Allow network access from your source PostgresSQL instance to the background replication system by updating the corresponding service instance and providing a custom parameter prepare_migration.

   NOTE: The service instance update to set prepare_migration to true can be executed at any point in time. The recommendation is to execute the service instance update upfront (e. g. during a prior release) to reduce the time of the actual migration process.

   cf update-service <<source instance name>> -c '{"prepare_migration": "true"}'
   

2. Create service keys for both the source and target service instances with the Cloud Foundry cf create-service-key command.

   cf create-service-key <<source instance name>> <<source instance key name>>
   cf create-service-key <<target instance name>> <<target instance key name>>
   

   IMPORTANT: In case you are migrating on GCP landscapes, you must add the following custom parameter during the creation of the service key for the target instance '{"enable_session_replication": true}'. Additionally, this service key must not be used after the migration on GCP.

   cf create-service-key <<target instance name>> <<target instance key name>> -c '{"enable_session_replication": true}'
   

3. You must send a valid OAuth token with each request to the PostgreSQL Migration REST API. Use the command cf oauth-token to obtain an OAuth token. Copy the output of the command including the bearer prefix. Example:

   $ cf oauth-token
   bearer eyJhbGciOiJSUzI1Ni...
   

   Add an Authorization header to the request with the schema Bearer followed by the OAuth token. Do not base64-encode the token like you would do for Basic authentication. Note, the expiration time of tokens obtained with cf oauth-token is quite short for security reasons (~10 minutes). If the REST API starts to reject your requests with 403 Forbidden you must obtain a fresh token.

4. You must send the following parameters with each request to the REST API:

   • org: The name of the organization where the service instances are located.
   • space: The name of the space where the service instances are located.
   • source_service_instance_name: The name of the service instance associated with the source PostgreSQL database.
   • target_service_instance_name: The name of the service instance associated with the target PostgreSQL database.
   • source_service_key_name: The name of a service key referring to the source service instance.
   • target_service_key_name: The name of a service key referring to the target service instance.

   For GET requests provide the parameters in a query string. Example:

   GET /v1/preflight-check?org=the-org&space=the-space&
        source_service_instance_name=the-instance&
        target_service_instance_name=another-instance&
        source_service_key_name=the-key&
        target_service_key_name=another-key
   

   For all other requests, provide the parameters in the request body in JSON format. The Content-Type of the request must be application/json. Example:

   {
      "org": "the-org",
      "space": "the-space",
      "source_service_instance_name": "the-instance",
      "target_service_instance_name": "another-instance",
      "source_service_key_name": "the-key",
      "target_service_key_name": "another-key"
   }
   

5. Perform a preflight check to detect potential issues for the migration. These can be critical issues or simply warnings. If there are critical issues, the migration will fail. In such a case, critical issues must be resolved before starting the migration.

   A preflight check can be performed with a GET request to the /v1/preflight-check endpoint of the REST API. The response format will be application/json. Example:

   curl -X GET -H 'Authorization: Bearer <OAUTH_TOKEN>'
      "https://pg-migrate.cf.{landscape}/v1/preflight-check?org=the-org&space=the-space..."
   
   HTTP/1.1 200 OK
   {
      "sourceDatabaseIssues": {
         "critical": [
               "Unknown data types are not support for PostgreSQL version 10 or higher"
         ],
         "warning": [
               "Multiple roles found (beware that no role will be migrated)",
               "Multiple access privileges found (beware that no privilege will be migrated)"
         ]
      },
      "targetDatabaseIssues": {
         "critical": [],
         "warning": [
               "Database is not empty (3 objects found)"
         ]
      }
   }
   

   This request may take a while to complete.

6. If the result of the preflight check suggests that your database instances are suited for an automatic migration, setup a replication with a POST request to the /v1/replication endpoint of the REST API. Example:

   curl -X POST
      -H 'Content-Type: application/json'
      -H 'Authorization: Bearer <OAUTH_TOKEN>'
      -d '{
      "org": "the-org",
      "space": "the-space",
      ...
      }'
      "https://pg-migrate.cf.{landscape}/v1/replication"
   

   The response status is 202 Accepted in case of success and a Location header is provided with the URL of the new replication resource. This URL can be polled subsequently to retrieve the status of the running replication.

7. Poll the status of the replication with GET requests to the endpoint /v1/replication of the REST API. Example:

   curl -X GET -H 'Authorization: Bearer <OAUTH_TOKEN>'
      "https://pg-migrate.cf.{landscape}/v1/replication?org=the-org&space=the-space..."
   

   The response status is 200 OK in case of success and the body contains a JSON object with a single attribute status. Possible status values are WAITING, RUNNING, IN-SYNC, FAILED and COMPLETED. In case the status turns to FAILED, contact support.

8. Once the replication is in status IN-SYNC you can start the finalization of the migration any time. The replication mechanism will keep the databases synchronized until the finalization is completed.

9. Before starting the finalization, make sure to stop all applications that are bound to the source database and disconnect all other clients that might have access to it. Read access is still possible, though, through the finalization phase.

10. Trigger the finalization with a POST request to the /v1/finalization endpoint of the REST API. Example:

    curl -X POST
       -H 'Content-Type: application/json'
       -H 'Authorization: Bearer <OAUTH_TOKEN>'
       -d '{
       "org": "the-org",
       "space": "the-space",
       ...
       }'
       "https://pg-migrate.cf.{landscape}/v1/finalization"
    

    The response status is 202 Accepted in case of success and a Location header is provided with the URL of the new finalization resource. This URL can be polled subsequently to retrieve the status of the running finalization.

11. Poll the status of the finalization with GET requests to the endpoint /v1/finalization of the REST API. Example:

    curl -X GET -H 'Authorization: Bearer <OAUTH_TOKEN>'
       "https://pg-migrate.cf.{landscape}/v1/finalization?org=the-org&space=the-space..."
    

    The response status is 200 OK in case of success and the body contains a JSON object with a single attribute status. Possible status values are WAITING, RUNNING, FAILED and COMPLETED.

12. Once the finalization is in status COMPLETED you can rebind your applications to the target service instance and restart them. The migration is complete. In case the status turns to FAILED, restart your applications to resume normal operation using the source PostgreSQL instance and contact support.

13. Validate that your applications are working properly with the PostgreSQL, hyperscaler option.

14. Decommission the source PostgresSQL instance by deleting the corresponding service key and service instance:

    cf delete-service-key <<source instance name>> <<source instance key name>>
    cf delete-service <<source instance name>>
    

Reference Documentation