How to Backup a PostgreSQL Database from OpenShift to AWS S3

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
 
 

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

 
 
robin repo list
 
 
 

You should see an output similar to the following.

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.

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.

 

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.

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
 
 

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;"
 
 

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
 
 

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
 
 

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;"
 
 

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.

Recommended Posts