Backups

Backups are enabled by default. The PostgreSQL instance will be backed up daily at a randomly chosen time between 22:00 and 4:00. The backup is done using https://pgbackrest.org/ tool which supports Point-in-Time Recovery. This approach allows to make a backup of an online database with minimum performance hit. It also guarantees that the changes made to the data during the backup process will also end up in the backup files.

You can specify a different backup time and interval using the following backup configuration:

apiVersion: vshn.appcat.vshn.io/v1
kind: VSHNPostgreSQL
metadata:
  name: pgsql-app1-prod
  namespace: <your-namespace>
spec:
  parameters:
    ...
    backup:
      schedule: 0 22 * * * (1)
    ...
1 The schedules for your backups.

Point in Time interval

By default, the WALs (Write Ahead Logs) will be shipped every time a WAL file has been completed. They grow to 16Mb before being considered completed and be archived to the backup object store. This will result in variable RPO times.

If fixed RPO time is required, the archive_timeout setting can be set. It will force an archive of the WAL files every given interval.

apiVersion: vshn.appcat.vshn.io/v1
kind: VSHNPostgreSQL
metadata:
  name: pgsql-app1-prod
  namespace: <your-namespace>
spec:
  parameters:
    ...
    service:
      pgSettings:
        archive_timeout: "300" (1)
    ...
1 Amount of time between WAL archives in seconds

However, the WAL files will always be 16Mb, even if it’s not completed. So using this setting will most likely increase the data usage on the backup object store.

Listing Backups

Get an overview of all backups performed of your database instance:

$ kubectl -n <your-namespace> get vshnpostgresbackups
BACKUP NAME                                 DATABASE INSTANCE   STORED TIME               STATUS     AGE
pgsql-app1-prod-pk8k4-2023-03-05-13-05-00   pgsql-app1-prod     2023-03-05T13:05:15.755Z  Completed  38s
pgsql-app1-dev-pk8k4-2023-03-01-16-52-11    pgsql-app1-dev                                Failed     28s
The list will show all backups from all instances in this namespace. In the example above there are two database instances with one backup each.

Backup Details

Learn more about a chosen backup:

$ kubectl -n <your-namespace> get vshnpostgresbackups pgsql-app1-prod-pk8k4-2023-03-05-13-05-00 -oyaml
apiVersion: api.appcat.vshn.io/v1
kind: VSHNPostgresBackup
metadata:
  name: pgsql-app1-prod-pk8k4-2023-03-05-13-05-00 (1)
  namespace: <your-namespace>
status:
  databaseInstance: pgsql-app1-prod (2)
  process:
    failure: "" (3)
    jobPod: pgsql-app1-prod-pk8k4-backup-27967024-6xjpq
    status: Completed
    timing:
      end: "2023-03-05T13:05:15.61228Z"
      start: "2023-03-05T13:05:09.519369Z"
      stored: "2023-03-05T13:05:15.755Z" (4)
1 The name of the backup.
2 The name of the database instance of this backup.
3 A failure message if the backup job failed.
4 The date and time of the backup.