Automating database backups with Kubernetes CronJobs

For many years, system administrators have used Cron to automate recurring tasks on Unix systems, in comparison CronJobs in Kubernetes is the new kid on the block, reaching general availability in April of 2021, CronJobs provides a resource that allows users to schedule recurring tasks, each CronJob is similar to a crontab (cron table) file on a Unix system.

In this tutorial, we’ll look at how to automate database backups using Kubernetes CronJobs, to store the backups, we will be leveraging Civo’s object storage.

Prerequisites

This article assumes some working knowledge of Kubernetes. In addition, you would need the following installed:

• A Civo Account
• A Kubernetes cluster
• Kubectl installed
• psql
• Civo CLI installed

Creating a Database

We’ll begin by creating a database using the Civo CLI:

1civo db create backup-labs -m PostgreSQL

This creates a one-node database cluster. Using the -m flag we supply the type of database we want to create. At the time of writing, Civo supports PostgreSQL and MySQL.

From this, you should see the following output:

Seeding the Database

Before we create a backup, we’ll need a database. In your terminal, run the following command to create one:

1psql -U civo -h [HOST_IP] -W -c 'create database customers;'

Next, let’s create a schema and populate the database with some mock data. In a directory of your choice, create a file named schema.sql. Add the following code to define the Customers table:

Creating a table

1CREATE TABLE Customers (
2    ID  serial ,
3    Name varchar(50) NOT NULL,
4    Phone varchar(15) NOT NULL,
5    Address varchar(50),
6    Birthday date NOT NULL,
7    CustomerEmail varchar(50) NOT NULL,
8    PRIMARY KEY (ID)
9);

Apply the schema changes

1psql -U civo -d customers -h 74.220.17.133 -W -f  schema.sql

Adding mock data

Begin creating a new file called data.sql within your editor of choice, and add the following code:

1INSERT INTO Customers (Name, Phone, Address, Birthday, CustomerEmail)
2SELECT
3    md5(random()::text || clock_timestamp()::text)::uuid::varchar(50) as Name,
4    substring(md5(random()::text || clock_timestamp()::text)::uuid::varchar(50), 1, 15) as Phone,
5    md5(random()::text || clock_timestamp()::text)::uuid::varchar(50) as Address,
6    current_date - interval '18 years' - random() * interval '50 years' as Birthday,
7    md5(random()::text || clock_timestamp()::text)::uuid::varchar(50) || '@example.com' as CustomerEmail
8FROM generate_series(1, 100); -- Adjust the number of rows as needed

Apply the schema changes

1psql -U civo -d customers -h <YOUR-DATABASE-IP> -W -f data.sql

You can verify the mock data was indeed generated by running the following command:

1psql -U civo -d customers -h 74.220.17.133 -W -c 'select * from customers;'

Output should be similar to:

Creating a backup script

With a database created, we can shift our attention towards the backups. For this demonstration, we will be using a bash script to perform the backup operations. Create file named backup.sh and add the following code:

1#!/bin/bash
2
3DB_HOST=$DB_HOST
4DB_NAME=$DB_NAME
5S3_BUCKET=$S3_BUCKET
6BACKUP_PREFIX=cronjob
7
8# Create a timestamped backup filename
9BACKUP_FILENAME="${BACKUP_PREFIX}_$(date +%Y%m%d_%H%M%S).sql"
10
11# Create the database backup
12PGPASSWORD="$DB_PASSWORD" pg_dump -U civo -h $DB_HOST $DB_NAME > ./$BACKUP_FILENAME
13
14# configure aws cli
15aws configure set aws_access_key_id $AWS_ACCESS_KEY_ID
16aws configure set aws_secret_access_key $AWS_SECRET_ACCESS_KEY
17aws configure set default.region LON1
18
19# Upload the backup to S3
20aws --endpoint-url <https://objectstore.lon1.civo.com> s3 cp $BACKUP_FILENAME s3://$S3_BUCKET
21
22# Cleanup (optional)
23rm $BACKUP_FILENAME

The script starts by defining several environment variables that will be used later:

• DB_HOST: The hostname or IP address of the PostgreSQL database server
• DB_NAME: The name of the PostgreSQL database to back up
• S3_BUCKET: The name of the bucket to upload backups to
• BACKUP_PREFIX: A prefix that will be added to backup filenames

It then constructs a backup filename using BACKUP_PREFIX, the current date/time, and a .sql extension. This ensures each backup has a unique name.

The pg_dump command creates a compressed backup file in custom format. It connects to the DB server using the configured credentials and database name and writes the output to the backup filename generated earlier.

As we have used object store, we created resides in the LON1 region on Civo. The endpoint URL is https://objectstore.lon1.civo.com.

The AWS CLI is configured using the access key and secret access key environment variables. This allows uploading the backup file to S3.

Civo's object storage is S3 compatible, which means it can be accessed and managed using the same tools and APIs as Amazon S3. Therefore, we can utilize the AWS CLI, a command-line interface tool for interacting with AWS services, to upload backups to Civo's object storage.

Finally, the backup is uploaded to the specified S3 bucket and then deleted locally. The upload location in S3 will be s3://$S3BUCKET/$BACKUPFILENAME.

Containerizing the backup

Next up, we need to create a container image we can deploy to our Kubernetes cluster. Create a file named Dockerfile and add the following directives:

1FROM ubuntu:22.04 
2RUN apt-get update && apt-get install -y \\
3    curl \\
4    openssl  \\
5    postgresql-client \\
6    python3-pip  \\
7    libsasl2-modules \\
8    libssl-dev \\
9    postgresql-client-common \\
10    libpq-dev
11
12RUN pip3 install awscli
13RUN mkdir /scripts 
14COPY backup.sh /scripts
15WORKDIR /scripts
16RUN chmod +x backup.sh
17ENTRYPOINT [ "./backup.sh" ]

Next, we need to build and push the image to a container registry. In this demo, we will be using ttl.sh, an ephemeral container registry that doesn’t require authentication to use, this makes it easy to use in demos such as these. In production, you’d probably want to use an internal registry or something like DockerHub to store your images.

Build and push the image

1export IMAGE_NAME=k8s-db-backup
2docker build --push -t ttl.sh/${IMAGE_NAME}:1h .

Creating an object store

Before we begin scheduling backups, the last resource we need to provision is an object store. This can be any S3-compatible storage, for this demonstration, I would be using Civo’s object storage. To create an object store using the CLI, run the following commands:

Generate object store credentials

1civo objectstore credentials create k8s-backup

Create the object store

1civo objectstore create –region LON1 k8s-db-backup --owner-access-key k8s-backup --wait`

Finally, you’d need to obtain your object store credentials. To do this using the CLI, run:

1civo objectstore credential secret --access-key=[your access key]

Scheduling backups

With all the moving parts in place, we can finally schedule a backup using the CronJob resource. Create a file named backup.yaml and follow along with the code below:

1apiVersion: batch/v1
2kind: CronJob
3metadata:
4  name: db-backup
5spec:
6  schedule: "*/5 * * * *"
7  jobTemplate:
8    spec:
9      template:
10        spec:
11          containers:
12          - name: db-backup
13            image: ttl.sh/k8s-db-backup:1h
14            env:
15            - name: DB_HOST 
16              value: [YOUR DB IP ADDRESS]
17            - name: DB_NAME
18              value: "customers"
19            - name: DB_PASSWORD
20              valueFrom:
21                secretKeyRef:
22                  name: db-password 
23                  key: password
24            - name: AWS_ACCESS_KEY_ID
25              valueFrom: 
26                secretKeyRef:
27                  name: civo-credentials
28                  key: access-key-id  
29            - name: AWS_SECRET_ACCESS_KEY
30              valueFrom:
31                secretKeyRef:
32                  name: civo-credentials
33                  key: secret-access-key
34            - name: S3_BUCKET
35              value: "k8s-db-backup"
36          restartPolicy: OnFailure

In the manifest above, we created a new CronJob resource named db-backup. The job also specifies the docker image containing the backup script we created earlier and configures the appropriate environment variables.

Before we can apply this manifest, we need to supply the secrets. To do this, create a file named secrets.yaml and add the following code:

1apiVersion: v1
2kind: Secret
3metadata:
4  name: db-password
5type: Opaque
6data:
7  password: <BASE64_ENCODED_DB_PASSWORD> 
8---
9apiVersion: v1
10kind: Secret
11metadata:
12  name: civo-credentials
13type: Opaque
14data:
15  access-key-id: <BASE64_ENCODED_AWS_ACCESS_KEY_ID> 
16  secret-access-key: <BASE64_ENCODED_AWS_SECRET_ACCESS_KEY>

Apply the manifests:

1# create the secret 
2kubectl apply -f secrets.yaml 
3
4# create the cronJob 
5kubectl apply -f backup.yaml

In five minutes, the CronJob should kick in, and Kubernetes will spawn a new pod using the image we provided to perform the backup. To verify the pods get created, run the following:

1kubectl get pods

Viewing backups

To view the backup that has been created, head over to your Civo dashboard. Click on the object store tab and select the bucket you created. You should see the following:

Summary

Whether you run your database within or outside Kubernetes, backups will always remain an essential part of your disaster recovery plan. In this tutorial, we covered one of many ways to backup your database using CronJobs.