How to Install PostGIS in a Cloud Database Cluster

How to Install PostGIS in a Cloud Database Cluster

August 21, 2015

How to Install PostGIS in a Cloud Database Cluster
Version 1.4.2

by EnterpriseDB Corporation

Copyright © 2012 - 2015 EnterpriseDB Corporation. All rights reserved.

Introduction

A Geographic Information System (GIS) object is a defined unit of geographical data that represents a specific spatial object. PostGIS is a Postgres extension that stores and manipulates GIS objects; you can use PostGIS in a Cloud Database PostgreSQL or Advanced Server cluster.

PostGIS supports storage of a wide range of GIS object types, including:

    POINT

    LINE

    POLYGON

    MULTIPOINT

    MULTILINE

    MULTIPOLYGON

    GEOMETRYCOLLECTION

In addition to adding GIS storage capabilities to Postgres, PostGIS also adds support for GiST-based R-tree spatial indexes; these indexes allow you to quickly find GIS data based on criteria specified in terms of spatial relationship. An example of such a query would be to SELECT all Chinese restaurants within X miles of a given geographical point.

PostGIS also includes numerous functions you can use to analyze GIS objects; the functions perform tasks such as:

    ● Calculate the surface area of a given object.

    ● Find the distance between two objects.

    ● Locate the Geometric center of a given object.

The support that PostGIS provides for GIS objects to Advanced Server is comparable to support added by Oracle Spatial, DB2 Spatial, and SQL Server Spatial to their associated databases.

This EnterpriseDB tutorial will lead you step-by-step through the process of installing PostGIS in a Cloud Database cluster. If you need more information about obtaining Cloud Database, visit the EnterpriseDB website at:

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

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.

Installing PostGIS into a Cloud Database Cluster

If you know prior to creating a (multi-node) Cloud Database cluster that you will be using PostGIS, you can simplify the installation process by creating a single node cluster, installing PostGIS on that node, and then adding replicas until the cluster reaches the desired size. This sequence allows you to install PostGIS only once; Cloud Database will propagate the extension to replica servers during the scale up process. The new extension will persist during scale up, restore and failover operations.

If you are adding PostGIS to an existing cluster, you must invoke the installer on each node in the replication cluster; you should begin by installing the extension on the master server node.

The following tutorial assumes:

    ● You have already obtained Cloud Database.

    ● You have registered as a Cloud Database user.

    ● You have created a single-node cluster into which you wish to install PostGIS.

    ● You have modified the cluster's security group, allowing connections from your client workstation on port 22.

Step One - ssh to the Cloud Database host

You can use an ssh connection to download the PostGIS installer directly to a node of a Cloud Database cluster. Open a terminal window, and connect to the Cloud Database host, connecting to the server as root, and including the -i flag to specify the location of the ssh key file in the command:

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

Where:

    path specifies the location of your Cloud Database ssh private key file (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 or IP address 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.1).

Figure 1.1 - The Details panel of the Clusters tab.

Step Two - Use wget to download the PostGIS installer

The PostGIS installer is available from the EnterpriseDB website at:

http://www.enterprisedb.com/downloads/add-on-components-bundles

Select the PostGIS installer that is compatible with your Cloud Database server. Right click on the Download link, and select Copy Link from the context menu to copy the URL.

http://www.enterprisedb.com/add-postgis-92-201-linux64

Return to your ssh session, and enter:

    wget "installer_URL"

Where installer_URL is the link pasted from the EnterpriseDB download site.

wget will download the PostGIS installer to your current directory (see Figure 1.2).

Figure 1.2 - Download the PostGIS installer file.

Step Three - Change the permissions on the PostGIS installation file.

Use the following command to change the permissions of the PostGIS installer file:

    chmod u+x installer_name

Where installer_name is the name of the PostGIS installer file.

Step Four - Invoke the PostGIS installer.

Invoke the PostGIS installer:

    ./installer_name

Where installer_name is the name of the PostGIS installer file (see Figure 1.3).

Figure 1.3 - The PostGIS installer.

The PostGIS installer will prompt you to select a language for the installation; select a language or press Return to accept the default (English).

Figure 1.4 - Accept the default installation directory.

When prompted, press Return to accept the default installation directory for the Postgres server (as shown in Figure 1.4).

Figure 1.5 - Provide a user name and password for Postgres.

Enter the name of the database superuser, and the password associated with that account (see Figure 1.5). The database superuser is the name specified in the Master User field when creating the Cloud Database cluster; the password is the name specified in the Master Password field.

Figure 1.6 - Enter the listener port address.

If you've modified the listener port, enter the new port number, or accept the default and press return; when the installer notifies you that Setup is ready to begin the installation process, enter a Y to continue (as shown in Figure 1.6).

Figure 1.7 - A progress bar marks the installation process.

A progress bar marks the progress of the PostGIS installation, and will notify you when the installation is complete (as shown in Figure 1.7).

You can confirm that the PostGIS installation completed successfully by connecting to the server, and reviewing the contents of the template_postgis database (See Figure 1.8).

Figure 1.8 - The PEM client tree control, displaying the PostGIS functions.

Step Five - Add Replica Nodes to the Cloud Database Cluster.

Since PostGIS is installed on the primary node, any replica nodes added during the process of scaling up will automatically include PostGIS functions. For information about adding replica nodes, see Section 9, Manual Scaling, in the Postgres Plus Cloud Database Getting Started Guide.

The EnterpriseDB Quick Tutorial, How to use PostGIS with Postgres Plus Advanced Server provides an introduction to PostGIS functionality. You can find the tutorial online at:

http://www.enterprisedb.com/resources-community/tutorials-quickstarts/all-platforms

The official PostGIS documentation is available through the PostGIS project website; the address is:

http://postgis.refractions.net/