Backing Up and Restoring Tanzu Postgres

Tanzu SQL with Postgres includes the pgBackRest backup and restore solution. Backups can be stored to any S3 compatible location, external (like Amazon AWS) or on-premise (like Minio). A Kubernetes secret handles the S3 bucket access authentication. This topic describes the Tanzu SQL with Postgres backup secret setup, the pgBackRest configuration details, the backup process, and the restore process.

See the pgBackRest documentation for more detailed information about using pgBackRest.

Setting up the environment

Prerequisites

Before creating an S3 Kubernetes secret you need:

  • access permissions to a preconfigured S3 bucket where the pgdata persistent volume (PV) backups will be stored.
  • the values bucket, region, endpoint, key, and keySecret of the preconfigured S3 bucket, that are required to setup the S3 backup secret.
  • the kubectl command line tool installed on your local client, with access permissions to the Kubernetes cluster.
  • the instance namespace, if the Postgres instance is already created. Use kubectl get namespaces for a list of available namespaces.

Create a S3 Kubernetes Secret

  1. To enable backup in Tanzu SQL with Postgres, first create a Kubernetes secret to allow access to the S3 backup location.

    Create a s3-secret-example.yaml file, that is used as a Kubernetes secret for the S3 bucket access. Use the template below, and edit according to your environment:

     apiVersion: v1
     kind: Secret
       metadata:
       name: s3-secret-example
     type: generic
       stringData:
       bucket: "your_S3_bucket_name"
       region: "your_region"
       endpoint: "your_endpoint"
       key: "your_key"
       keySecret: "your_key_secret"
    
       #optional fields with default values. Change to your own configuration
       port:"443"
       uriStyle:"host"
       verifyTLS: "true"
    

    Where:

    • name (required) is the name of the kubernetes secret object created with this yaml.
    • bucket (required) is the name of the S3 bucket, for example “postgres-containers-dev-backup”.
    • region (required) is the S3 bucket region, for example “us-west-01”.
    • endpoint (required) is the S3 repository endpoint, for example “minio.svc.cluster.local” or “s3.amazonaws.com”.
    • key (required) is the S3 key provided by your S3 storage administrator, for example “AKIAIOSFODNN7EXAMPLE”.
    • keySecret(required) is the S3 key secret provided by your S3 storage administrator, for example “wJalrXUtnFEMI/K7MDENG/ bPxRfiCYEXAMPLEKEY”.
    • port (optional) is the S3 repository port used for the endpoint connection, for example 9000.
    • uriStyle (optional) is the S3 repository URI option, for example “path”. For more information see S3 Repository URI Style Option in the pgBackRest documentation.
    • verifyTLS (optional) is determines the TLS authentication state; default is “true”.
  2. Create the Kubernetes secret:

    $ kubectl apply -f s3-secret-example.yaml -n <your-instance-namespace>
    

    where s3-secret-example matches the name value.

  3. Verify that the secret was created successfully:

    $ kubectl get secrets s3-secret-example
    

    The output should be similar to:

    NAME                         TYPE     DATA   AGE
    s3-secret-example            Opaque   3      108m
    
  4. Use this secret as the name value in the backupLocationSecret field of the Postgres instance manifest file. See Configuring the Postgres Instance Manifest file.

    Edit the pg-instance-example.yaml file, providing a value for backupLocationSecret, and use kubectl to refresh the configuration:

    kubectl apply -f pg-instance-example.yaml
    

    You can then use the pgBackRest commands for taking backups and restoring.

Taking backups

Performing a Backup

  1. If you have an HA configuration, verify that you are on the primary node. For more information, see Verifying High Availability.

  2. The first time you set up pgBackRest on the primary node (or the single node for non-HA configurations), create the pgBackRest stanza using kubectl exec:

    $ kubectl exec -it pg-instance-example-0 -- bash -c 'pgbackrest stanza-create --stanza=${BACKUP_STANZA_NAME}'
    
    2020-03-02 20:19:12.194 P00   INFO: WAL segment 00000001000000000000000D successfully archived to '/pgbackrest/backups/archive/${BACKUP_STANZA_NAME}/11-1/0000000100000000/00000001000000000000000D-ea744b6c5b4db4f6b8b125ea76ce7e433534eaf7.gz'
    2020-03-02 20:19:12.195 P00   INFO: check command end: completed successfully (2675ms)
    

    Check the pgBackRest stanza using kubectl exec:

     $ kubectl exec -it pg-instance-example-0 -- bash -c 'pgbackrest check --stanza=${BACKUP_STANZA_NAME}'
    
  3. To perform a backup of a Postgres instance run the pgbackrest backup command.

    $ kubectl exec -it pg-instance-example-0 -- bash -c 'pgbackrest backup --stanza=${BACKUP_STANZA_NAME}'
    

    The --stanza option is the only required option. See Perform a Backup in the pgBackRest documentation for a full list of pgBackRest options.

    The default is to perform an incremental backup (--type=incremental) if two full backups exist. An incremental backup requires an existing full backup; if no full backup exists, pgBackRest performs a full backup instead of an incremental backup.

    To specify a full backup of the ${BACKUP_STANZA_NAME} database, use:

    $ kubectl exec -it pg-instance-example-0 -- bash -c 'pgbackrest backup --stanza=${BACKUP_STANZA_NAME} --type=full'
    

Getting Backup Information

The pgbackrest info command lists backup information.

$ kubectl exec -it pg-instance-example-0 -- bash -c 'pgbackrest info --stanza=${BACKUP_STANZA_NAME}'
stanza: ${BACKUP_STANZA_NAME}
    status: ok
    cipher: aes-256-cbc

    db (current)
        wal archive min/max (11-1): 000000010000000000000005/000000010000000000000005

        full backup: 20200302-183323F
            timestamp start/stop: 2020-03-02 18:33:23 / 2020-03-02 18:33:34
            wal start/stop: 000000010000000000000005 / 000000010000000000000005
            database size: 31.0MB, backup size: 31.0MB
            repository size: 3.7MB, repository backup size: 3.7MB

    incr backup: 20200302-183323F_20200302-184244I
        timestamp start/stop: 2020-03-02 18:42:44 / 2020-03-02 18:42:48
        wal start/stop: 000000010000000000000007 / 000000010000000000000007
        database size: 31.0MB, backup size: 8.2KB
        repository size: 3.7MB, repository backup size: 519B
        backup reference list: 20200302-183323F

See the pgBackRest Backup Information for other command options for pgbackrest info.

Restoring a Backup

Restoring a single Postgres instance

The pgbackup restore command restores backups. You can restore all databases or individual databases. The built-in databases template0, template, and postgres are always restored.

Restoring requires manually stopping and restarting the Postgres instance, after identifying the pg_auto_failover process that monitors and runs the Postgres instance. Find the process identifier (PID) of the pg_auto_failover sub-process, stop it, and then stop the Postgres instance.

  1. Get the pg_auto_failover child process pid:

    $ kubectl exec -t pg-instance-example-0 -- ps -ef | grep "pg_autoctl: start/stop postgres" | grep -v grep | awk '{print $2}'
    
  2. Stop the pg_auto_failover child process:

    $ kubectl exec -t pg-instance-example-0 -- kill -STOP <pid from above>
    
  3. Stop the Postgres instance:

    $ kubectl exec -t pg-instance-example-0 -- pg_ctl stop
    
  4. Perform the restore:

    $ kubectl exec -t pg-instance-example-0 -- bash -c 'pgbackrest restore --stanza=${BACKUP_STANZA_NAME} --delta --db-include=my-postgres'
    
  5. Restart the pg_auto_failover child process (which will bring a Postgres process back up):

    $ kubectl exec -t pg-instance-example-0 -- kill -CONT <pid from above>
    
    2020-03-05 18:52:11.926 P00   INFO: restore backup set 20200305-184945F
    2020-03-05 18:52:11.928 P00   INFO: remove invalid files/links/paths from '/pgsql/data'
    2020-03-05 18:52:12.368 P02   INFO: restore file /pgsql/data/global/pg_control.pgbackrest.tmp (8KB, 88%) checksum b0b618494c0f57557afbc61df0948182861b55b5
    2020-03-05 18:52:12.487 P02   INFO: restore file /pgsql/data/backup_label (257B, 99%) checksum 58a0d0f21f0d16ca8f6f076754e4bcefdc2e559d
    2020-03-05 18:52:12.578 P00   INFO: write /pgsql/data/recovery.conf
    2020-03-05 18:52:12.599 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
    2020-03-05 18:52:12.802 P00   INFO: restore command end: completed successfully (1012ms)
    

See Restore a Backup on the pgBackRest website for detailed information about pgBackRest restore options.

Restoring in a High Availability Cluster

To restore Tanzu SQL with Postgres in a high availability scenario, disable the HA configuration, delete the secondary node, remove the secondary data volume claim, and follow the restore process on the primary node.

  1. Go to the directory where you unpacked the Tanzu SQL with Postgres distribution.

  2. Edit the pg-instance yaml, disable HA, and re-apply the configuration via kubectl.
    Note: the HA disable process is not a supported workflow outside a restore scenario.

    $ kubectl apply -f pg-instance-example.yaml
    
  3. Using kubectl on the monitor pod, identify and drop the replica node. Under the Host:Port column, the hostname uses the instance name and namespace (default in this example). Identify the replica node using show state and finding the status secondary under Current State:

    $ kubectl exec -t pg-instance-example-monitor-0 -- pg_autoctl show state
    
    Name   |  Node |                                                      Host:Port |       LSN | Reachable |  Current State |   Assigned State
    -------+-------+----------------------------------------------------------------+-----------+-----------+----------------+------------------
    node_1 |     1 | pg-instance-example-0.pg-instance-example-agent.default.svc.cluster.local:5432 | 0/4000108 |       yes |   wait_primary |      wait_primary
    node_2 |     2 | pg-instance-example-1.pg-instance-example.default.svc.cluster.local:5432 | 0/4000108 |        no |      secondary |        catchingup
    

    Drop the replica (secondary) node using drop node:

    $ kubectl exec -t pg-instance-example-monitor-0 -- pg_autoctl drop node --hostname pg-instance-example-1.pg-instance-example-agent.default.svc.cluster.local --pgport 5432
    

    Delete the corresponding persistant volume claim (PVC):

    $ kubectl delete pvc pg-instance-example-pgdata-pg-instance-example-1 --namespace <your-instance-namespace>
    
  4. Restore the data following the steps in Restoring a single Postgres instance.

  5. Re-enable high availability by modifying the pg-instance yaml, and reapply via kubectl.

    $ kubectl apply -f pg-instance-example.yaml
    

Validating a Successful Restore

  1. View the Postgres log to verify that the Postgres server started.

    $ kubectl logs my-postgres-0
    

    You may see log events for past failures, for example:

    Normal   Started                 76s (x2 over 78s)    kubelet, gke-postgres-k8s-cluster-default-pool-6a8f2693-rjw9  Started container pg-container
    Warning  Unhealthy               50s                  kubelet, gke-postgres-k8s-cluster-default-pool-6a8f2693-rjw9  Readiness probe failed: psql: FATAL:  the database system is shutting down
    Warning  Unhealthy               44s (x2 over 47s)    kubelet, gke-postgres-k8s-cluster-default-pool-6a8f2693-rjw9  Readiness probe failed: psql: could not connect to server: No such file or directory
    Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
    

    These messages are normal and expected. Look for status: ok and ready: true messages near the end of the log to verify the server is currently running.

  2. Verify that you can connect to the Postgres instance and access the restored data.

    $ kubectl exec -it pg-instance-example-0 -- /bin/bash -c "psql somedb -c 'select count(1) from sometable'"
    

Review the pgBackRest Configuration

For reference and troubleshooting, you may review the Tanzu SQL with Postgres pgBackRest configuration properties in postgresql.conf in the /pgsql/data/ directory. The file specifies the backup details, and is pre-configured with a stanza name matching the Postgres instance database name.

To view the contents of the pgbackrest.conf file:

$ kubectl exec -it pg-instance-example-0 cat /pgsql/data/pgbackrest.conf
[global]
# configure encryption
repo1-cipher-pass=tpwZwPmQHs850nmtxHYUGOFWS6Oj8z
repo1-cipher-type=aes-256-cbc
# configure retention
repo1-retention-diff=1
repo1-retention-full=2
# configure parallelism
process-max=2
# configure logs
log-path=/pgsql/logs/
# log-level-console=info
log-level-console=debug
log-level-file=debug
archive-async=y
spool-path=/pgsql/spool
start-fast=y

[${BACKUP_STANZA_NAME}]
repo1-type=s3
repo1-s3-key=admin
repo1-s3-key-secret=adminsecret
repo1-s3-bucket=postgresql-backups
repo1-s3-endpoint=minio.minio.svc.cluster.local
repo1-s3-port=9000
repo1-s3-verify-tls=n
repo1-s3-region=us-east-1
repo1-s3-uri-style=path
pg1-path=/pgsql/data

The [global] section defines the location of backups, logging settings, and encryption settings. The pgBackRest configuration file sets the following backup defaults:

  • repo1-path=/pgbackrest/backups - Backups and archives are stored in the /pgbackrest/backups directory (the repo1 repository).
  • repo1-cipher-pass=Fi2iRk4rvZ66MqSgPktzJERJ2987VG - The passphrase used to encrypt the repository. In this example, the value repo1-cipher-type=aes-256-cbc defines the repo1 repository encryption as aes-256.
  • repo1-retention-full=2 - Two full backups are retained. When a full backup expires, differential and incremental backups associated with the full backup also expire.
  • repo1-retention-diff=1 - One differential backup per full backup is retained. When a differential backup expires, the incremental backups associated with it also expire.
  • process-max=2 - Backups are performed with two parallel processes. Increasing this parameter can improve backup performance.
  • log-path=/pgbackrest/logs - Log files are saved at /pgbackrest/logs.
  • log-level-console=info - Send info, warning, and error messages to the console.
  • log-level-file=debug - Send debug, detail, info, warning, and error messages to the log file.
  • archive-async=y - Asynchronous operation is enabled for the archive-push and achive-get commands.
  • spool-path=/pgbackrest/spool - WAL segments are queued in the /pgbackrest/spool directory.
  • start-fast=y - Forces a checkpoint so the backup starts immediately.

The [${BACKUP_STANZA_NAME}] section defines a stanza name that matches the Postgres instance database name, for example the repo1 backup repository. The pg1-path parameter is the data directory for the ${BACKUP_STANZA_NAME} stanza.

See pgBackRest Configuration Reference for information about the pgBackRest configuration file organization and parameters.