Connecting to a Cloud Database Cluster

April 15, 2012

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

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

Introduction

In the following EnterpriseDB tutorial, we will demonstrate:

    ● Downloading and using your private key.

    ● Connecting to a Cloud Database cluster with Postgres Enterprise Manager.

    ● Connecting to a Cloud Database cluster with edb-psql or psql.

    ● Connecting to the Cloud Database host operating system with ssh.

    ● Copying a file to the Cloud Database host with scp.

    ● Using a graphical program that resides on Cloud Database on a local workstation.

This tutorial assumes:

    ● You have already obtained Cloud Database.

    ● You are using a Linux workstation.

    ● You have modified the security group for your cluster, allowing client connections on the appropriate ports (as described below).

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

This tutorial uses the term Postgres to mean 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

This tutorial includes examples and commands that will execute on a Linux system. Windows-friendly versions of ssh and xauth are available on the web; if you are using a Windows workstation, you should follow the basic steps outlined in the following tutorial, substituting platform-appropriate commands for the commands shown.

Selecting a Port for a Client Connection

By default, only port 9999 on the master server node of a Cloud Database cluster is open for client connections. Port 9999 is the load balancing port; connect to this port when executing queries or modifying data.

If you are modifying a database object or invoking administrative functions, you should connect to the Postgres server's listener port, identified in the DBPORT column, on the Details panel of the Clusters tab. By default, this is port 5444 on an Advanced Server cluster, and port 5432 on a PostgreSQL cluster. Before connecting to the listener port, you must modify the security group to allow connections.

Before connecting with ssh or scp, you must first modify the cluster's security group, opening port 22 for connections from your workstation. 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.

Downloading your Private Key

Cloud Database uses public-key authentication to authenticate a client connecting to the operating system of any hosted node. You should download your private key immediately after registering with Cloud Database. To download your private key, navigate to the User tab of the Cloud Database console, and click the Download SSH Key button.

After downloading your private ssh key, open a terminal window, navigate to the location of the ssh key file, and modify the key permissions with the command:

    $ chmod 0600 key_file_name

After setting the permissions for the key file, you can use the key to connect to a host operating system on any node of a Cloud Database cluster.

Default User Identities

Operating System User ID - Cloud Database creates an operating system user named root; when connecting to the operating system as root, you should provide the private key that was created when you registered with Cloud Database.

Database Superuser ID - When creating a Cloud Database cluster, you can specify the name of a database superuser in the Master User field of the Create a new Server Cluster dialog; you can also provide the password associated with that role in the Master Password field.

    ● By default, the PostgreSQL Master User is named postgres, and password is postgres.

    ● By default, the Advanced Server Master User is named enterprisedb, and the password is postgres.

Using ssh to Connect to a Server in a Cloud Database Cluster

You can use ssh to connect to the operating system of any node hosted by Cloud Database (see Figure 1.1). Unless you have created other operating system user accounts, you should ssh to the server node as root, specifying the location of the ssh key (on your local host) in the command:

    ssh -i/path/ssh_key root@host_name

Figure 1.1 - Connecting to a node on Cloud Database.

Where:

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

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

    host_name specifies the host name of the node to which you wish to connect. You can find the host name in the DNSNAME column, on the Details panel of the Clusters tab in the Cloud Database console (see Figure 1.2).

Figure 1.2 - The Details panel of the Clusters tab.

After connecting via ssh, you can:

    ● Shutdown, start, or restart the Postgres server.

    ● Download and install Postgres extensions.

    ● Modify configuration files that require a server restart. Please note that you can modify postgresql.conf and pgpool.conf parameters that do not require a server restart on the Configurations panel of the Clusters tab of the Cloud Database console.

    ● Use PostgreSQL Client Applications.

    ● Invoke PostgreSQL Server Applications.

Please Note: Postgres Server applications must be invoked by the Postgres cluster owner (identified when creating a Cloud Database cluster as the Master User). If you are using a PostgreSQL server, this user will be named postgres; if you are using Advanced Server, the user will be named enterprisedb. To change your identity after connecting via ssh, use the su command:

    # su enterprisedb

Copying files to Cloud Database

You can use the scp command to copy a file to a Cloud Database cluster; include the -i option to specify the location of your ssh key (see Figure 1.3):

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

Figure 1.3 - Moving a file to Cloud Database.

Where:

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

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

    file_name specifies the name of the file you are copying to Cloud Database.

    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.

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.

Please note: If you know prior to creating a cluster that you will be adding a server extension (that must be recreated on each replica node), you can simplify the process by following the procedure outlined below.

    1) Create a single instance cluster (a cluster with only a master node).

    2) Install the extension (such as PostGIS).

    3) Add replicas to scale up to the desired initial cluster size.

This sequence allows you to install the extension only once; Cloud Database will propagate the extension to each replica server during the scale up process. In addition, the added extension will persist during scale up, restore and failover operations.

Using Postgres Enterprise Manager to Connect to a Cloud Database Cluster

The Postgres Enterprise Manager (PEM) graphical client is installed by default during an installation of Postgres Plus Advanced Server version 9.1 (or greater). PEM is also available to Postgres and PostgreSQL users through Postgres Plus Solution Pack.

You can use the PEM client to:

    ● Create, modify or drop roles (individual or group).

    ● Create, modify or drop database objects (schemas, tables, views, etc.).

    ● Manage privileges associated with roles or objects.

To open the PEM client, navigate through the Applications menu to the Postgres Plus Advanced Server 9.1 menu, and select Postgres Enterprise Manager. The client window opens as shown in Figure 1.3.

Figure 1.3 - The Postgres Enterprise Manager client window.

The Object browser pane displays a tree control that expands to show the database objects on each defined server; right click a server name to provide a password and connect to the selected server.

To add a connection to a server hosted on Cloud Database, select the Add Server option from the File menu. The New Server Registration dialog opens as shown in Figure 1.4.

Figure 1.4 - The New Server Registration dialog.

Provide information about the connection in the New Server Registration dialog:

    ● Specify the name of the Cloud Database cluster in the Name field.

    ● Provide the IP address or host name of the master node of the cluster in the Host field. You can find the IP address in the DNSNAME column on the Details panel for the cluster on the Cloud Database console.

    ● Specify the Port on which the server is listening.

    If you are modifying a database or invoking administrative functions, you should connect to the Postgres server's listener port, identified in the DBPORT column, on the Details panel of the Clusters tab. By default, this is port 5444 on an Advanced Server cluster, and port 5432 on a PostgreSQL cluster. Before connecting to the listener port, you must modify the security group to allow connections.

    ● Select a maintenance database using the drop-down listbox in the Maintenance DB field. Select edb if you are connecting to an Advanced Server database, and postgres if you are connecting to a PostgreSQL database.

    ● Specify the role name that the PEM client should use when connecting, in the Username field.

    ● Provide the password associated with that role, in the Password field.

Click OK to connect to Cloud Database; once connected, the server will appear in the tree control in the PEM Object browser (shown in Figure 1.5).

Figure 1.5 - The PEM client window, showing local and Cloud Database servers.

Now, you are ready to use all of the point-and-click functionality of the Postgres Enterprise Manager client to create and manage database objects that reside in the cloud. For more information about using PEM, download the Postgres Enterprise Manager Getting Started Guide from EnterpriseDB at:

Postgres Enterprise Manager also offers context-driven help; click the Help button provided on each dialog to access online documentation about the current dialog.

Connecting to Cloud Database with psql or edb-psql

psql is the PostgreSQL command-line client; edb-psql is the Advanced Server command-line client. After connecting to a Postgres server hosted on Cloud Database via psql or edb-psql, you can invoke SQL commands or use psql meta-commands to:

    ● Create user roles and manage privileges

    ● Create and manage database objects (tables, indexes, views, etc.)

    ● Manage data

    ● Review object and role attributes

    ● Read and execute file contents (invoke a script)

edb-psql offers complete compatibility with psql, while adding the ability to process Oracle-compatible syntax for anonymous blocks, CREATE FUNCTION statements, CREATE PROCEDURE statements and CREATE PACKAGE statements on an Advanced Server database. You can also use edb-psql to configure and invoke Advanced Server utilities, or use Oracle-compatible features not found in PostgreSQL.

To open an edb-psql console, navigate through the Applications (or Start) menu to the Postgres Plus Advanced Server menu; then, open the Run SQL Command Line menu, and select EDB-PSQL.

To open a psql console, navigate through the Applications (or Start) menu to the PostgreSQL menu, and select SQL Shell (psql). When prompted, provide connection information for the server to which you are connecting:

    ● The IP address or DNS name of the Cloud Database server. The address is displayed in the DNSNAME column on the Details panel of the Clusters tab.

    ● The name of the database to which you wish to connect. By default, an Advanced Server cluster is created with a database named edb. A PostgreSQL cluster is created with a database named Postgres.

    ● The port on which the server is listening. By default, Advanced Server monitors port 5444, while PostgreSQL monitors port 5432. Before connecting, you must modify the cluster's security group, opening the listener port for connections.

    ● The role you wish to use when connecting to the server. The name of the database superuser is specified in the Master User field when defining a Cloud Database server cluster. By default, the Advanced Server database superuser is enterprisedb. The default superuser of a PostgreSQL database is postgres.

    ● The password associated with that role. The database superuser's password is specified in the Master Password field when defining a Cloud Database server cluster.

Figure 1.6 - The EDB-PSQL command line utility.

After connecting, the edb-psql (or psql) prompt will display the name of the database to which you are connected (as shown in Figure 1.6).

You can also connect to Cloud Database with the edb-psql or psql client from the command prompt (see Figure 1.7). Before starting the client from the command line, you should add the Postgres utilities to your search path. For Advanced Server, open a terminal window, and enter:

    source /opt/PostgresPlus/version_number/pgplus_env.sh

For PostgreSQL, enter:

    source /opt/PostgreSQL/version_number/pg_env.sh

Now, you can start the edb-psql (or psql) client:

    edb-psql -h host_name -U user_name

Figure 1.7 - A psql session, started at the command line.

Where:

    host_name specifies the IP address or DNS name of the server.

    user_name specifies the name of the Postgres role that is connecting to the server.

    If prompted, enter the password associated with the specified role.

You can now use SQL commands to create, modify, delete, and query roles and database objects. Please note that SQL commands are always terminated with a semi-colon (;).

psql meta-commands begin with a backslash (\), and unlike SQL commands, are not terminated by a semi-colon (;).

    Enter \? on the EDB-PSQL command line to display help topics for psql meta-commands.

    Enter \h sql_command on the EDB-PSQL command line to display help for the SQL command specified in the sql_command parameter.

To exit the psql client, enter \q.

For more information about the Postgres SQL commands, please see the Postgres documentation at:

For information about using psql and the psql meta-commands, please see the Postgres documentation at:

Using a Graphical Program that Resides on Cloud Database

xauth is an X Windows authentication package. xauth allows you to connect graphical clients, installers and text editors running on a Cloud Database cluster to an X11 server running on your local Linux workstation.

To add xauth to a node within your Cloud Database cluster, ssh to the node, and enter:

    # yum install -y xauth

Include the -y flag to indicate a yes response to any prompts that yum might issue during the installation. As the installation progresses, yum displays information about the programs downloaded and installed. When the installation completes, you are ready to invoke a graphical program that resides on the Cloud Database cluster.

Exit the ssh session, and then reconnect, specifying the -X flag to enable X Windows functionality:

    ssh -X -i/path/ssh_key root@host_name

Once connected, invoke the program on the Cloud Database host; the window will open on your local workstation. For example, to open a Postgres Studio session (available on Advanced Server hosts only), enter:

    /opt/PostgresPlus/9.0AS/bin/pgadmin3

When Postgres Studio opens, right click on the server name to provide a password and connect to the server (as shown in Figure 1.8)

Figure 1.8 - A Postgres Studio session, on the Cloud Database host.