Pivotal Knowledge Base

Follow

How can I import data into my database service?

Purpose

When procuring a database service through the Marketplace, you'll be give a default account with the service provider.  In many cases, you need to initialize the service or load data before you can begin using it. This can be done either programmatically through your application or manually through a client.  This article discusses some of the common ways to do that.

Instructions

Option #1 - Direct Access

In most cases, service providers allow access to your service from the public internet. As a result, it is possible to use various tools to administer and manage your service instance. In order to use these tools you’ll need the following:

  1. Service Credentials: The account credentials for your service can be obtained through different mechanisms. From the PWS web interface. The first option is to click on the manage link on the corresponding services in the “Services” section on the space page. This will open up the service provider’s management page and the credentials will be available there. The second option is to bind the service instance to an application. Once bound it is possible to visit the application page and obtain the appropriate credentials underneath the services tab “Show Credentials” link. If you are using the cli, ‘cf env <app>’ to show the VCAP_SERVICES environment variable which will contain the services credentials.

  2. Management Client. This is an application that will provide an interface to your service. Some examples are

    MySQL: MySQL cli, Navicat, SequelPro
    Postgres: Navicat
    Mongo: Mongochef
    Redis: Redis cli

    Additional clients can be found with an internet search. PWS makes no endorsement of these products.

    Configure your client with the services credentials and once connected you will be able to create schemas, upload existing database dumps and otherwise administer your service.

Option #2 - PWS App

Some PWS Service providers do not allow access to their services from the open internet, for these services you can simply run an app on PWS that will perform the access for you. There are two strategies for doing this, use an administration app or write a custom app.

Web Based Administration App

Most of the database technologies available on PWS have existing web based administration tools. Most of these tools can be deployed to PWS to provide you with a nice GUI from which to modify your service data. Exactly what functionality you get depends on the administration app, but this can include tasks like schema management, data backup and data imports.

Some tips to keep in mind when using a web based administrative app.

  • There are many handy web based administrative apps that you can use on PWS, with two of the most popular being PHPMyAdmin and PHPPgAdmin.  Just keep in mind that should you decide to use one of these apps and have trouble with it you'll need to contact the author of the application.  Pivotal does not endorse or support any third party or community developed applications.

  • Not all web based administrative applications will run on PWS out-of-the-box.  Most do, but need minor adjustments so that they properly connect to your services.  In most cases, you’ll need to adjust the application so that it’s load the service connection information from the VCAP_SERVICES environment variable instead of an application specific configuration file. For more details on what apps run or tips for making a specific app run on PWS, please see this community maintained list of apps.

  • When you’re running a web based administrative application on PWS it will incur cost as long as that application is running.  We recommended that you stop the administrative application when you are not using IT to minimize your cost.

  • The security of the web based administrative app is up to you.  Because PWS is a public platform and your web based administrative app will be publicly accessible, you'll want to make sure that you properly secure it.  If left unsecured, the app could provide a way for an attacker to read, steal or alter your data.

Custom Script or App

When there is no existing administration utility for your service or the existing utilities do not provide the functionality that you require then you'll need to write your own script or application to interact with your service.  Since you’re writing the tool it can be customized exactly to your workload be that setting up a database, importing data, exporting a backup or something else entirely.

When writing a custom app, there are a few things to keep in mind.

  • If you’re writing an application or script that does not have a web GUI, push the application with the --no-route option enabled. This tells PWS that the script will not be listening for web connections (i.e. it's just a cli app).

  • Make sure the script does not exit when it's done.  Instead of exiting, just log a message to STDOUT indicating the task is complete and have the app sleep indefinitely.  This will prevent the system from restarting your application, which is the default behavior when an application exits, and possibly running it multiple times.
  • Prior to pushing your application to PWS, we suggest that you open a second terminal and run `cf logs` there.  In that terminal, you’ll see the output of the application as it runs and you can watch for when it completes.  If you have a task that will run for a long period of time and leaving a terminal open for that long is not practical, you'll want to look for some other notification method, like sending an email, so that you know when the application is finished.
  • When your application is done running, you can simply delete it.  This will stop the application from running and stop you from being billed for it.  This will also delete any logs.  If you need to review the logs, please do so prior to deleting the application or have the logs sent to a third party logging service.
  • You can access the service credentials from any bound service through the VCAP_SERVICES environment variable.  More information on this and examples can be found in this KB article.

Comments

Powered by Zendesk