Migrating Existing Data

Last Updated October 2014

Once you've created a database on Pagoda Box, chances are you have existing data you need to migrate. There's a few different approaches you can take. This doc will give an overview of those approaches and touch on a few things you should be aware of. After reading it, you should be familiar with:

  • Different approaches to migrating data
  • Things to be aware of when migrating data

Different Approaches to Migrating Data

There are three main methods of migrating data into your database: using the Pagoda Tunnel, through SSH port forwarding, or in a Deploy Hook.

Using the Pagoda Tunnel (Recommended)

The Pagoda CLI's "tunnel" command opens a secure remote connection to your data service and allows you to manage it using a local client. It essentially sets up SSH port forwarding (but is a lot easier to understand).

When you run the tunnel command, it will provide the credentials necessary to connect to your live data service. Note that the connection is bound to a port on your localhost that you specify. That's the port to which you need to connect.

Tunneling Into a Data Service Terminal

  $ pagoda tunnel -a app-name database1 -p 3306
  Enabling tunnel...

  Tunnel established, use the following credentials to connect:

  Host      : localhost
  Port      : 3306
  Username  : jennie
  Password  : x8F0pLcn
  Database  : gopagoda

  (note : ctrl-c To close this tunnel)

With the tunnel open, use the provided credentials and any local client you'd like to connect to your live data service and import your existing data.

Through SSH Port Forwarding

SSH Access into databases allows you to connect directly to your database from your local desktop and manage it using your tool of choice. It's as simple as enabling SSH access on your database service, setting up SSH port forwarding, then connecting to your databases process using the proper connection details. You can then import data the same way you would on a local database.

Most modern database GUI's even have an SSH connection option making it possible to securely connect to your database without ever getting close to a command line prompt. More information in the SSH Access documentation.

Note: The Pagoda Tunnel does this for you. This method is only necessary if you don't have the Pagoda CLI installed.

Through a Deploy Hook

Deploy Hooks allow you to add commands or custom scripts to your app's deploy process enabling you to import data as part of the deploy process. This method is great for including sample data in a Quickstart, but may not be the best method for a one-time import.

To use a deploy hook to import your data, include your data-dump in your repo or place it in your network storage. Your database is only accessible to before_deploy and after_deploy hooks, so either can be used to run an import. The following is an example of a MySQL import run through a deploy hook. Note that the php_max_execution_time has been increased, allowing time for the import to complete.

Example MySQL DB Import Deploy Hook YAML

    - "mysql -h $DATABASE1_HOST -P $DATABASE1_PORT -u $DATABASE1_USER -p$DATABASE1_PASS $DATABASE1_NAME < var/www/path/to/sqldump.sql"

On deploy, your data-dump will be imported into your database.

Remove the Hook from Your Boxfile After it Run

If this is a one-time import, be sure to remove the deploy hook from your Boxfile. If left in, the import will run on each deploy, overwriting any changes to the data since the last import.

If creating a Quickstart, include the script in your Boxfile.install. The Boxfile.install is only honored when the application is created.

Things to Be Aware of

Service RAM Limits

It's important to recognize that data-imports only have access to the resources allotted to the service they're using. If the resource limit is maxed out, the import will fail.

For example, you cannot import a 1GB database dump into a 128MB RAM database. The 128MBs of RAM will be consumed and the import will fail. You may need to scale your database up to run an import successfully.

If you have any questions, suggestions, or corrections, let us know.