You are looking at the documentation of a prior release. To read the documentation of the latest release, please
visit here.
New to KubeDB? Please start here.
Don’t know how to take continuous backup? Check this tutorial on Continuous Archiving.
PostgreSQL Initialization from Local Storage
WAL-G is used to handle replay, and restoration mechanism. Please refer to Initialization from WAL files in KubeDB to know more about it.
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 kind.
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
Prepare WAL Archive
We need a WAL archive to perform initialization. If you don’t have a WAL archive ready, create one by following the tutorial here.
Let’s populate the database so that we can verify that the initialized database has the same data. We will exec into the database pod and use psql command-line tool to create a table.
At first, find out the primary replica using the following command,
$ kubectl get pods -n demo --selector="app.kubernetes.io/instance=wal-postgres","kubedb.com/role=primary"
NAME READY STATUS RESTARTS AGE
wal-postgres-0 1/1 Running 0 8m
Now, let’s exec into the pod and create a table,
$ kubectl exec -it -n demo wal-postgres-0 sh
# login as "postgres" superuser.
/ # psql -U postgres
psql (11.1)
Type "help" for help.
# list available databases
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
# connect to "postgres" database
postgres=# \c postgres
You are now connected to database "postgres" as user "postgres".
# create a table
postgres=# CREATE TABLE COMPANY( NAME TEXT NOT NULL, EMPLOYEE INT NOT NULL);
CREATE TABLE
# list tables
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | company | table | postgres
# quit from the database
postgres=# \q
# exit from the pod
/ # exit
Now, we are ready to proceed for rest of the tutorial.
Note: YAML files used in this tutorial are stored in docs/examples/postgres folder in GitHub repository kubedb/docs.
Create Postgres with WAL source
User can initialize a new database from this archived WAL files. We have to specify the archive backend in the spec.init.postgresWAL field of Postgres object.
The YAML file in this tutorial creates a Postgres object using WAL files from Local Storage.
apiVersion: kubedb.com/v1alpha2
kind: Postgres
metadata:
name: replay-postgres
namespace: demo
spec:
version: "11.1-v3"
replicas: 2
authSecret:
name: wal-postgres-auth
storage:
storageClassName: "standard"
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 50Mi
init:
postgresWAL:
local:
mountPath: /tmp/replay-postgres
subPath: wal-postgres-0
persistentVolumeClaim:
claimName: pgbackup
Here,
spec.init.postgresWALspecifies storage information that will be used byWAL-Gspec.init.postgresWAL.localpoints to Local Storage configuration.spec.init.postgresWAL.mountPathpoints to a path where Local Storage containing archived WAL data will be mounted.spec.init.postgresWAL.subPathpoints to the path inside the mountPath where archived WAL data can be found. It is usually the name of the archiver’s primary pod.spec.init.postgresWAL.persistentVolumeClaim.claimNamepoints to the same local volume that was used to store WAL files.
wal-g receives archived WAL data from a directory {mountPath}/{subPath} inside the pod.
Note: Postgres
replay-postgresmust have same superuser credentials as archived Postgres. In our case, it iswal-postgres.
Now, let’s create the Postgres object that’s YAML has shown above,
$ kubectl create -f https://github.com/kubedb/docs/raw/v2021.01.02-rc.0/docs/examples/postgres/initialization/replay-postgres-local.yaml
postgres.kubedb.com/replay-postgres created
This will create a new database and will initialize the database from the archived WAL files.
Verify Initialization
Let’s verify that the new database has been initialized successfully from the WAL archive. It must contain the table we have created for wal-postgres database.
We will exec into new database pod and use psql command-line tool to list tables of postgres database.
$ kubectl exec -it -n demo replay-postgres-0 sh
# login as "postgres" superuser
/ # psql -U postgres
psql (11.1)
Type "help" for help.
# list available databases
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
# connect to "postgres" database
postgres=# \c postgres
You are now connected to database "postgres" as user "postgres".
# list tables
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | company | table | postgres
(1 row)
# quit from the database
postgres=# \q
# exit from pod
/ # exit
So, we can see that our new database replay-postgres has been initialized successfully and contains the data we had inserted into wal-postgres.
Cleaning up
To cleanup the Kubernetes resources created by this tutorial, run:
kubectl patch -n demo pg/replay-postgres -p '{"spec":{"terminationPolicy":"WipeOut"}}' --type="merge"
kubectl delete -n demo pg/replay-postgres
kubectl delete ns demo
Also cleanup the resources created for wal-postgres following the guide here.
Next Steps
- Learn about initializing PostgreSQL with Script.
- Monitor your PostgreSQL database with KubeDB using built-in Prometheus.
- Monitor your PostgreSQL database with KubeDB using Prometheus operator.
- Want to hack on KubeDB? Check our contribution guidelines.