How Do I Set Up PostGIS on an AWS EC2 Instance?

Introduction

This guide provides a comprehensive walkthrough for setting up PostGIS on an AWS EC2 instance using Pulumi. PostGIS is a powerful spatial database extension for PostgreSQL, allowing you to store and query geographic objects. By following the steps outlined in this guide, you will successfully provision an EC2 instance with PostGIS installed and configured, ready to handle spatial data operations.

Step-by-Step Process

Step 1: Provision an EC2 Instance

1. Create a Security Group:

   • Define a security group to allow SSH and PostgreSQL access.
   • Configure ingress rules for SSH (port 22) and PostgreSQL (port 5432).

2. Select an Amazon Machine Image (AMI):

   • Retrieve the latest Amazon Linux 2 AMI using filters to ensure compatibility.

3. Launch the EC2 Instance:

   • Use the selected AMI to create a new EC2 instance of type t2.micro.
   • Attach the security group to the instance for proper access control.

Step 2: Install PostgreSQL and PostGIS

1. User Data Script:

   • Utilize a user data script to automate the installation process.
   • Install PostgreSQL and initialize the database.
   • Start and enable the PostgreSQL service for automatic startup.
   • Install PostGIS and its utilities.

2. Configure the Database:

   • Create a new PostgreSQL database named gisdb.
   • Enable the PostGIS extension on the database to support spatial data.

Key Points

1. Security Group: A security group is created to allow SSH and PostgreSQL access.
2. EC2 Instance: An EC2 instance is provisioned using the latest Amazon Linux 2 AMI.
3. User Data Script: A user data script is provided to install PostgreSQL, initialize it, start the service, install PostGIS, and set up a PostGIS-enabled database.

import * as pulumi from "@pulumi/pulumi";
import * as aws from "@pulumi/aws";

// Create a new security group to allow SSH and PostgreSQL access
const securityGroup = new aws.ec2.SecurityGroup("postgis-sg", {
    description: "Allow SSH and PostgreSQL access",
    ingress: [
        { protocol: "tcp", fromPort: 22, toPort: 22, cidrBlocks: ["0.0.0.0/0"] }, // SSH
        { protocol: "tcp", fromPort: 5432, toPort: 5432, cidrBlocks: ["0.0.0.0/0"] }, // PostgreSQL
    ],
    egress: [
        { protocol: "tcp", fromPort: 0, toPort: 0, cidrBlocks: ["0.0.0.0/0"] },
    ],
});

// Get the latest Amazon Linux 2 AMI
const ami = aws.ec2.getAmi({
    filters: [
        { name: "name", values: ["amzn2-ami-hvm-*-x86_64-gp2"] },
    ],
    owners: ["137112412989"], // Amazon
    mostRecent: true,
});

// Create an EC2 instance
const instance = new aws.ec2.Instance("postgis-instance", {
    instanceType: "t2.micro",
    ami: ami.then(ami => ami.id),
    securityGroups: [securityGroup.name],
    userData: `#!/bin/bash
    # Install PostgreSQL
    amazon-linux-extras install postgresql10 -y
    yum install postgresql-server -y
    # Initialize PostgreSQL
    postgresql-setup initdb
    # Start PostgreSQL service
    systemctl start postgresql
    systemctl enable postgresql
    # Install PostGIS
    yum install postgis postgis-utils -y
    # Create a PostgreSQL database and enable PostGIS
    sudo -i -u postgres psql -c "CREATE DATABASE gisdb;"
    sudo -i -u postgres psql -d gisdb -c "CREATE EXTENSION postgis;"
    `,
    tags: {
        Name: "PostGIS-Instance",
    },
});

// Export the public IP of the instance
export const publicIp = instance.publicIp;
export const publicDns = instance.publicDns;

Summary

In this guide, we demonstrated how to set up an AWS EC2 instance with PostGIS installed. We covered the creation of a security group to manage access, the provisioning of an EC2 instance using the latest Amazon Linux 2 AMI, and the use of a user data script to automate the installation and configuration of PostgreSQL and PostGIS. The public IP and DNS of the instance are exported for convenient access, completing the setup process.