How to Move an Existing Postgres Database into a Cloud Database Cluster

April 15, 2012

How to Move an Existing Postgres Database into a Cloud Database Cluster
Version 1.02

by EnterpriseDB Corporation
Copyright © 2012 EnterpriseDB Corporation. All rights reserved.

Introduction

This EnterpriseDB tutorial leads you through the process of moving an existing PostgreSQL or Advanced Server database into Cloud Database. The process consists of three basic steps:

    1. Use the pg_dump utility on the source database to create an archive containing the commands needed to re-create and populate the source database on Cloud Database.

    2. Copy the archive to the target Cloud Database cluster.

    3. Use pg_restore on the target database to uncompress and play the SQL commands contained in the archive.

This tutorial moves an existing database from a Postgres server to an equivalent Cloud Database cluster (i.e., from Advanced Server version 9.0 into a Cloud Database cluster that is running Advanced Server version 9.0). Additional steps may be required if the server versions differ.

This tutorial assumes:

    ● You have already obtained Cloud Database, and are an administrative user.

    ● You have an existing PostgreSQL or Advanced Server database.

    ● You have modified your Cloud Database security group, opening port 22 for connections.

If you need more information about obtaining Cloud Database, or registering a user, visit the EnterpriseDB website at:

Cloud Database uses public-key authentication; for detailed setup information about public-key authentication, please see the Postgres Plus Cloud Database Getting Started Guide, or the tutorial, Connecting to Cloud Database, available through the link on your Cloud Database dashboard.

By default, only port 9999 on the master server node of a Cloud Database cluster is open for client connections.

Before connecting with ssh or scp, you must first modify the Cloud Database security group, opening port 22. When you are no longer using ssh or scp, you can safely disable this security rule.

For detailed information about modifying a security group, please see Section 12.3, Securing a Cloud Database Cluster in the Cloud Database Getting Started Guide, available through the Cloud Database Dashboard tab.

In this tutorial, the term Postgres refers to a Postgres Plus Advanced Server or PostgreSQL database. Cloud Database currently supports:

      ● PostgreSQL 9.1 32 bit

      ● PostgreSQL 9.1 64 bit

      ● Postgres Plus Advanced Server 9.0 64 bit

      ● Postgres Plus Advanced Server 9.0 32 bit

      ● Postgres Plus Advanced Server 9.1.2.2 32 bit

      ● Postgres Plus Advanced Server 9.1.2.2 64 bit

Step One - Set the Search Path on the Source System

Open a terminal window on the system that contains your Postgres source database, and navigate to the Postgres installation directory. By default, the Advanced Server installation directory is:

          /opt/PostgresPlus/version_number

For PostgreSQL, the directory is:

          /opt/PostgreSQL/version_number

Add the pg_dump utility to the search path for the current session. For Advanced Server, invoke the pgplus_env.sh script:

          source pgplus_env.sh

For PostgreSQL, invoke the pg_env.sh script:

          source pg_env.sh

Step Two - Create the pg_dump Archive

Use the pg_dump utility to create an archive that contains the commands required to recreate a database. When invoking pg_dump, include the -Ft flag to instruct pg_dump to format the output as a tar file, and the -U flag to specify the name of the database superuser (see Figure 1.1):

      pg_dump -Ft -U db_superuser db_name > archive_name.tar

Figure 1.1 - Creating the pg_dump archive.

Where:

      db_superuser is the name of a Postgres database superuser.

      db_name is the name of the database that you wish to move to Cloud Database.

      archive_name.tar is the complete path and name of the archive. Please note that you must have permission to write a file to the location specified.

If prompted, enter the password associated with the database superuser.

Step Three - Move the Archive to Cloud Database

Use the scp command to copy the archive to the master server in the Cloud Database cluster; include the -i option to specify the location of your ssh key (see Figure 1.2):

scp -i /path/ssh_key.pem file_name root@host_name:

Figure 1.2 - Moving the archive to Cloud Database.

Where:

      path specifies the location of your Cloud Database ssh certificate on the system from which you are connecting.

      ssh_key.pem specifies the name of the Cloud Database ssh private key file.

      file_name specifies the archive name.

      host_name specifies the host name of the master node of the Cloud Database cluster; the host name is located on the Details panel of the Clusters tab in the Cloud Database console (see Figure 1.3).

      The colon (:) at the end of this command specifies that the file will be copied to the root directory on the cluster's primary node; please note that you can specify a file destination by adding a destination path after the colon.

Figure 1.3 - The Details panel of the Clusters tab.

For information about obtaining and using your ssh key, please see the tutorial, Connecting to Cloud Database, available through the Cloud Database Dashboard.

Step Four - Connect to Cloud Database with ssh

Use ssh to connect to your Cloud Database cluster master node. You should always connect as root, specifying the location of the ssh key (on your local host) in the command (see Figure 1.3):

      ssh -i/path/ssh_key.pem root@host_name

Where:

      path specifies the location of your Cloud Database ssh certificate on the system from which you are connecting.

      ssh_key.pem specifies the name of the Cloud Database ssh private key file.

      host_name specifies the host name of the master node of the Cloud Database cluster; the host name is located on the Details panel of the Clusters tab in the Cloud Database console.

Figure 1.3 - Connecting to Cloud Database with ssh.

Step Five - Set the Search Path on Cloud Database

After connecting, you should add the pg_restore command to the search path of the session. Navigate to /opt/PostgresPlus/CloudDB, and invoke (for Advanced Server), the pgplus_env.sh script:

          source pgplus_env.sh

For PostgreSQL, invoke the pg_env.sh script:

          source pg_env.sh

Step Six - Invoke pg_restore on the master server in the Cloud Database cluster

Before invoking the pg_restore utility, you must create the target database in the master server; you can use the createdb client utility at the command line to create the target:

      createdb database_name

Where:

      database_name specifies the name of the database on Cloud Database.

Then, invoke the pg_restore utility:

      pg_restore -Ft /path/archive_name.tar -d target_db_name

Where:

      path is the pathname to the archive on the Cloud.

      archive_name.tar is the name of the archived database.

      target_db_name is the name of the target database on the Cloud.

Include the -Ft flag to specify that the file is an archive; use the -d target_db_name flag to specify the name of the database on the Cloud (see Figure 1.4).

Figure 1.4 - Restoring the database on the Cloud.

Step Seven - Confirm that the Move was Successful

After performing the restore, you can use the psql client (or Advanced Server's edb-psql) to connect to the Cloud Database and confirm that the database has been transferred (see Figure 1.5):

      psql target_db_name

Use the \d command to view a list of database objects in the current database:

Figure 1.5 - Confirming that the move was successful.

To exit the psql client, enter \q; to exit the ssh session, type exit and Return.

For more information about using the psql client, please see the tutorial, Connecting to a Cloud Database. You can access the tutorial through the Dashboard tab of the Cloud Database console.

For more information about using PostgreSQL utilities to move an existing database into Cloud Database, please see the documentation at: