Configure application-consistent backups

This document shows you how to configure scheduled, application-consistent backups and point-in-time recovery workflows for self-managed databases running on Compute Engine Linux virtual machine (VM) instances.

When you back up stateful database workloads—such as IBM Db2, SAP HANA, MySQL, or PostgreSQL—standard crash-consistent snapshots taken by Backup and DR Service can capture the database while transactions are still in progress. These incomplete transactions can corrupt data, leave tables in inconsistent states, and require lengthy database repair routines upon crash recovery. Application-consistent backups resolve these issues by integrating with the VM guest agent to quiesce the database and optionally freeze the file system before creating a snapshot.

Objectives

• Understand the BYOS guest-flush framework and script requirements.
• Review sample scripts for your database.
• Deploy guest-flush scripts on the VM instance.
• Enable the guest agent for snapshots.
• Configure an application-consistent backup plan in Backup and DR Service.
• Configure Persistent Disk backups for database archive logs.
• Restore and recover the database to a specific point in time.

Costs

This tutorial uses the following billable components of Google Cloud:

• Compute Engine
• Backup and DR Service
• Persistent Disk
• Secret Manager

Use the pricing calculator to generate a cost estimate based on your projected usage.

Before you begin

1. Select or create a Google Cloud project.

   Go to project selector

2. Enable billing for your project.

   Enable billing

3. Ensure your application data is stored on standard Persistent Disks. Google Cloud Hyperdisk volumes don't support guest-flush operations.

4. If you plan to configure point-in-time recovery, configure your database to archive its transaction and redo logs to a dedicated, separate Persistent Disk, for example, mounted at /db2/ACT/log_archive.

5. If you retrieve database credentials dynamically from Secret Manager in your scripts, ensure the service account associated with the Compute Engine instance has the Secret Manager Secret Accessor role (roles/secretmanager.secretAccessor) for the specific secret, and that the VM instance has access to the Secret Manager API.

Use the bring-your-own-script (BYOS) guest-flush framework

Backup and DR Service uses the Bring-Your-Own-Script (BYOS) guest-flush framework to quiesce your database before a snapshot is taken. The guest environment relies on two scripts residing in the /etc/google/snapshots/ directory on your Linux VM instance:

• Pre-snapshot script (/etc/google/snapshots/pre.sh): Executes before the snapshot is taken. This script must quiesce database operations and can optionally freeze the file system.
• Post-snapshot script (/etc/google/snapshots/post.sh): Executes after the snapshot is taken. This script must optionally unfreeze the file system and resume database operations.

To learn more about database-specific script templates, see Review sample scripts by database type.

Freeze and unfreeze file systems (Optional)

Freezing the file system using the standard Linux utility fsfreeze is optional and not mandatory. Quiescing your database ensures application-level transaction consistency. However, if you require additional file-system-level consistency, you can optionally include the following file system freeze and unfreeze commands in your scripts.

Ensure your pre.sh and post.sh scripts on the VM are configured to handle optional file system freezing for log volumes without suspending database operations.

Add file system freeze instructions to pre.sh

If you choose to freeze file systems, add the following discovery and freeze logic at the end of your pre.sh script after quiescing the database:

# Discover local writeable mount points
MOUNT_POINTS=$(findmnt -n -l -o TARGET -t xfs,ext4,ext3,ext2,btrfs)

# FREEZE FILESYSTEMS (Optional)
for mnt in $MOUNT_POINTS; do
    if ! sudo fsfreeze -f "$mnt"; then
        logger "Error: Failed to freeze $mnt. Cleaning up..."
        # Attempt emergency thaw
        for thaw_mnt in $MOUNT_POINTS; do sudo fsfreeze -u "$thaw_mnt" 2>/dev/null; done
        # Add your database-specific resume/cleanup commands here before exiting
        exit 1
    fi
    logger "Filesystem $mnt is frozen."
done

Add file system unfreeze instructions to post.sh

If you freeze file systems in pre.sh, add the following unfreeze logic at the beginning of your post.sh script before resuming the database:

# Discover local writeable mount points
MOUNT_POINTS=$(findmnt -n -l -o TARGET -t xfs,ext4,ext3,ext2,btrfs)

# UNFREEZE FILESYSTEMS (Optional)
for mnt in $MOUNT_POINTS; do
    sudo fsfreeze -u "$mnt"
    logger "Filesystem $mnt is unfrozen."
done

Fetch credentials dynamically with Secret Manager

Avoid hardcoding static database credentials or passwords inside your shell scripts. Instead, use Secret Manager to fetch credentials dynamically at runtime. Replace static password variables in your scripts with the following logic:

# Fetch the database password from Secret Manager dynamically
DBPASSWORD=$(gcloud secrets versions access latest --secret="YOUR_SECRET_NAME")

if [ $? -ne 0 ]; then
    logger "Critical Error: Unable to fetch the password from Secret Manager."
    exit 1
fi

Review sample scripts by database type

DB2

Pre-snapshot script (/etc/google/snapshots/pre.sh)

 #!/bin/bash
 # Location: /etc/google/snapshots/pre.sh
 # 1. Define Mount Points for the Database
 MOUNT_POINTS=("/db_data" "/db_logs")
 logger "BYOS Framework: Starting pre-snapshot script."

 # 2. QUIESCE DATABASE (Generic Hook)
 # Replace with DB-specific command (e.g., 'db2 set write suspend')
 logger "Suspending Database I/O..."
 # [INSERT DB QUIESCE COMMAND HERE]

 # 3. FREEZE FILESYSTEMS (Optional)
 for mnt in "${MOUNT_POINTS[@]}"; do
     if ! sudo fsfreeze -f "$mnt"; then
         logger "Error: Failed to freeze $mnt."
         # Attempt emergency thaw
         for thaw_mnt in "${MOUNT_POINTS[@]}"; do sudo fsfreeze -u "$thaw_mnt" 2>/dev/null; done
         # [INSERT DB RESUME COMMAND HERE]
         exit 1
     fi
     logger "Filesystem $mnt is frozen."
 done

Post-snapshot script (/etc/google/snapshots/post.sh)

 #!/bin/bash
 # Location: /etc/google/snapshots/post.sh
 MOUNT_POINTS=("/db_data" "/db_logs")
 logger "BYOS Framework: Starting post-snapshot script."

 # 1. UNFREEZE FILESYSTEMS (Optional)
 for mnt in "${MOUNT_POINTS[@]}"; do
     sudo fsfreeze -u "$mnt"
     logger "Filesystem $mnt is unfrozen."
 done

 # 2. RESUME DATABASE (Generic Hook)
 # Replace with DB-specific command (e.g., 'db2 set write resume')
 logger "Resuming Database I/O..."
 # [INSERT DB RESUME COMMAND HERE]
 logger "BYOS Framework: Cleanup complete."

SAP HANA

Pre-snapshot script (/etc/google/snapshots/pre.sh)

 #!/bin/bash
 # Location: /etc/google/snapshots/pre.sh

 DISK_ARG="$1"

 # 1. Configuration & Discovery
 DBSID="DM2"              # Replace with your SAP HANA SID
 DBADM="dm2adm"
 ID_FILE="/tmp/hana_snapshot_id_$DBSID"
 HDB_KEY="ACTBACKUP"      # HDB userstore key for authentication

 if [[ "$DISK_ARG" == "," ]] || [[ "$DISK_ARG" == "1/0" ]] || [[ "$DISK_ARG" == "sda" ]]; then
     # Dynamically discover all local writeable mount points for full VM image consistency
     # Filters out pseudo, temporary, and read-only filesystems
     MOUNT_POINTS=$(findmnt -n -l -o TARGET -t xfs,ext4,ext3,ext2,btrfs)

     logger "SAP HANA Full VM BYOS: Starting pre-snapshot script for $DBSID."

     # 2. QUIESCE DATABASE (Create SAP HANA Snapshot)
     logger "SAP HANA Full VM BYOS: Creating database snapshot..."

     # Determine HANA Version (Logic adapted from act_saphana_pre.sh)
     # Versions 2.0+ use 'FOR FULL SYSTEM' syntax
     HANAVERSION=$(su - $DBADM -c "HDB version" | grep "version:" | awk '{print $2}' | cut -d'.' -f1)

     if [ "$HANAVERSION" = "1" ]; then
         SQL="BACKUP DATA CREATE SNAPSHOT COMMENT 'GCE_MACHINE_IMAGE_SNAPSHOT'"
     else
         SQL="BACKUP DATA FOR FULL SYSTEM CREATE SNAPSHOT COMMENT 'GCE_MACHINE_IMAGE_SNAPSHOT'"
     fi

     # Execute the snapshot command via hdbsql
     su - $DBADM -c "hdbsql -j -U $HDB_KEY \"$SQL\""
     if [ $? -ne 0 ]; then
         logger "Error: Failed to create SAP HANA snapshot."
         exit 1
     fi

     # Retrieve the BACKUP_ID for the prepared snapshot
     ID=$(su - $DBADM -c "hdbsql -j -t -U $HDB_KEY \"SELECT BACKUP_ID FROM M_BACKUP_CATALOG WHERE STATE_NAME = 'prepared' ORDER BY SYS_START_TIME DESC\"" | head -n 2 | tr -d '"' | tail -n 1)

     if [ -z "$ID" ]; then
         logger "Error: Could not retrieve BACKUP_ID for prepared snapshot."
         exit 1
     fi

     echo "$ID" > "$ID_FILE"
     logger "SAP HANA Full VM BYOS: Database snapshot prepared with ID $ID."
 fi

Post-snapshot script (/etc/google/snapshots/post.sh)

 #!/bin/bash
 # Location: /etc/google/snapshots/post.sh

 DISK_ARG="$1"

 # 1. Configuration & Discovery
 DBSID="DM2"
 DBADM="dm2adm"
 ID_FILE="/tmp/hana_snapshot_id_$DBSID"
 HDB_KEY="ACTBACKUP"

 if [[ "$DISK_ARG" == "," ]] || [[ "$DISK_ARG" == "1/0" ]] || [[ "$DISK_ARG" == "sda" ]]; then
     # Discover all local writeable mount points to perform unfreeze
     MOUNT_POINTS=$(findmnt -n -l -o TARGET -t xfs,ext4,ext3,ext2,btrfs)

     logger "SAP HANA Full VM BYOS: Starting post-snapshot script for $DBSID."

     # 3. RESUME DATABASE (Close SAP HANA Snapshot)
     if [ -f "$ID_FILE" ]; then
         ID=$(cat "$ID_FILE")
         logger "SAP HANA Full VM BYOS: Closing database snapshot ID $ID..."

         # Determine HANA version for correct syntax
         HANAVERSION=$(su - $DBADM -c "HDB version" | grep "version:" | awk '{print $2}' | cut -d'.' -f1)

         if [ "$HANAVERSION" = "1" ]; then
             SQL="BACKUP DATA CLOSE SNAPSHOT BACKUP_ID $ID SUCCESSFUL 'GCE_MACHINE_IMAGE_COMPLETED'"
         else
             SQL="BACKUP DATA FOR FULL SYSTEM CLOSE SNAPSHOT BACKUP_ID $ID SUCCESSFUL 'GCE_MACHINE_IMAGE_COMPLETED'"
         fi

         # Execute the close snapshot command
         RESULT=$(su - $DBADM -c "hdbsql -j -U $HDB_KEY \"$SQL\"")
         if [ $? -ne 0 ]; then
             logger "Error: Failed to close SAP HANA snapshot. $RESULT"
         else
             logger "SAP HANA Full VM BYOS: Database snapshot closed successfully."
         fi

         # Cleanup the temporary ID file
         rm -f "$ID_FILE"
     else
         logger "Error: Snapshot ID file not found. Manual intervention required to close HANA snapshot."
     fi

     logger "SAP HANA Full VM BYOS: Cleanup complete."

 else
     MOUNT_POINTS="/hanabackup"

     for mnt in $MOUNT_POINTS; do
         sudo fsfreeze -u "$mnt"
         logger "Filesystem $mnt is unfrozen."
     done
 fi

SAP ASE

Pre-snapshot script (/etc/google/snapshots/pre.sh)

 #!/bin/bash
 # Location: /etc/google/snapshots/pre.sh
 # 1. Configuration
 OSUSER="sybase" # OS user for Sybase
 SYB_SERVER="SYB_SERVER_NAME"
 SYB_USER="sa"
 SYB_PASS="password" # Recommended: use a secure method to retrieve this
 SYB_DBLIST="db1,db2" # Comma-separated list of databases to quiesce
 TAG_NAME="GCE_SNAPSHOT_$(date +%Y%m%d)"
 MANIFEST_DIR="/var/tmp/sybase_manifests" # Directory for manifest files
 # 2. Discovery
 # Dynamically discover local writeable mount points
 MOUNT_POINTS=$(findmnt -n -l -o TARGET -t xfs,ext4,ext3,ext2,btrfs)

 logger "Sybase Full VM BYOS: Starting pre-snapshot script."
 # 3. QUIESCE DATABASE
 mkdir -p "$MANIFEST_DIR"
 chown $OSUSER "$MANIFEST_DIR"

 logger "Sybase Full VM BYOS: Quiescing databases: $SYB_DBLIST..."
 # Command logic from act_sybase_pre.sh
 # We use 'hold' to suspend activity and create the manifest file
 su - $OSUSER -c "isql -U$SYB_USER -P$SYB_PASS -S$SYB_SERVER -X --retserverror" <<EOF
 quiesce database $TAG_NAME hold $SYB_DBLIST for external dump to '$MANIFEST_DIR/manifest_$TAG_NAME.dat' with override
 go
 exit
 EOF
 if [ $? -ne 0 ]; then
     logger "Error: Failed to quiesce Sybase databases."
     exit 1
 fi
 # 4. FREEZE FILESYSTEMS (Optional)
 for mnt in $MOUNT_POINTS; do
     if ! sudo fsfreeze -f "$mnt"; then
         logger "Error: Failed to freeze $mnt."
         # Attempt emergency thaw
         for thaw_mnt in $MOUNT_POINTS; do sudo fsfreeze -u "$thaw_mnt" 2>/dev/null; done
         # Release the database quiesce
         su - $OSUSER -c "isql -U$SYB_USER -P$SYB_PASS -S$SYB_SERVER -X" <<EOF
         quiesce database $TAG_NAME release
         go
         exit
 EOF
         exit 1
     fi
     logger "Filesystem $mnt is frozen."
 done

Post-snapshot script (/etc/google/snapshots/post.sh)

 #!/bin/bash
 # Location: /etc/google/snapshots/post.sh

 # 1. Configuration
 OSUSER="sybase"
 SYB_SERVER="SYB_SERVER_NAME"
 SYB_USER="sa"
 SYB_PASS="password"
 TAG_NAME="GCE_SNAPSHOT_$(date +%Y%m%d)"
 MANIFEST_DIR="/var/tmp/sybase_manifests"

 # 2. Discovery
 MOUNT_POINTS=$(findmnt -n -l -o TARGET -t xfs,ext4,ext3,ext2,btrfs)

 logger "Sybase Full VM BYOS: Starting post-snapshot script."

 # 3. UNFREEZE FILESYSTEMS (Optional)
 for mnt in $MOUNT_POINTS; do
     sudo fsfreeze -u "$mnt"
     logger "Filesystem $mnt is unfrozen."
 done

 # 4. RESUME DATABASE
 logger "Sybase Full VM BYOS: Releasing databases for tag $TAG_NAME..."

 # Command logic from act_sybase_post.sh
 su - $OSUSER -c "isql -U$SYB_USER -P$SYB_PASS -S$SYB_SERVER -X" <<EOF
 use master
 go
 quiesce database $TAG_NAME release
 go
 exit
 EOF

 if [ $? -ne 0 ]; then
     logger "Error: Failed to release Sybase databases."
 else
     logger "Sybase Full VM BYOS: Databases released successfully."
     # Cleanup manifest
     rm -f "$MANIFEST_DIR/manifest_$TAG_NAME.dat"
 fi

 logger "Sybase Full VM BYOS: Cleanup complete."

SAP IQ

Pre-snapshot script (/etc/google/snapshots/pre.sh)

 #!/bin/bash
 # Location: /etc/google/snapshots/pre.sh

 # 1. Configuration
 OSUSER="sybiq" # OS user for SybaseIQ
 DB_NAME="MY_IQ_DB"
 CONN_STR="uid=DBA;pwd=password;dbn=$DB_NAME;eng=MY_ENG"
 BKP_DIR="/var/tmp/sybaseiq_bkp"
 FULL_BKP_FILE="$BKP_DIR/FULL_VIR_DEC"

 # 2. Discovery
 # Dynamically discover all local writeable mount points for full VM consistency
 MOUNT_POINTS=$(findmnt -n -l -o TARGET -t xfs,ext4,ext3,ext2,btrfs)

 logger "SybaseIQ Full VM BYOS: Starting pre-snapshot script."

 # 3. QUIESCE DATABASE (Virtual Decoupled Backup)
 mkdir -p "$BKP_DIR"
 chown $OSUSER "$BKP_DIR"

 logger "SybaseIQ Full VM BYOS: Preparing virtual decoupled backup..."

 # Execute the quiesce command logic from act_sybaseiq_pre.sh
 su -m $OSUSER -c "dbisql -nogui -c '$CONN_STR' \"BACKUP DATABASE FULL VIRTUAL DECOUPLED TO '$FULL_BKP_FILE'\""

 if [ $? -ne 0 ]; then
     logger "Error: Failed to freeze (not able to take full_vir_dec backup)."
     exit 1
 fi

 # 4. FREEZE FILESYSTEMS (Optional)
 for mnt in $MOUNT_POINTS; do
     if ! sudo fsfreeze -f "$mnt"; then
         logger "Error: Failed to freeze $mnt."
         # Attempt emergency thaw
         for thaw_mnt in $MOUNT_POINTS; do sudo fsfreeze -u "$thaw_mnt" 2>/dev/null; done
         # Cleanup quiesce file as backup is invalid
         rm -f "$FULL_BKP_FILE"
         exit 1
     fi
     logger "Filesystem $mnt is frozen."
 done

Post-snapshot script (/etc/google/snapshots/post.sh)

 #!/bin/bash
 # Location: /etc/google/snapshots/post.sh
 # 1. Configuration
 OSUSER="sybiq"
 DB_NAME="MY_IQ_DB"
 CONN_STR="uid=DBA;pwd=password;dbn=$DB_NAME;eng=MY_ENG"
 BKP_DIR="/var/tmp/sybaseiq_bkp"
 FULL_BKP_FILE="$BKP_DIR/FULL_VIR_DEC"
 INC_BKP_FILE="$BKP_DIR/INC_BKP"
 # 2. Discovery
 MOUNT_POINTS=$(findmnt -n -l -o TARGET -t xfs,ext4,ext3,ext2,btrfs)
 logger "SybaseIQ Full VM BYOS: Starting post-snapshot script."
 # 3. UNFREEZE FILESYSTEMS (Optional)
 for mnt in $MOUNT_POINTS; do
     sudo fsfreeze -u "$mnt"
     logger "Filesystem $mnt is unfrozen."
 done
 # 4. RESUME DATABASE (Post-Snapshot Cleanup)
 logger "SybaseIQ Full VM BYOS: Performing incremental since full backup..."
 # Logic from act_sybaseiq_post.sh to finish the decoupled sequence
 su -m $OSUSER -c "dbisql -nogui -c '$CONN_STR' \"BACKUP DATABASE INCREMENTAL SINCE FULL TO '$INC_BKP_FILE'\""
 if [ $? -ne 0 ]; then
     logger "Error: Failed post-quiesce incremental backup."
 fi
 # Remove temporary files created during quiesce
 rm -f "$FULL_BKP_FILE"*
 logger "SybaseIQ Full VM BYOS: Cleanup complete."

SAP MaxDB

Pre-snapshot script (/etc/google/snapshots/pre.sh)

 #!/bin/bash
 # Location: /etc/google/snapshots/pre.sh

 # 1. Configuration
 DBSID="ACT" # Replace with your SAP MaxDB SID
 OSUSER="sdba" # OS user for MaxDB
 MAXDB_KEY="-user BACKUPUSER,password" # Or use a DBM key: "-k MYKEY"
 AUTOLOG_TMPLT="LOG_BACKUP"

 # 2. Discovery
 # Dynamically discover all local writeable mount points for full VM consistency
 MOUNT_POINTS=$(findmnt -n -l -o TARGET -t xfs,ext4,ext3,ext2,btrfs)

 logger "MaxDB Full VM BYOS: Starting pre-snapshot script for $DBSID."

 # 3. QUIESCE DATABASE
 # Optional: Trigger a log backup first to ensure point-in-time recovery readiness
 logger "MaxDB Full VM BYOS: Triggering log backup..."
 su -m $OSUSER -c "dbmcli -d $DBSID $MAXDB_KEY -uUTL -c backup_start $AUTOLOG_TMPLT LOG"

 # Suspend logwriter to quiesce the database
 logger "MaxDB Full VM BYOS: Suspending logwriter..."
 su -m $OSUSER -c "dbmcli -d $DBSID $MAXDB_KEY -uUTL -c util_execute suspend logwriter"
 if [ $? -ne 0 ]; then
     logger "Error: Failed to suspend MaxDB logwriter."
     exit 1
 fi

 # 4. FREEZE FILESYSTEMS (Optional)
 for mnt in $MOUNT_POINTS; do
     if ! sudo fsfreeze -f "$mnt"; then
         logger "Error: Failed to freeze $mnt."
         # Attempt emergency thaw
         for thaw_mnt in $MOUNT_POINTS; do sudo fsfreeze -u "$thaw_mnt" 2>/dev/null; done
         # Resume the logwriter as the backup is no longer consistent
         su -m $OSUSER -c "dbmcli -d $DBSID $MAXDB_KEY -uUTL -c util_execute resume logwriter"
         exit 1
     fi
     logger "Filesystem $mnt is frozen."
 done

Post-snapshot script (/etc/google/snapshots/post.sh)

 #!/bin/bash
 # Location: /etc/google/snapshots/post.sh

 # 1. Configuration
 DBSID="ACT"
 OSUSER="sdba"
 MAXDB_KEY="-user BACKUPUSER,password"

 # 2. Discovery
 MOUNT_POINTS=$(findmnt -n -l -o TARGET -t xfs,ext4,ext3,ext2,btrfs)

 logger "MaxDB Full VM BYOS: Starting post-snapshot script for $DBSID."

 # 3. UNFREEZE FILESYSTEMS (Optional)
 for mnt in $MOUNT_POINTS; do
     sudo fsfreeze -u "$mnt"
     logger "Filesystem $mnt is unfrozen."
 done

 # 4. RESUME DATABASE
 logger "MaxDB Full VM BYOS: Resuming logwriter..."
 su -m $OSUSER -c "dbmcli -d $DBSID $MAXDB_KEY -uUTL -c util_execute resume logwriter"
 if [ $? -ne 0 ]; then
     logger "Error: Failed to resume MaxDB logwriter."
 else
     logger "MaxDB Full VM BYOS: Logwriter resumed successfully."
 fi

 logger "MaxDB Full VM BYOS: Cleanup complete."

PostgreSQL

Pre-snapshot script (/etc/google/snapshots/pre.sh)

 #!/bin/bash
 # 1. Configuration
 OSUSER="postgres"
 PG_HOME="/usr/lib/postgresql/16"
 DBUSER="postgres"
 PORT=5432
 DBNAME="postgres"
 FIFO="/tmp/pg_backup_fifo_$PORT"
 PID_FILE="/tmp/pg_backup_pid_$PORT"
 ACT_JOBNAME="GCE_SNAPSHOT_$(date +%Y%m%d%H%M%S)"

 # 2. Discovery
 MOUNT_POINTS=$(findmnt -n -l -o TARGET -t xfs,ext4,ext3,ext2,btrfs)
 export PATH=$PG_HOME/bin:$PATH

 logger "PostgreSQL Full VM BYOS: Starting pre-snapshot script."

 # 3. QUIESCE DATABASE (Session-Bound)
 rm -f "$FIFO"
 mkfifo "$FIFO"
 chown "$OSUSER" "$FIFO"

 su "$OSUSER" -c "psql -p $PORT -U $DBUSER -d $DBNAME -f $FIFO" > /tmp/pg_backup_output.log 2>&1 &
 echo $! > "$PID_FILE"

 psql_version=$(su "$OSUSER" -c "psql --version" | awk '{print $3}' | cut -d'.' -f1)
 if [ "$psql_version" -ge 15 ]; then
     CMD="SELECT pg_backup_start('$ACT_JOBNAME');"
 else
     CMD="SELECT pg_start_backup('$ACT_JOBNAME');"
 fi

 echo "$CMD" > "$FIFO"
 sleep 2

 # 4. FREEZE FILESYSTEMS (Optional)
 for mnt in $MOUNT_POINTS; do
     if ! sudo fsfreeze -f "$mnt"; then
         logger "Error: Failed to freeze $mnt. Cleaning up..."
         for thaw_mnt in $MOUNT_POINTS; do sudo fsfreeze -u "$thaw_mnt" 2>/dev/null; done
         echo "SELECT pg_backup_stop(); exit;" > "$FIFO"
         rm -f "$FIFO" "$PID_FILE"
         exit 1
     fi
     logger "Filesystem $mnt is frozen."
 done

Post-snapshot script (/etc/google/snapshots/post.sh)

 #!/bin/bash
 PORT=5432
 FIFO="/tmp/pg_backup_fifo_$PORT"
 PID_FILE="/tmp/pg_backup_pid_$PORT"

 MOUNT_POINTS=$(findmnt -n -l -o TARGET -t xfs,ext4,ext3,ext2,btrfs)
 logger "PostgreSQL Full VM BYOS: Starting post-snapshot script."

 # 1. UNFREEZE FILESYSTEMS (Optional)
 for mnt in $MOUNT_POINTS; do
     sudo fsfreeze -u "$mnt"
     logger "Filesystem $mnt is unfrozen."
 done

 # 2. RESUME DATABASE (Close Session)
 if [ -p "$FIFO" ]; then
     logger "PostgreSQL Full VM BYOS: Closing backup session via FIFO..."
     echo "SELECT pg_backup_stop(); SELECT pg_stop_backup(); exit;" > "$FIFO"
     if [ -f "$PID_FILE" ]; then
         wait "$(cat "$PID_FILE")" 2>/dev/null
     fi
     rm -f "$FIFO" "$PID_FILE"
     logger "PostgreSQL Full VM BYOS: Database session closed."
 else
     logger "Error: FIFO not found. Session may have terminated unexpectedly."
 fi
 logger "PostgreSQL Full VM BYOS: Cleanup complete."

MySQL

Pre-snapshot script (/etc/google/snapshots/pre.sh)

 #!/bin/bash
 # 1. Configuration
 DBUSER="root"
 # Recommended: Use Secret Manager Integration instead of plaintext passwords
 DBPASSWORD="your_password"
 PORTNO=3306
 SOCKET_FILE="/var/run/mysqld/mysqld.sock"
 MYSQL_PATH="/usr/bin/mysql"
 FIFO="/tmp/mysql_backup_fifo_$PORTNO"
 PID_FILE="/tmp/mysql_backup_pid_$PORTNO"

 # 2. Discovery
 MOUNT_POINTS=$(findmnt -n -l -o TARGET -t xfs,ext4,ext3,ext2,btrfs)

 logger "MySQL Full VM BYOS: Starting pre-snapshot script."

 # 3. QUIESCE DATABASE (Session-Bound Lock)
 rm -f "$FIFO"
 mkfifo "$FIFO"

 $MYSQL_PATH -u"$DBUSER" -p"$DBPASSWORD" -S"$SOCKET_FILE" -P"$PORTNO" < "$FIFO" > /tmp/mysql_backup_output.log 2>&1 &
 echo $! > "$PID_FILE"

 logger "MySQL Full VM BYOS: Issuing FLUSH TABLES WITH READ LOCK..."
 echo "FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400);" > "$FIFO"
 sleep 2

 # 4. FREEZE FILESYSTEMS (Optional)
 for mnt in $MOUNT_POINTS; do
     if ! sudo fsfreeze -f "$mnt"; then
         logger "Error: Failed to freeze $mnt. Cleaning up..."
         for thaw_mnt in $MOUNT_POINTS; do sudo fsfreeze -u "$thaw_mnt" 2>/dev/null; done
         kill $(cat "$PID_FILE") 2>/dev/null
         rm -f "$FIFO" "$PID_FILE"
         exit 1
     fi
     logger "Filesystem $mnt is frozen."
 done

Post-snapshot script (/etc/google/snapshots/post.sh)

 #!/bin/bash
 PORTNO=3306
 FIFO="/tmp/mysql_backup_fifo_$PORTNO"
 PID_FILE="/tmp/mysql_backup_pid_$PORTNO"

 MOUNT_POINTS=$(findmnt -n -l -o TARGET -t xfs,ext4,ext3,ext2,btrfs)
 logger "MySQL Full VM BYOS: Starting post-snapshot script."

 # 1. UNFREEZE FILESYSTEMS (Optional)
 for mnt in $MOUNT_POINTS; do
     sudo fsfreeze -u "$mnt"
     logger "Filesystem $mnt is unfrozen."
 done

 # 2. RESUME DATABASE (Unlock)
 if [ -f "$PID_FILE" ]; then
     logger "MySQL Full VM BYOS: Releasing locks..."
     kill $(cat "$PID_FILE") 2>/dev/null
     rm -f "$FIFO" "$PID_FILE"
     logger "MySQL Full VM BYOS: Locks released and session closed."
 else
     logger "Error: PID file not found. Lock may have been lost prematurely."
 fi
 logger "MySQL Full VM BYOS: Cleanup complete."

MariaDB

Pre-snapshot script (/etc/google/snapshots/pre.sh)

 #!/bin/bash
 # Location: /etc/google/snapshots/pre.sh

 # 1. Configuration
 DBUSER="root"
 DBPASSWORD="your_password" # Recommended: use a secure method to retrieve this
 PORTNO=3306
 SOCKET_FILE="/var/run/mysqld/mysqld.sock"
 MARIADB_PATH="/usr/bin/mariadb" # or /usr/bin/mysql
 SRV_TYPE="Master" # Set to "Slave" for standby nodes
 FIFO="/tmp/mariadb_backup_fifo_$PORTNO"
 PID_FILE="/tmp/mariadb_backup_pid_$PORTNO"
 STARTPOS_FILE="/tmp/STARTPOS_FILE_$PORTNO.txt"

 # 2. Discovery
 # Dynamically discover all local writeable mount points for full VM consistency
 MOUNT_POINTS=$(findmnt -n -l -o TARGET -t xfs,ext4,ext3,ext2,btrfs)

 logger "MariaDB Full VM BYOS: Starting pre-snapshot script."

 # 3. QUIESCE DATABASE (Session-Bound)
 # Create a FIFO to hold the session open
 rm -f "$FIFO"
 mkfifo "$FIFO"

 # Start a background mariadb session that reads from the FIFO
 # This keeps the session (and locks) active until the process is killed
 $MARIADB_PATH -u"$DBUSER" -p"$DBPASSWORD" -S"$SOCKET_FILE" -P"$PORTNO" < "$FIFO" > /tmp/mariadb_backup_output.log 2>&1 &
 echo $! > "$PID_FILE"

 # Prepare quiesce query (Logic from act_mariadb_pre.sh)
 if [ "$SRV_TYPE" != "Slave" ]; then
     # Primary node: lock all tables
     logger "MariaDB Full VM BYOS: Issuing FLUSH TABLES WITH READ LOCK..."
     echo "FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400);" > "$FIFO"

     # Capture master status for PITR/replication info
     $MARIADB_PATH -u"$DBUSER" -p"$DBPASSWORD" -S"$SOCKET_FILE" -P"$PORTNO" -e "SHOW MASTER STATUS;" > "$STARTPOS_FILE"
 else
     # Replica node: stop the slave thread
     logger "MariaDB Full VM BYOS: Issuing STOP SLAVE..."
     echo "STOP SLAVE; SELECT SLEEP(86400);" > "$FIFO"

     # Capture slave status
     $MARIADB_PATH -u"$DBUSER" -p"$DBPASSWORD" -S"$SOCKET_FILE" -P"$PORTNO" -e "SHOW SLAVE STATUS \G;" > "$STARTPOS_FILE"
 fi

 # Wait a brief moment to ensure the command is processed
 sleep 2
 # 4. FREEZE FILESYSTEMS (Optional)
 for mnt in $MOUNT_POINTS; do
     if ! sudo fsfreeze -f "$mnt"; then
         logger "Error: Failed to freeze $mnt. Cleaning up..."
         # Attempt emergency thaw
         for thaw_mnt in $MOUNT_POINTS; do sudo fsfreeze -u "$thaw_mnt" 2>/dev/null; done
         # Kill the background session to release locks
         kill $(cat "$PID_FILE") 2>/dev/null
         rm -f "$FIFO" "$PID_FILE"
         exit 1
     fi
     logger "Filesystem $mnt is frozen."
 done

Post-snapshot script (/etc/google/snapshots/post.sh)

 #!/bin/bash
 # Location: /etc/google/snapshots/post.sh
 # 1. Configuration
 PORTNO=3306
 SRV_TYPE="Master"
 DBUSER="root"
 DBPASSWORD="your_password"
 SOCKET_FILE="/var/run/mysqld/mysqld.sock"
 MARIADB_PATH="/usr/bin/mariadb"
 FIFO="/tmp/mariadb_backup_fifo_$PORTNO"
 PID_FILE="/tmp/mariadb_backup_pid_$PORTNO"
 # 2. Discovery
 MOUNT_POINTS=$(findmnt -n -l -o TARGET -t xfs,ext4,ext3,ext2,btrfs)
 logger "MariaDB Full VM BYOS: Starting post-snapshot script."
 # 3. UNFREEZE FILESYSTEMS (Optional)
 for mnt in $MOUNT_POINTS; do
     sudo fsfreeze -u "$mnt"
     logger "Filesystem $mnt is unfrozen."
 done
 # 4. RESUME DATABASE
 if [ -f "$PID_FILE" ]; then
     logger "MariaDB Full VM BYOS: Resuming database operations..."
     # Kill the background sleep process to automatically release session-bound locks
     kill $(cat "$PID_FILE") 2>/dev/null
     # For slave nodes, explicitly ensure replication is started
     if [ "$SRV_TYPE" = "Slave" ]; then
         $MARIADB_PATH -u"$DBUSER" -p"$DBPASSWORD" -S"$SOCKET_FILE" -P"$PORTNO" -e "START SLAVE;"
     fi
     rm -f "$FIFO" "$PID_FILE"
     logger "MariaDB Full VM BYOS: Cleanup complete."
 else
     logger "Error: PID file not found. Database state might be inconsistent."
 fi

Deploy your guest-flush scripts

After preparing your pre.sh and post.sh scripts for your database, deploy them to the Linux VM instance:

1. Connect to the Linux VM instance running your self-managed database using SSH.

2. Create the /etc/google/snapshots/ directory if it doesn't already exist:

   sudo mkdir -p /etc/google/snapshots
   

3. Save your custom pre.sh and post.sh scripts to the /etc/google/snapshots/ directory on the VM instance.

4. Ensure that the scripts have execute permissions:

   sudo chmod 755 /etc/google/snapshots/pre.sh /etc/google/snapshots/post.sh
   

Enable the guest agent for snapshots

To allow Backup and DR Service to run pre-snapshot and post-snapshot scripts, enable snapshot integration in the Compute Engine guest environment on your Linux VM instance:

1. Connect to the Linux VM instance running your self-managed database using SSH (if not already connected).

2. Open the guest environment configuration file in a text editor:

   sudo nano /etc/default/instance_configs.cfg
   

3. Add or modify the following settings under the [Snapshots] section to enable snapshots:

   [Snapshots]
   enabled = true
   timeout_in_seconds = 120
   

   Note: The timeout_in_seconds parameter specifies the maximum time the guest agent waits for the pre.sh and post.sh scripts to finish. Adjust this value to accommodate the duration of your database quiesce or file system freeze operations.

4. Restart the guest agent on the VM instance to apply the changes:

   sudo systemctl restart google-guest-agent
   

Configure the backup plan

Now that the VM instance is configured with the guest-flush scripts and the guest agent is enabled for snapshots, configure a backup plan in the Google Cloud console:

1. In the Google Cloud console, go to the Backup and DR Service page.

   Go to Backup and DR

2. From the navigation menu, select Backup Plans.

3. Click Create Backup Plan, or select an existing backup plan and click Edit.

4. Under the Instance Backup configuration steps, locate the consistency options.

5. Select the Enable Application Consistency option.

6. Save and apply the backup plan to your VM instance. On schedule, the backup plan triggers the guest agent to run your pre.sh script, take the snapshot, and run the post.sh script.

Configure the Persistent Disk backup for database point-in-time recovery

For self-managed databases such as DB2 and SAP HANA, you can configure standalone Persistent Disk backups to capture database archive logs, enabling point-in-time recovery. If you don't require point-in-time recovery, you can skip to Clean up.

Follow best practices for point-in-time recovery log backups

• Frequency: Configure the backup plan schedule for this dedicated log disk to be equal to your point-in-time recovery log granularity, for example, every 15 minutes or hourly.

• Cost optimization: Storing frequent snapshots can increase storage costs. To minimize overhead, configure the minimum retention period and the corresponding immutability vault period to the minimum allowable time that still satisfies your point-in-time recovery window.

Recover a self-managed database to a specific point in time

If you have configured regular VM backups alongside dedicated Persistent Disk log backups, follow this procedure to restore your database to a specific point in time. If you didn't configure dedicated log backups or don't require point-in-time recovery, see Restore a Compute Engine instance from a backup vault to restore your VM directly, and skip to Clean up post-restore.

Complete the recovery workflow

1. Restore the base VM: select the instance backup, meaning the machine image, from the backup vault that precedes your target recovery time. Restore it following standard Compute Engine restore procedures. For instructions, see Restore a Compute Engine instance from a backup vault.

   If you didn't configure dedicated log backups or don't require point-in-time recovery, skip to step 4.

2. Attach log disks: identify and restore the relevant log disk snapshots captured up to your target recovery time. Attach these newly created disks to the restored instance.

   Note: You can achieve this using the Google Cloud console or using gcloud CLI during the roll-forward procedure. You may need to mount several log backup disks to ensure all essential archived logs are available.

3. Roll forward the database: connect to the restored instance using SSH and execute your database-specific roll-forward command, such as ROLLFORWARD DATABASE in DB2, using the logs located on the newly attached snapshot volumes.

4. Clean up post-restore: perform any database-specific steps required to bring the application online.

   Important: Ensure database configurations are updated if the name of the restored VM and attached disks differs from the source environment.

Clean up

To avoid incurring ongoing backup charges or to remove application-consistent backup configurations from your VM instance, perform the following steps:

1. Unassign the backup plan from your instance: In the Google Cloud console, go to the Backup and DR Service page, locate your VM instance under Protected Resources, and unassign or delete the backup plan association.

2. Remove guest-flush scripts: Connect to your Linux VM instance using SSH and remove the custom guest-flush scripts:

   sudo rm -f /etc/google/snapshots/pre.sh /etc/google/snapshots/post.sh
   

3. Disable snapshot integration in the guest agent: Open /etc/default/instance_configs.cfg on your VM instance, set enabled = false under [Snapshots], and restart the guest agent:

   sudo systemctl restart google-guest-agent
   

What's next

• Create and manage backup plans.
• Monitor backup and recovery jobs.