How to Backup a PostgreSQL Database from OpenShift to AWS S3

By June 7, 2019 June 13th, 2019 AWS, Blog, OpenShift

In this tutorial, we will backup a PostgreSQL database that is deployed on OpenShift to AWS3. We will then simulate an error in the database and restore the entire database from the backup.

Before you begin this tutorial, make sure you have installed ROBIN Storage on your existing OpenShift cluster, your PostgreSQL database is deployed on OpenShift, has data loaded in it, and the Helm release is registered with ROBIN and you have taken a snapshot of your deployed PostgreSQL database.

Backup PostgreSQL Database from OpenShift to AWS S3

ROBIN elevates the experience from backing up just storage volumes (PVCs) to backing up entire applications/databases, including their metadata, configuration, and data.

A backup s a full copy of the application snapshot that resides on completely different storage media than the application’s data. Therefore, backups are useful to restore an entire application from an external storage media in the event of catastrophic failures, such as disks errors, server failures, or entire datacenters going offline, etc.

Let’s now back up our database to an external secondary storage repository (repo). Snapshots (metadata + configuration + data) are backed up into the repo.

ROBIN enables you to backup your Kubernetes applications to AWS S3 or Google GCS ( Google Cloud Storage. In this demo, we would use AWS S3 to create a backup.

Before we proceed, we need to create an S3 bucket and get access parameters for it. Follow the documentation here.

Let us first register an AWS repository with ROBIN:

robin repo register pgsqlbackups s3://robin-pgsql/pgsqlbackups awstier.json readwrite --wait
Backup Postgres from OpenShift to AWS - Register Repository

Let’s confirm that our secondary storage repository is successfully registered.

robin repo list

You should see an output similar to the following.

Backup PostgreSQL - Register AWS with ROBIN

Let us now attach this repo to our app so that we can back up its snapshots there.

robin repo attach pgsqlbackups movies --wait

Let us confirm that our secondary storage repository is successfully attached to our PostgreSQL database.

robin app info movies

You should see an output similar to the following.

PostgreSQL backup from OpenShift to AWS3 - confirm secondary storage repo attached to app

Let’s backup up our snapshot to the registered secondary storage repository using the snapshot ID from the Snapshot PostgreSQL tutorial.

robin backup create bkp-of-my-movies Your_Snapshot_ID pgsqlbackups --wait

Let us now confirm that the snapshot has been backed up in S3 by running the following command.

robin app info movies

You should see an output similar to the following.

Backup PostgreSQL from OpenShift to AWS3 - snapshot backed up in S3

Let’s also confirm that backup has been copied to the remote s3 repository.

robin repo contents pgsqlbackups

You should see an output similar to the following.

Backup PostgreSQL from OpenShift to AWS3 - copy on remote S3 repo

The snapshot has now been backed up into our AWS S3 bucket.

Now since we have backed-up our application snapshot to the cloud, let us delete that snapshot locally.

robin snapshot delete Your_Snapshot_ID --wait
Backup PostgreSQL from OpenShift to AWS3 - delete snapshot locally

Now let’s simulate a data loss situation by deleting all data from the “movies” table.

$PGPASSWORD="$POSTGRES_PASSWORD" psql -h $IP_ADDRESS -U postgres -d testdb -c "DELETE from movies;"

Let us verify that all data is lost.

$PGPASSWORD="$POSTGRES_PASSWORD" psql -h $IP_ADDRESS -U postgres -d testdb -c "SELECT * from movies;"
Backup Postgres verify data loss

We will now use our backed-up snapshot on S3 to restore the data we just lost.
Now let’s restore to a snapshot from the backup in the cloud and then roll back our application to that snapshot.

robin snapshot pull movies Your_Backup_ID --wait
Backup PostgreSQL - snapshot from backup

Remember, we had deleted the local snapshot of our data. Let us verify that the above command has pulled the snapshot stored in the cloud. Run the following command:

robin snapshot list --app movies
Backup Snapshot - pull snapshot from the cloud

Rollback

Now we can rollback to the snapshot, we just retrieved from the cloud, to get our data back and restore the desired state.

robin app rollback Your_Snapshot_ID movies --wait

Let’s verify all 9 rows are restored to the “movies” table by running the following command.

PGPASSWORD="$POSTGRES_PASSWORD" psql -h $IP_ADDRESS -U postgres -d testdb -c "SELECT * from movies;"
Backup PostgreSQL from OpenShift to AWS3 - 9 movies

As you can see, we can restore the database to the desired state in the event of data corruption. We simply pull the backup from the cloud and use it to restore the database.

This concludes the Backup PostgreSQL from OpenShift to AWS3 tutorial.

mm

Author Ripul Patel, Sr Software Engineer

More posts by Ripul Patel, Sr Software Engineer