Migrating a Database in Cloud Foundry

<%= modified_date %>

This page assumes you are using cf CLI v6.

Application development and maintenance often requires changing a database schema, known as migrating the database. This topic describes three ways to migrate a database on Cloud Foundry.

Migrate Once

This method executes SQL commands directly on the database, bypassing Cloud Foundry. This is the fastest option for a single migration. However, this method is less efficient for multiple migrations because it requires manually accessing the database every time.

Note: Use this method if you expect your database migration to take longer than the timeout that cf push applies to your application. The timeout defaults to 60 seconds, but you can extend it up to 180 seconds with the -t command line option.

Run cf env and obtain your database credentials by searching in the VCAP_SERVICES environment variable:

$ cf env db-app
Getting env variables for app my-db-app in org My-Org / space development as admin...
OK

System-Provided:
{
  "VCAP_SERVICES": {
    "example-db-n/a": [
      {
        "name": "test-777",
        "label": "example-db-n",
        "tags": ["mysql","relational"],
        "plan": "basic",
        "credentials" : {
          "jdbcUrl": "jdbcmysql://aa11:[email protected]:3306/ad_01",
          "uri": "mysql://aa11:[email protected]:  34/ad_01?reconnect=true",
          "name": "ad_01",
          "hostname": "cdbr-05.example.net",
          "port": "1234",
          "username": "aa11",
          "password": "2b"
        }
      }
    ]
  }

Connect to the database using your database credentials.
Migrate the database using SQL commands.
Update the application using cf push.

Migrate Occasionally

This method requires you to:

Create a schema migration command or script.
Run the migration command when deploying a single instance of the application.
Re-deploy the application with the original start command and number of instances.

This method is efficient for occasional use because you can re-use the schema migration command or script.

Create a schema migration command or SQL script to migrate the database. For example:

rake db:migrate
Deploy a single instance of your application with the database migration command as the start command. For example:

cf push APP -c 'rake db:migrate' -i 1

Note: After this step the database has been migrated but the application itself has not started, because the normal start command is not used.
Deploy your application again with the normal start command and desired number of instances. For example:

cf push APP -c 'null' -i 4

Note: This example assumes that the normal start command for your application is the one provided by the buildpack, which the -c 'null' option forces Cloud Foundry to use.

Migrate Frequently

This method uses an idempotent script to partially automate migrations. The script runs on the first application instance only.

This option takes the most effort to implement, but becomes more efficient with frequent migrations.

Create a script that:
- Examines the instance_index of the VCAP_APPLICATION environment variable. The first deployed instance of an application always has an instance_index of “0”. For example, this code uses Ruby to extract the instance_index from VCAP_APPLICATION:
  
  instance_index = JSON.parse(ENV["VCAP_APPLICATION"])["instance_index"]
- Determines whether or not the instance_index is “0”.
- If and only if the instance_index is “0”, runs a script or uses an existing command to migrate the database. The script or command must be idempotent.
Create a manifest that provides:
- The application name
- The command attribute with a value of the schema migration script chained with a start command.
Example partial manifest:
```
 ---
  applications:
  - name: my-rails-app
    command: bundle exec rake cf:on_first_instance db:migrate && bundle exec rails s -p $PORT -e $RAILS_ENV
```
Update the application using cf push.

For an example of the migrate frequently method used with Rails, see Running Rake Tasks.