How to Move an Existing Database into a Cloud Database Cluster

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

August 21, 2015

Version 1.4.2

by EnterpriseDB Corporation

Copyright © 2012 - 2015 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.2 into a Cloud Database cluster that is running Advanced Server version 9.2). 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 13.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.

Step One - Set the Search Path on the Source Host

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 (see Figure 1.2); include the -i option to specify the location of your ssh key:

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

Figure 1.2 - Copying 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 in the DNSNAME column on the Details panel of the Clusters tab in the Cloud Database console (see Figure 1.3).

    directory specifies the name of the directory to which the archive will be copied.

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 the Master Node of the Cluster Host with ssh

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

    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.4 - Connecting to Cloud Database with ssh.

Step Five - Assume the Identity of the Database Superuser and Source the Environment Variables on the Target Host

After connecting, assume the identity of the database superuser. By default, on an Advanced Server instance, the superuser is named enteprisedb; on PostgreSQL, the database superuser is named postgres:

      su - user_name

Where:

    user_name specifies the name of a Postgres database superuser.

Navigate to /opt/PostgresPlus/CloudDB and invoke (for Advanced Server) the pgplus_env.sh script:

      source pgplus_env.sh

If your database resides in a PostgreSQL cluster, invoke the pg_env.sh script:

      source pg_env.sh

Figure 1.5 - Sourcing the environment variables.

Step Six - Create the Target database and Restore the Source Database

Before invoking the pg_restore utility, you must create the target database in the master server; you can use the createdb server 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.6 - 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.7 - 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:

http://www.enterprisedb.com/docs/en/9.3/pg/backup-dump.html