{"id":24203,"date":"2021-11-19T18:32:38","date_gmt":"2021-11-19T13:02:38","guid":{"rendered":"https:\/\/mobisoftinfotech.com\/resources\/?p=24203"},"modified":"2025-11-27T12:43:55","modified_gmt":"2025-11-27T07:13:55","slug":"install-postgresql-and-postgis-in-gke","status":"publish","type":"post","link":"https:\/\/mobisoftinfotech.com\/resources\/blog\/install-postgresql-and-postgis-in-gke","title":{"rendered":"How to Install PostgreSQL and PostGIS in Google Kubernetes Engine (GKE)"},"content":{"rendered":"

Though there are PostgreSQL Operators to fully manage PostgreSQL High Availability (HA) installations in Kubernetes (K8S), sometimes you don’t need HA. For many small applications, you just need a single instance of PostgreSQL with daily backup. For these cases, simple K8S deployment with persistent volume and once in 24 hours backup with K8S CronJob is fine. I will show you how to run a single instance of PostgreSQL with daily backups in this post. <\/span><\/p>\n\n\n\n

For this tutorial, I assume you have a K8S cluster on GCP and Kubectl configured on your local machine.  If you don’t have one, please refer to this <\/span>Quickstart <\/span><\/a>on Google Kubernetes Engine docs. Google also gives you $300 credit to explore Google Cloud Services, which should be more than sufficient to try out this tutorial. <\/span><\/p>\n\n\n\n

Namespace<\/b><\/h2>\n\n\n\n

I am using a namespace called prod<\/span>. <\/span><\/p>\n\n\n\n

Filename: namespace.yaml<\/strong><\/span><\/p>\n\n\n

apiVersion: v1\nkind: Namespace<\/span>\nmetadata<\/span>:\n  name<\/span>: prod<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
To create the namespace, run the command:<\/span><\/p>\n\n\n
kubectl<\/span> apply<\/span> -f<\/span> namespace<\/span>.yaml<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
This should create the namespace for you. <\/span><\/p>\n\n\n\n
K8S Secret<\/b><\/h2>\n\n\n\n
Now we need to create a K8S secret so that we don’t have to specify the PostgreSQL user password in plain text. <\/span><\/p>\n\n\n\n
Create a file named prod-postgresql-12-password.txt<\/span> and add a sufficiently complex password in it. Make sure that the file contains only 1 line, and you DO NOT put a line break (Enter) at the end of the line. Once the file is ready, you can run the following command to create the Kubernetes Secret:<\/span><\/p>\n\n\n
kubectl create secret --namespace=prod generic prod-pgsql-12<\/span>-password \\\n  --from<\/span>-file=password=.\/passwords\/prod-postgresql-12<\/span>-password.txt<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
K8S Deployment and Service<\/b><\/h2>\n\n\n\n
Next, we need to create the Persistent Volume, Deployment, and Service for PostgreSQL. We will put all of these in the same file so that we can refer to them together later. <\/span><\/p>\n\n\n\n
Filename: postgresql-12.yaml<\/strong><\/span><\/p>\n\n\n
apiVersion: v1\nkind<\/span>: PersistentVolumeClaim\nmetadata<\/span>:\n  name: pgsql12-volumeclaim\n  namespace<\/span>: prod\nspec<\/span>:\n  storageClassName: standard\n  accessModes<\/span>:\n    - ReadWriteOnce\n  resources<\/span>:\n    requests:\n      storage: 20<\/span>Gi\n---\napiVersion: apps\/v1\nkind<\/span>: Deployment\nmetadata<\/span>:\n  name: pgsql12\n  namespace<\/span>: prod\nspec<\/span>:\n  replicas: 1<\/span>\n  selector<\/span>:\n    matchLabels:\n      app: pgsql12\n  strategy<\/span>:\n    type: Recreate\n  template<\/span>:\n    metadata:\n      labels:\n        app: pgsql12\n      namespace<\/span>: prod\n    spec<\/span>:\n      containers:\n        - name: postgres\n          image<\/span>: postgis\/postgis:12<\/span>-3.1<\/span>\n          resources<\/span>:\n            requests:\n              memory: \"1024Mi\"<\/span>\n              cpu<\/span>: \"250m\"<\/span>\n            limits<\/span>:\n              memory: \"1024Mi\"<\/span>\n              cpu<\/span>: \"250m\"<\/span>\n          imagePullPolicy<\/span>: \"IfNotPresent\"<\/span>\n          ports<\/span>:\n            - containerPort: 5432<\/span>\n          env<\/span>:\n            - name: POSTGRES_DB\n              value<\/span>: \"postgresdb\"<\/span>\n            - name: POSTGRES_USER\n              value<\/span>: \"pguser\"<\/span>\n            - name: POSTGRES_PASSWORD\n              valueFrom<\/span>:\n                secretKeyRef:\n                  name: prod-pgsql-12<\/span>-password\n                  key<\/span>: password\n          volumeMounts<\/span>:\n            - mountPath: \/var\/<\/span>lib\/postgresql\/data\n              name<\/span>: postgredb\n              subPath<\/span>: postgres\n      volumes<\/span>:\n        - name: postgredb\n          persistentVolumeClaim<\/span>:\n            claimName: pgsql12-volumeclaim\n---\napiVersion: v1\nkind<\/span>: Service\nmetadata<\/span>:\n  name: pgsql12\n  namespace<\/span>: prod\n  labels<\/span>:\n    app: pgsql12\nspec<\/span>:\n  type: ClusterIP\n  ports<\/span>:\n    - protocol: TCP\n      port<\/span>: 5451<\/span>\n      targetPort<\/span>: 5432<\/span>\n  selector<\/span>:\n    app: pgsql12\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Here we are creating 3 K8S objects:<\/span><\/p>\n\n\n\n
    \n
  1. PersistentVolumeClaim <\/b>of 20GB to hold our PostgreSQL data.<\/span><\/li>\n<\/ol>\n\n\n\n
      \n
    1. Deployment, <\/b>which runs postgis\/postgis:12-3.1<\/span> image so that we can use the wonderful PostGIS extension for GeoSpatial analysis along with PostgreSQL. Here we have assigned 1GB RAM and 1\/4th of the CPU core to this DB. To get better performance, you might need to increase this value depending on the kind of nodes you have in your K8S cluster. <\/span><\/li>\n<\/ol>\n\n\n\n
        \n
      1. Service <\/b>which exposes the PostgreSQL service at port 5451 using Cluster IP. Since this is specified as Cluster IP, one can’t connect to the database outside the cluster. <\/span><\/li>\n<\/ol>\n\n\n\n
        To create these objects, you can run the following command:<\/span><\/p>\n\n\n
        kubectl<\/span> apply<\/span> -f<\/span> postgresql-12<\/span>.yaml<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
        At this point, you should have PostgreSQL running inside your GKE cluster. <\/span><\/p>\n\n\n\n
        Port Forwarding<\/b><\/h2>\n\n\n\n
        To connect to the database from your local machine, you need to set up temporary port forwarding. You can do the same using the following command:<\/span><\/p>\n\n\n
        kubectl port-forward service\/pgsql12 5451<\/span>:5451<\/span> --namespace<\/span>=prod<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
        At this point, the PostgreSQL server should be available on your localhost:5451<\/span> port. You can connect to it using the psql<\/span> command-line utility or using a GUI tool like DBeaver. You will need to use pguser<\/span> as the username and the password you used while creating the K8S secret. <\/span><\/p>\n\n\n\n