How to backup and restore PostgreSQL on Kubernetes

To jump directly to PostgreSQL specific backup information. Jump to the below section.

Kubernetes runs mission critical applications in production; that is a fact. Running applications—especially stateful applications in production—requires care and planning. We have covered how to run a highly-available PostgreSQL service on Kubernetes in production already, and now we are going to focus on the best way to protect your data services in production with backup and restore.

Here are some of the things you need to ask yourself when protecting PostgreSQL with backup and restore on Kubernetes in production:

• What Kubernetes objects do I need to backup?
• How can I backup my PersistentVolumes (PVs)?
• Where do my backup files live?
• How long do my backups remain available?
• Can I restore to another Kubernetes cluster?
• Who has access to my backups?
• Who can take a backup?
• Can I take backups on a set schedule?
• How long will my backups take?
• Are my backups secure?

This blog post will aim to answer most of the questions you may be asking and provide a clear guide on how to backup PostgreSQL on Kubernetes in production.

The essential steps to backup PostgreSQL on Kubernetes

When you are creating a backup and restore strategy, it is important to remember that not every data service needs the same level of protection. In production, we typically need the best level of protection that fits our business needs as well as those of our customers. Given this, it’s best to understand the essential steps for creating a backup and restore plan in production.

1. Understand WHO will be responsible for creating backups.
2. Know what level of RPO (recovery point objective) you require.
3. Make sure you know where your backup targets will be located.
4. Plan out what type of schedule and retention plan your backups need.
5. Make sure any application-aware hooks are in place so your backups are app-consistent.

We’ll walk through and explain these critical steps for backing up PostgreSQL in production in more detail as well as provide examples and screenshots below.

Performing backup and restore for PostgreSQL

Before we can backup PostgreSQL, we must first configure PX-Backup with the access to the cluster which it is running in.

> Note, this blog does not go into installing PX-Backup; for this, please reference our documentation.

Within the PX-Backup interface, select Add Cluster from the navigation.

Then you will need to provide a cluster name and a Kubeconfig for the Kubernetes cluster you are using as well as the Portworx details if you have access to them. Keep in mind that the Kubeconfig is what controls the type of access you have to the cluster, and the same is true within PX-Backup. If you only have access to one namespace, you will only be able to set up backup and restore for that namespace. Also, if you do not have access to the Portworx cluster details or are not using Portworx for volumes, you can leave that section blank.

Once you have done this, you should see your cluster added to the main screen. There should be a green backup icon in the cluster tile that will bring you to the backup view for the cluster.

If your backup icon is not green, double check that you are running Stork 2.4+ in the cluster you added.

> Refer to the Add Cluster screen within the backup UI. You can easily copy the command to add stork to your cluster.

(Running Portworx)

KBVER=$(kubectl version --short | awk -Fv '/Server Version: /{print $3}')

curl -fsL -o stork-spec.yaml "https://install.portworx.com/2.5?kbver=${KBVER}&comp=stork"

kubectl apply -f stork-spec.yaml

(Not running Portworx)

curl -fsL -o stork-spec.yaml "https://install.portworx.com/2.5?comp=stork&storkNonPx=true"

kubectl apply -f stork-spec.yaml

Configure your backup targets

Before we can backup PostgreSQL from our cluster, we must create a backup target location. Click on Cloud Settings to be taken to the location where you can input your target credentials and bucket information.

You can follow our documentation on how to set up various backup targets. At a minimum, you will need to create both a single Cloud Account (e.g., AWS, Azure, Google) and a Backup Location (e.g., bucket in cloud object store).

When you create a backup location, you will select the previously created Cloud Account and then fill in the bucket information accordingly for your backup target provider.

Consider characteristics of the backup location. Retention of data within the backup location as well as the geographic location of the location are not configured by PX-Backup and thus should be carefully configured prior to using the location.

To make sure backups are encrypted within the backup location, enter an encryption key of your choice.

Create a backup schedule

This step is optional. However, using a schedule enables point in time recovery (PITR) of applications. Using a schedule will provide the PITR points that can be used.

You may want to create a schedule specifying how often your backup takes place (that allows you to meet your RPO objectives) and how many copies of the backup are to be kept (if you need RPO zero, check out kubernetes disaster recovery). Click Schedule Policies from the settings navigation, and this will bring you to a view where you can configure your schedules.

Click the Add button in the navigation bar.

From here, create your desired schedule. When you choose Periodic, Daily, Weekly, or Monthly, you will then be able to select the number of retained copies. Later, you will be able to select this schedule when we create our backup for PostgreSQL.

Pre- and post- rules for creating application-consistent PostgreSQL backups

Backups should be taken when the system can verify that the data service is ready for a backup. This is called application awareness. For this reason, we may want to control logic that happens before and after a backup is triggered to help control consistency based on the above information. With PX-Backup, we configure pre- and post- rules that can run arbitrary commands inside one or more pods of the data service to achieve these goals.

First, we need to understand how PostgreSQL stores states. This will ultimately help provide a blueprint for our backup policies and rules in production.

Postgres places data within a data directory known as PGDATA. The most common location for PGDATA is /var/lib/pgsql/data. You must configure your Kubernetes spec file with this volumeMount when using a persistent volume. This directory contains control, configuration, and data files related to the Postgres server. Assigning the PGDATA directory a persistent volume claim will enable backup and restore for all the Postgres server data. An example of such configuration is below.

<snip>
        volumeMounts:
        - name: postgres-data
          mountPath: /var/lib/postgresql/data
      volumes:
      - name: postgres-data
        persistentVolumeClaim:
          claimName: postgres-data
</snip>

Postgres documentation states, “As with everything that contains valuable data, PostgreSQL databases should be backed up regularly, ” and it describes various techniques for doing so:

• SQL dump
• Filesystem level backup
• Consistent snapshot
• Continuous archiving

PX-Backup will perform a consistent block-level snapshot, which will quiesce and freeze the data directory file system, including the WAL (write-ahead log). PX-Backup captures an atomic view of all Postgres data. When Postgres recovers from this type of snapshot, the Postgres server needs to replay the WAL as if the Postgres server had not been properly shut down since the snapshot is taken on the live system. To reduce recovery times, Postgres documentation suggests taking a CHECKPOINT to flush data to disk before any snapshots.

“A checkpoint is a point in the write-ahead log sequence at which all data files have been updated to reflect the information in the log. All data files will be flushed to disk.” – postgresql 12.x documentation 

PX-Backup can also take simultaneous snapshots of a volume group. This can be useful if you are running Postgres with multiple nodes (highly available master and slave deployment) or if files exist on more than one filesystem.

PX-Backup can create backups which are application aware by using rules. We will create a pre-backup rule that uses CHECKPOINT in our backup scenario.

Note: We are not using the low level base backup APIs pg_start_backup() and pg_stop_backup() because we are not copying files; rather, PX-backup provides consistent block-level snapshots for one or a group of volumes. Postgres also has a crash-consistent design and can benefit from the added speed of these types of backups. The low level backup methods typically take more time to complete and require an open session, which most pre- and post- hooks inherently don’t provide. 

Let’s now move on to create a pre- backup rule in the PX-Backup interface.

Pre- rule for PostgreSQL

For the pre- backup rule, we will create a CHECKPOINT rule that will run within our Postgres pod(s). If you set up the password file, you may not need to use environment variables for the password in the below example. However, if you have injected the Postgres password into the pod as an environment variable via secrets, the below rule can be used.

The action for the pre-exec backup rule will be to run the CHECKPOINT command via the appropriate Postgres user.

PGPASSWORD=$POSTGRES_PASSWORD; psql -U "$POSTGRES_USER" -c "CHECKPOINT";

This is what these rules will look like in the PX-Backup UI.

Post- rule for PostgreSQL

PostgreSQL is designed in such a way that it is crash consistent. Since there is no outright read or write locking done with Postgres, it will start back up as if it has crashed. This is perfectly fine and expected because it is crash safe and designed to work that way. Postgres documentation states:

“…therefore, when you start the database server on the backed-up data, it will think the previous server instance crashed and will replay the WAL log. This is not a problem; just be aware of it (and be sure to include the WAL files in your backup).”

In other words, because we are using consistent and atomic snapshots taken by Portworx, we don’t need a post-backup rule. So, we can move on to the next section and leave the post-backup rule blank during backup creation in the PX-Backup interface.

Create the backup for PostgreSQL

Now that configuration is done and we have created rules for our application, we are ready to start backing up PostgreSQL. What we need to do is enter the backup view of the cluster our service is running in and select the namespace that our application is running in.

Once you are in the namespace, you can select the labels associated with PostgreSQL to only backup certain objects with the label or backup the entire namespace by selecting the Backup button in the top right corner of the namespace backup view.

Once you are into the backup-specific input, give the backup the following information in the pop out menu:

• Name
• Backup Target
• Backup Now or On a schedule
• Provide Pre- and Post- rules
• Optional backup labels for accounting

Once you complete this information, click Create.

Once created, the backup will enter a Pending and then an In Progress state. The backup icon will look like the below example.

To view the progress of what is happening in the backup, you can select the navigation to the right side of the backup and select the Show Details button. This will allow you to view the current status and metadata of the associated backup. Any progress or errors will be reported within this screen.

Part of this information will be the status of the pre- and post- rules we created earlier. When they are being executed, it will be reported as in progress, and any output or errors will be present within this screen if there is an issue.

Once the rule is executed, it will continue and start to back up the volumes, and the details will change. An example of these details can be seen below.

Upon successful completion of the backup, the icon will look like the below example.

If something goes wrong, you will notice the icon turn red, and details of the failure will be available in the Show Details section of the backup.

Restoring PostgreSQL from backup

To start a restore, select Backups from the top navigation bar.

Then find the backup you wish to restore and select the Restore selection from the right navigation.

From here you can provide a name for your restore and a destination cluster as well as the following options.

• Default restore
  • This will restore the backup to the namespace which it came from. Take note of the “replace existing resources” option when using this method.
• Custom restore
  • This will allow you to provide a new namespace name to restore your backup to. Note that this namespace does not need to pre-exist.

• Replace existing resources
  • This will make sure the restore operation overwrites the objects that may already exist. Note that objects will be deleted then re-created.
• Restore Jobs
  • Jobs often run once and complete. It is not always necessary to run these jobs again—especially when restoring to the same cluster where the backup was taken. However, it may be necessary when restoring to a new cluster or new namespace.

You should see a similar transition in the UI from Pending to Success, and you can select Show Details from the menu—just like with the backup—to get the active information about the backup.

→

Demo

Conclusion

As we’ve just seen, backup and restore is a critical piece of the data protection plan for any application in Kubernetes today. PX-Backup makes it simple to understand who, when, where, and how the backup takes place—all while making it hyper configurable to specific data services for extra consistency and application awareness. Remember to check out the documentation on backup—as well as take advantage of a free trial.