Install PostGIS for AlloyDB Omni

Select a documentation version: 18.3.0

• Current (18.3.0)
• 18.3.0

This page describes how to manually add the PostGIS extension to an existing AlloyDB Omni installation. The PostGIS extension enables storing, indexing, and querying geospatial data.

Before you begin

Install AlloyDB Omni using the RPM orchestrator.

Add PostGIS to your AlloyDB Omni installation

To add the PostGIS extension to your AlloyDB Omni cluster, follow these steps manually on all database nodes including read pool nodes in your cluster.

1. Request access to the AlloyDB Omni using RPM.

2. Initialize Red Hat Subscription Manager on the VM where you installed the AlloyDB Omni server:

   sudo dnf install -y subscription-manager
   sudo subscription-manager register --username "RHSM_USER" --password "RHSM_PASS" --auto-attach --force
   

   Replace the following:

   • RHSM_USER: the Red Hat Subscription Manager user.
   • RHSM_PASS: the Red Hat Subscription Manager password.

3. Enable CodeReady Builder repository:

   sudo subscription-manager repos --enable="codeready-builder-for-rhel-9-$ARCH-rpms"
   

   Replace ARCH with the RHEL architecture you want to use, for example, x86_64.

4. Add the PostgreSQL Yum Repository:

   sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
   

5. Install PostgreSQL server and development packages:

   sudo dnf install -y "postgresql18-server"
   

   Replace OMNI_VERSION with the AlloyDB Omni version number, which is 18.

6. Install the PostGIS extension:

   sudo dnf install -y postgis35_18
   

7. Create required symlinks so the extensions are in the expected paths:

   set -e
   PGVER="18"
   
   sudo mkdir -p "/usr/lib/postgresql/${PGVER}/share/extension"
   
   for file in /usr/pgsql-${PGVER}/share/extension/*; do
       target="/usr/lib/postgresql/${PGVER}/share/extension/$(basename "$file")"
       if [[ ! -e "$target" ]]; then
         sudo ln -s "$file" "$target" || (echo "Failed to link \"$file\" to \"$target\", exiting." && exit 1)
         echo "Created extension link for $(basename "$file")"
       else
         echo "Target $target already exists."
       fi
   done
   
   sudo mkdir -p "/usr/lib/postgresql/${PGVER}/lib"
   
   for file in /usr/pgsql-${PGVER}/lib/*; do
       target="/usr/lib/postgresql/${PGVER}/lib/$(basename "$file")"
       if [[ ! -e "$target" ]]; then
         sudo ln -s "$file" "$target" || (echo "Failed to link \"$file\" to \"$target\", exiting." && exit 1)
         echo "Created lib link for $(basename "$file")"
       else
         echo "Target $target already exists."
       fi
   done
   

8. Connect to your database and run the CREATE EXTENSION command to enable PostGIS:

   For instructions on connecting to your instance, see Connect to AlloyDB Omni.

   Note: You only need to enable the extension once on the primary database instance.

   CREATE EXTENSION IF NOT EXISTS POSTGIS;
   

9. Confirm that PostGIS is installed and enabled:

   SELECT postgis_full_version();
   

   The output is similar to the following:

   postgres=# SELECT postgis_full_version();
   postgis_full_version
   --------------------------------------------------------------------------------------------------------------------------------
   POSTGIS="3.3.2 4975da8" [EXTENSION] PGSQL="150" GEOS="3.11.1-CAPI-1.18.1" PROJ="9.1.1" LIBXML="2.9.14" LIBJSON="0.16" LIBPROTOBUF="1.4.1" WAGYU="0.5.0 (Internal)"
   (1 row)