How to Script a PostGIS Setup on Amazon RDS Postgres

PostGIS is a powerful extension to PostgreSQL that turns it into a spatial database, perfect for handling geographic objects and location queries. Amazon RDS offers PostgreSQL as a service, and fortunately, PostGIS support is included. However, setting up PostGIS on Amazon RDS PostgreSQL may require a number of manual steps that can be a bit daunting.

By Saturn Cloud | Tuesday, August 08, 2023 | Miscellaneous

How to Script a PostGIS Setup on Amazon RDS Postgres

This guide will show you how to automate the PostGIS setup on Amazon RDS Postgres using scripting. This can save you time and ensure consistency in your setups.

Prerequisites

Before we start, you need to have:

An AWS account
AWS CLI installed and configured
PostgreSQL client (like psql) installed

Step 1: Launch an Amazon RDS PostgreSQL Instance

The first step is to create a PostgreSQL instance on Amazon RDS. We can script this with the AWS CLI.

aws rds create-db-instance \
  --db-instance-identifier my-postgis-instance \
  --db-instance-class db.t2.micro \
  --engine postgres \
  --allocated-storage 20 \
  --master-username masteruser \
  --master-user-password masteruserpassword \
  --vpc-security-group-ids sg-0abcd1234efgh5678 \
  --availability-zone us-west-2a \
  --no-multi-az \
  --engine-version 13.3 \
  --publicly-accessible

Step 2: Connect to the Instance

Once the instance is ready, connect to it using psql:

psql -h my-postgis-instance.c123456789.us-west-2.rds.amazonaws.com -U masteruser postgres

Step 3: Enable PostGIS Extension

After connecting, enable the PostGIS extension. Unfortunately, we can’t automate this step with AWS CLI, but we can use a SQL script. Here’s a simple script to enable the PostGIS extension:

CREATE EXTENSION postgis;

Save this script as enable-postgis.sql and use psql to run it:

psql -h my-postgis-instance.c123456789.us-west-2.rds.amazonaws.com -U masteruser postgres -f enable-postgis.sql

Step 4: Verify the Installation

To ensure that PostGIS is set up correctly, run the following command:

psql -h my-postgis-instance.c123456789.us-west-2.rds.amazonaws.com -U masteruser postgres -c "SELECT PostGIS_version();"

If PostGIS is correctly set up, this will output the version of PostGIS installed.

Conclusion

In this guide, we showed you how to script a PostGIS setup on Amazon RDS Postgres. This process saves time and reduces errors. By automating it, you can ensure that your PostGIS installations are consistent and reliable.

We hope this guide has been helpful. If you have any questions or need further clarification, feel free to leave a comment below.

keywords: Amazon RDS, PostGIS, PostgreSQL, scripting, automation, setup

Remember, while this guide simplifies the process, it’s crucial to understand each step and adjust the parameters according to your specific needs. Always follow best security practices, such as not exposing your database to the public internet, and using secure passwords.

About Saturn Cloud

Saturn Cloud is your all-in-one solution for data science & ML development, deployment, and data pipelines in the cloud. Spin up a notebook with 4TB of RAM, add a GPU, connect to a distributed cluster of workers, and more. Join today and get 150 hours of free compute per month.

Try Saturn Cloud Now