Connecting to a Cloud Database Cluster

Connecting to a Cloud Database Cluster

Version 1.4.2

August 21, 2015

by EnterpriseDB Corporation
Copyright © 2012 - 2015 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 client program that resides on Cloud Database from 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:

http://www.enterprisedb.com/cloud-database

This tutorial uses the term Postgres to mean a Postgres Plus Advanced Server or PostgreSQL database.

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.

Connection Pre-Requisites

Choosing 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 13.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 on the Cloud Database cluster, 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.

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 client.

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. If you are using Advanced Server, open a terminal window, and enter:

    source /opt/PostgresPlus/version_number/pgplus_env.sh

If you are using 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:

http://www.enterprisedb.com/docs/en/9.3/pg/sql-commands.html

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

http://www.enterprisedb.com/docs/en/9.3/pg/app-psql.html

Using a Client that Resides on Cloud Database

To use a copy of the psql client that resides on the Cloud Database host, first connect to the cluster using ssh:

    ssh -i/path/ssh_key root@host_name

After connecting to the host, invoke the psql client:

/path_to_psql_client/psql -d db_name -U user_name

Include the -d keyword to specify the database to which you wish to connect, and the -U keyword to specify the name of an Advanced Server user.

Where:

    path_to_psql_client specifies the complete path to the psql client.

    psql specifies the name of the psql executable (edb-psql on an Advanced Server host, or psql on a PostgreSQL host).

    db_name specifies the name of a Postgres database.

    user_name specifies the name of a Postgres user. The privileges associated with the specified user will determine the privileges enforced by the server.

For example, the following command uses the psql client that resides on an Advanced Server host to connect to a database named edb, using the privileges associated with a user named enterprisedb:

/opt/PostgresPlus/9.2AS/bin/edb-psql -d edb -U enterprisedb

Figure 1.7 - A psql session on the Cloud Database host.