Clone a database cluster in a single server using a Cloud Storage backup

This page show you how to clone a database cluster in a single server using a Cloud Storage backup.

The following workflow explains the cloning steps:

  1. Configure the pgbackrest.conf file to access the Cloud Storage backup.
  2. Use pgBackRest commands to verify source backups can be accessed.
  3. Use pgBackRest commands to restore the backup to the target server.

Before you begin

  • Access to the full path of the Cloud Storage bucket where your source database cluster backup resides. This is the same path you used when you created the BackupPlan resource for your source database cluster.
  • A single server target AlloyDB Omni database cluster is created. For more information about installing AlloyDB Omni on Kubernetes, see Install AlloyDB Omni.
  • Ensure you are logged in to the database as the postgres user.

Configure the pgBackRest file on the target server

Configure the pgBackRest file to enable the target database cluster to access the Cloud Storage bucket where source backups reside.

  1. In the target server, navigate to the alloydb-data directory.

  2. Create a pgBackRest configuration file to access backups stored in Cloud Storage:

      $ cat << EOF > /backup/pgbackrest.conf
      [db]
      pg1-path=/mnt/disks/pgsql/data-restored
      pg1-socket-path=/tmp
      pg1-user=pgbackrest
      [global]
      log-path=/obs/pgbackrest
      log-level-file=info
      repo1-type=gcs
      repo1-gcs-bucket=GCS_SOURCE_BACKUP_BUCKET_NAME
      repo1-path=GCS_SOURCE_BACKUP_BUCKET_PATH
      repo1-storage-ca-file=/etc/ssl/certs/ca-certificates.crt
      repo1-retention-full=9999999
      repo1-gcs-key-type=auto

    Replace the following:

    • GCS_SOURCE_BACKUP_BUCKET_NAME: the name of the Cloud Storage pgBackRest bucket you created in the source database cluster. This is not the full URL to the bucket; don't prefix the bucket name with gs://.
    • GCS_SOURCE_BACKUP_BUCKET_PATH: the path of the directory that the AlloyDB Omni Operator writes backups into, within the Cloud Storage bucket for the source database cluster. The path must be absolute, beginning with /.

    The repo1-gcs-key-type is set to auto to use the instance's service account. For more information about other options, see GCS Repository Key Type Option.

Verify source backups in target server

Sign in to the target server and run pgBackRest commands to verify that the source database cluster backups are accessible on the target server:

    sudo docker exec pg-service pgbackrest --config-path=/mnt/disks/pgsql --stanza=db --repo=1 info

The following is a sample response:

    stanza: db
      status: ok
      cipher: none
      db (current)
          wal archive min/max (15): 000000010000000000000002/00000001000000000000000D
          full backup: 20240213-231400F
              timestamp start/stop: 2024-02-13 23:14:00+00 / 2024-02-13 23:17:14+00
              wal start/stop: 000000010000000000000003 / 000000010000000000000003
              database size: 38.7MB, database backup size: 38.7MB
              repo1: backup set size: 4.6MB, backup size: 4.6MB
          incr backup: 20240213-231400F_20240214-000001I
              timestamp start/stop: 2024-02-14 00:00:01+00 / 2024-02-14 00:00:05+00
              wal start/stop: 00000001000000000000000D / 00000001000000000000000D
              database size: 38.7MB, database backup size: 488.3KB
              repo1: backup set size: 4.6MB, backup size: 84.2KB
              backup reference list: 20240213-231400F

The timestamps in the response are used either to restore the full backup or to restore from a point in time from the recovery window.

Restore the backup in the target server

After you identify the backup or a point in time you want to restore to, run pgBackRest commands in your target server. For more information about these commands, see Restore Command.

The following are some sample pgBackRest restore commands:

  • Restore from a backup

    pgbackrest --config-path=/mnt/disks/pgsql --stanza=db --repo=1 restore --set=20240213-231400F --type=immediate --target-action=promote --delta --link-all --log-level-console=info
  • Restore from a point in time

    pgbackrest --config-path=/mnt/disks/pgsql --stanza=db --repo=1 restore --target="2024-01-22 11:27:22" --type=time --target-action=promote --delta --link-all --log-level-console=info

Copy data to the target server

After the restore command completes successfully, you can copy the data from the /mnt/disks/pgsql/data-restored temporary directory to the current /alloydb-data/data directory.

  1. In the target server, stop the database service:

    alloydb database-server stop
  2. Rename the current data directory to another name as a best practice:

      mv ~/alloydb-data/data  ~/alloydb-data/data-old
  3. Rename the data-restored temporary directory to the current data directory:

      mv ~/alloydb-data/data-restored ~/alloydb-data/data
  4. Update pg1-path value in the postgresql.auto.conf file to load the restored data:

    vim ~/alloydb-data/data/postgresql.auto.conf
    # Verify postgresql.auto.conf.
    # Do not edit this file manually!
    # It will be overwritten by the ALTER SYSTEM command.
    # Recovery settings generated by pgBackRest restore on 2024-03-13 20:47:11
    restore_command = 'pgbackrest --config-path=/mnt/disks/pgsql --pg1-path=/mnt/disks/pgsql/data --repo=1 --stanza=db archive-get %f "%p"'
    recovery_target = 'immediate'
    recovery_target_action = 'promote'
    recovery_target_timeline = 'current'
  1. In the target server, start the database service:

    alloydb database-server start

After the database service starts, you can connect to the primary instance and run queries to verify that the data is restored from the backup. For more information, see Connect to AlloyDB Omni on a single server.

What's next