Don’t know how backup works? Check tutorial on Instant Backup.

Initialize PostgreSQL with Snapshot

KubeDB supports PostgreSQL database initialization.

Before You Begin

At first, you need to have a Kubernetes cluster, and the kubectl command-line tool must be configured to communicate with your cluster. If you do not already have a cluster, you can create one by using minikube.

Now, install KubeDB cli on your workstation and KubeDB operator in your cluster following the steps here.

To keep things isolated, this tutorial uses a separate namespace called demo throughout this tutorial.

$ kubectl create ns demo
namespace "demo" created

$ kubectl get ns demo
NAME    STATUS  AGE
demo    Active  5s

Note: Yaml files used in this tutorial are stored in docs/examples/postgres folder in github repository kubedb/cli.

This tutorial will show you how to use KubeDB to initialize a PostgreSQL database with existing snapshot data.

So, we need a Snapshot object in Succeeded phase to perform this initialization .

Follow these steps to prepare this tutorial

• Create Postgres object script-postgres, if not exists.

  $ kubedb create -f https://raw.githubusercontent.com/kubedb/cli/0.8.0/docs/examples/postgres/initialization/script-postgres.yaml
  postgres "script-postgres" created
  

  $ kubedb get pg -n demo script-postgres
  NAME                STATUS    AGE
  script-postgres     Running   57s
  

• Create storage Secret.
  In this tutorial, we need a storage Secret for backup process

  $ echo -n '<your-project-id>' > GOOGLE_PROJECT_ID
  $ mv downloaded-sa-json.key > GOOGLE_SERVICE_ACCOUNT_JSON_KEY
  $ kubectl create secret -n demo generic gcs-secret \
      --from-file=./GOOGLE_PROJECT_ID \
      --from-file=./GOOGLE_SERVICE_ACCOUNT_JSON_KEY
  secret "gcs-secret" created
  

• Take an instant backup, if not available. Follow this.

$ kubedb get snap -n demo --selector="kubedb.com/kind=Postgres,kubedb.com/name=script-postgres"
NAME               DATABASE             STATUS      AGE
instant-snapshot   pg/script-postgres   Succeeded   39s

Create PostgreSQL with Snapshot source

Specify the Snapshot name and namespace in the spec.init.snapshotSource field of your new Postgres object.

See the example Postgres object below

apiVersion: kubedb.com/v1alpha1
kind: Postgres
metadata:
  name: recovered-postgres
  namespace: demo
spec:
  version: "9.6"
  databaseSecret:
    secretName: script-postgres-auth
  storage:
    storageClassName: "standard"
    accessModes:
    - ReadWriteOnce
    resources:
      requests:
        storage: 50Mi
  init:
    snapshotSource:
      name: instant-snapshot
      namespace: demo

Here,

• spec.init.snapshotSource specifies Snapshot object information to be used in this initialization process.
  • snapshotSource.name refers to a Snapshot object name.
  • snapshotSource.namespace refers to a Snapshot object namespace.

Snapshot instant-snapshot in demo namespace belongs to Postgres script-postgres:

$ kubedb get snap -n demo instant-snapshot
NAME               DATABASE             STATUS      AGE
instant-snapshot   pg/script-postgres   Succeeded   12m

Note: Postgres recovered-postgres must have same postgres superuser password as Postgres script-postgres.

Now, create the Postgres object.

$ kubedb create -f https://raw.githubusercontent.com/kubedb/cli/0.8.0/docs/examples/postgres/initialization/recovered-postgres.yaml
postgres "recovered-postgres" created

When PostgreSQL database is ready, KubeDB operator launches a Kubernetes Job to initialize this database using the data from Snapshot instant-snapshot.

As a final step of initialization, KubeDB Job controller adds kubedb.com/initialized annotation in initialized Postgres object. This prevents further invocation of initialization process.

$ kubedb describe pg -n demo recovered-postgres -S=false -W=false
Name:           recovered-postgres
Namespace:      demo
StartTimestamp: Thu, 08 Feb 2018 17:23:21 +0600
Status:         Running
Annotations:    kubedb.com/initialized
Init:
  snapshotSource:
    namespace:  demo
    name:       instant-snapshot
Volume:
  StorageClass: standard
  Capacity:     50Mi
  Access Modes: RWO
StatefulSet:    recovered-postgres
Service:        recovered-postgres, recovered-postgres-replicas
Secrets:        script-postgres-auth

Topology:
  Type      Pod                    StartTime                       Phase
  ----      ---                    ---------                       -----
  primary   recovered-postgres-0   2018-02-08 17:23:26 +0600 +06   Running

No Snapshots.

Events:
  FirstSeen   LastSeen   Count     From                Type       Reason               Message
  ---------   --------   -----     ----                --------   ------               -------
  20s         20s        1         Postgres operator   Normal     Successful           Successfully patched StatefulSet
  20s         20s        1         Postgres operator   Normal     Successful           Successfully patched Postgres
  24s         24s        1         Job Controller      Normal     SuccessfulSnapshot   Successfully completed initialization
  31s         31s        1         Postgres operator   Normal     Initializing         Initializing from Snapshot: "instant-snapshot"
  34s         34s        1         Postgres operator   Normal     Successful           Successfully created StatefulSet
  34s         34s        1         Postgres operator   Normal     Successful           Successfully created Postgres
  35s         35s        1         Postgres operator   Normal     Successful           Successfully created Service
  35s         35s        1         Postgres operator   Normal     Successful           Successfully created Service

Now lets connect to our Postgres recovered-postgres using pgAdmin we have installed in quickstart tutorial.

Connection information:

• address: use Service recovered-postgres.demo
• port: 5432
• database: postgres
• username: postgres

Run following command to get postgres superuser password

$ kubectl get secrets -n demo script-postgres-auth -o jsonpath='{.data.\POSTGRES_PASSWORD}' | base64 -d

In PostgreSQL, run following query to check pg_catalog.pg_tables to confirm initialization.

select * from pg_catalog.pg_tables where schemaname = 'data';

We can see TABLE dashboard in data Schema which is created for initialization.

Cleaning up

To cleanup the Kubernetes resources created by this tutorial, run:

$ kubectl patch -n demo pg/script-postgres pg/recovered-postgres -p '{"spec":{"doNotPause":false}}' --type="merge"
$ kubectl delete -n demo pg/script-postgres pg/recovered-postgres

$ kubectl patch -n demo drmn/script-postgres drmn/recovered-postgres -p '{"spec":{"wipeOut":true}}' --type="merge"
$ kubectl delete -n demo drmn/script-postgres drmn/recovered-postgres

$ kubectl delete ns demo

Next Steps

• Learn about initializing PostgreSQL with Script.
• Learn how to schedule backup of PostgreSQL database.
• Want to setup PostgreSQL cluster? Check how to configure Highly Available PostgreSQL Cluster
• Monitor your PostgreSQL database with KubeDB using built-in Prometheus.
• Monitor your PostgreSQL database with KubeDB using CoreOS Prometheus Operator.
• Wondering what features are coming next? Please visit here.
• Want to hack on KubeDB? Check our contribution guidelines.