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.

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.
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
```
Create the Kubernetes namespace for your cluster if needed (for example, let’s name it postgres-operator):
```
$ kubectl create namespace postgres-operator
```
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)
```

Start the Operator within Kubernetes:

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

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.

Community Forum Get a Percona Expert Join K8S Squad

Last update: 2024-04-17