Migrating Your PostgreSQL Database to the Cloud

Now that EnterpriseDB has launched the Postgres Plus Cloud Database, it's easy to get a database running in the cloud. This blog covers the next step: migrating a PostgreSQL database that you already have running on traditional hardware to your new cloud database.

To provide a full example, this entry uses a simple web application written in Vaadin. I'm running it on a GlassFish 3.1.1 application server (supports Java EE 6), and the application talks to a PostgreSQL 9.1 database through a JDBC connection that's configured in the server. This means there's only one place I need to change my connection information once the data has been migrated.

The application itself is a very simple database for storing information about wine. I'll give a walkthrough of the application in a subsequent blog, followed by a version that runs on Tomcat that could be deployed to Amazon Elastic Beanstalk.

For better viewing, here is a direct link to the video embedded below.

After setting up the remote database in the cloud, the migration happens in three steps:

1. Back up the existing database to a file.
2. Restore the data into the new database.
3. Point the application to the new database location (and restart if necessary).

For the screen cast, I'm using all command line tools. For more information on using the PostgreSQL tools from the command line, see this article from EnterpriseDB.

Note that the GlassFish administration can be performed from the command line as well. To see a list of the properties that are set, you can use the asadmin "get" command and grep for the information (if you don't already know the property names):

BobbyMac}> ./asadmin get "*" | grep wine | grep property
resources.jdbc-connection-pool.winedb-pool.property.PortNumber=5432
resources.jdbc-connection-pool.winedb-pool.property.User=postgres
resources.jdbc-connection-pool.winedb-pool.property.ServerName=localhost
resources.jdbc-connection-pool.winedb-pool.property.Password=DemoPass
resources.jdbc-connection-pool.winedb-pool.property.DatabaseName=wine_db

Similarly, you can use the asadmin "set" command to change these values. Then you can redeploy the application to pick up the new values.