Skip to content

Use PostGIS extension with Percona Distribution for PostgreSQL

PostGIS is a PostgreSQL extension that adds GIS capabilities to this database.

Starting from the Operator version 2.3.0 it became possible to deploy and manage PostGIS-enabled PostgreSQL.

Due to the large size and domain specifics of this extension, Percona provides separate PostgreSQL Distribution images with it.

Deploy the Operator with PostGIS-enabled database cluster

Following steps will allow you to deploy PostgreSQL cluster with these images.

  1. Clone the percona-postgresql-operator repository:

    $ git clone -b v2.3.1 https://github.com/percona/percona-postgresql-operator
    $ cd percona-postgresql-operator
    

    Note

    It is crucial to specify the right branch with -b option while cloning the code on this step. Please be careful.

  2. The Custom Resource Definition for Percona Distribution for PostgreSQL should be created from the deploy/crd.yaml file. Custom Resource Definition extends the standard set of resources which Kubernetes “knows” about with the new items (in our case ones which are the core of the Operator). Apply it as follows:

    $ kubectl apply --server-side -f deploy/crd.yaml
    
  3. Create the Kubernetes namespace for your cluster if needed (for example, let’s name it postgres-operator):

    $ kubectl create namespace postgres-operator
    
  4. The role-based access control (RBAC) for Percona Distribution for PostgreSQL is configured with the deploy/rbac.yaml file. Role-based access is based on defined roles and the available actions which correspond to each role. The role and actions are defined for Kubernetes resources in the yaml file. Further details about users and roles can be found in Kubernetes documentation .

    $ kubectl apply -f deploy/rbac.yaml -n postgres-operator
    

    Note

    Setting RBAC requires your user to have cluster-admin role privileges. For example, those using Google Kubernetes Engine can grant user needed privileges with the following command:

    $ kubectl create clusterrolebinding cluster-admin-binding --clusterrole=cluster-admin --user=$(gcloud config get-value core/account)
    
  5. Start the Operator within Kubernetes:

    $ kubectl apply -f deploy/operator.yaml -n postgres-operator
    
  6. After the Operator is started, modify the deploy/cr.yaml configuration file with PostGIS-enabled image - use percona/percona-postgresql-operator:2.3.1-ppg16-postgres-gis instead of percona/percona-postgresql-operator:2.3.1-ppg16-postgres

    apiVersion: pgv2.percona.com/v2
    kind: PerconaPGCluster
    metadata:
      name: cluster1
    spec:
      ...
      image: percona/percona-postgresql-operator:2.3.1-ppg16-postgres-gis
      ...
    

    When done, Percona Distribution for PostgreSQL cluster can be created at any time with the following command:

    $ kubectl apply -f deploy/cr.yaml -n postgres-operator
    

    The creation process may take some time. When the process is over your cluster will obtain the ready status. You can check it with the following command:

    $ kubectl get pg -n postgres-operator
    
    Expected output
    NAME       ENDPOINT                         STATUS   POSTGRES   PGBOUNCER   AGE
    cluster1   cluster1-pgbouncer.default.svc   ready    3          3           30m
    

Check PostGIS extension

To use PostGIS extension you should enable it for a specific database.

For example, you can create the new database named mygisdata with the psql tool as follows:

CREATE database mygisdata;
\c mygisdata;
CREATE SCHEMA gis;

Next, enable the postgis extension. Make sure you are connected to the database you created earlier and run the following command:

CREATE EXTENSION postgis;

Finally, check that the extension is enabled:

SELECT postgis_full_version();

The output should resemble the following:

postgis_full_version
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="3.3.3" [EXTENSION] PGSQL="140" GEOS="3.10.2-CAPI-1.16.0" PROJ="8.2.1" LIBXML="2.9.13" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"

You can find more about using PostGIS in the official Percona Distribution for PostgreSQL documentation , as well as in this blogpost .

Get expert help

If you need assistance, visit the community forum for comprehensive and free database knowledge, or contact our Percona Database Experts for professional support and services. Join K8S Squad to benefit from early access to features and “ask me anything” sessions with the Experts.


Last update: 2024-03-29