FreeBSD qemu-guest-agent 8.0.2 freeze before shapshot. Proxmox snaphsot of pgsql database instance VM.

Задача - коректно зберігати базу даних при створені знімку віртуальної машини в Proxmox.

Що є:

Proxmox VE, VM з сервером FreeBSD 13, база даних postgresql.

Виконання:

Встановлюю qemu-guest-agent.

pkg version -x qemu-guest-agent
qemu-guest-agent-8.0.2 

Налаштовую qemu-guest-agent:

/etc/rc.d

qemu_guest_agent_enable="YES"
qemu_guest_agent_flags="-d -v --fsfreeze-hook" 

Скрипт для оброки freeze-hook

scripts/qemu-guest-agent/fsfreeze-hook

#!/bin/sh

# This script is executed when a guest agent receives fsfreeze-freeze and
# fsfreeze-thaw command, if it is specified in --fsfreeze-hook (-F)
# option of qemu-ga or placed in default path (/etc/qemu/fsfreeze-hook).
# When the agent receives fsfreeze-freeze request, this script is issued with
# "freeze" argument before the filesystem is frozen. And for fsfreeze-thaw
# request, it is issued with "thaw" argument after filesystem is thawed.

LOGFILE=/var/log/qga-fsfreeze-hook.log
FSFREEZE_D=$(dirname -- "$0")/fsfreeze-hook.d

# Check whether file $1 is a backup or rpm-generated file and should be ignored
is_ignored_file() {
    case "$1" in
        *~ | *.bak | *.orig | *.rpmnew | *.rpmorig | *.rpmsave | *.sample | *.dpkg-old | *.dpkg-new | *.dpkg-tmp | *.dpkg-dist | *.dpkg-bak | *.dpkg-backup | *.dpkg-remove)
            return 0 ;;
    esac
    return 1
}

# Iterate executables in directory "fsfreeze-hook.d" with the specified args
[ ! -d "$FSFREEZE_D" ] && exit 0
for file in "$FSFREEZE_D"/* ; do
    is_ignored_file "$file" && continue
    [ -x "$file" ] || continue
    printf "$(date): execute $file $@\n" >>$LOGFILE
    "$file" "$@" >>$LOGFILE 2>&1
    STATUS=$?
    printf "$(date): $file finished with status=$STATUS\n" >>$LOGFILE
done

exit 0

/usr/local/etc/qemu/fsfreeze-hook.d/pg-flush.sh

#!/bin/sh

# Flush PGSQL tables to the disk before the filesystem is frozen.
# At the same time, this keeps a read lock in order to avoid write accesses
# from the other clients until the filesystem is thawed.

case "$1" in
    freeze)
        logger -t pg-flush "freeze pg before stop service"
        service postgresql stop
        sync
        logger -t pg-flush "freeze pg after sync"
        ;;

    thaw)
        logger -t pg-flush "thaw pg starting service"
        service postgresql start
        logger -t pg-flush "thaw pg service `service postgresql status`"
        ;;

    *)
        exit 1
        ;;
esac 

Proxmox snapshot VM

tail -f /var/log/messages

Aug  1 17:39:24 pleroma pg-flush[40320]: freeze pg before stop service
Aug  1 17:39:24 pleroma postgres[29734]: [10-1] 2023-08-01 17:39:24.116 [29734] FATAL:  terminating connection due to administrator command
Aug  1 17:39:24 pleroma postgres[29739]: [10-1] 2023-08-01 17:39:24.116 [29739] FATAL:  terminating connection due to administrator command
Aug  1 17:39:24 pleroma postgres[29736]: [10-1] 2023-08-01 17:39:24.116 [29736] FATAL:  terminating connection due to administrator command
Aug  1 17:39:24 pleroma postgres[29733]: [10-1] 2023-08-01 17:39:24.116 [29733] FATAL:  terminating connection due to administrator command
Aug  1 17:39:24 pleroma postgres[29738]: [10-1] 2023-08-01 17:39:24.116 [29738] FATAL:  terminating connection due to administrator command
Aug  1 17:39:24 pleroma postgres[29730]: [10-1] 2023-08-01 17:39:24.116 [29730] FATAL:  terminating connection due to administrator command
Aug  1 17:39:24 pleroma postgres[29735]: [10-1] 2023-08-01 17:39:24.116 [29735] FATAL:  terminating connection due to administrator command
Aug  1 17:39:24 pleroma postgres[29737]: [10-1] 2023-08-01 17:39:24.116 [29737] FATAL:  terminating connection due to administrator command
Aug  1 17:39:24 pleroma postgres[29731]: [10-1] 2023-08-01 17:39:24.116 [29731] FATAL:  terminating connection due to administrator command
Aug  1 17:39:24 pleroma postgres[16588]: [10-1] 2023-08-01 17:39:24.116 [16588] FATAL:  terminating connection due to administrator command
Aug  1 17:39:24 pleroma postgres[37548]: [10-1] 2023-08-01 17:39:24.116 [37548] FATAL:  terminating connection due to administrator command
Aug  1 17:39:24 pleroma postgres[47096]: [10-1] 2023-08-01 17:39:24.116 [47096] FATAL:  terminating connection due to administrator command
Aug  1 17:39:24 pleroma postgres[29742]: [10-1] 2023-08-01 17:39:24.116 [29742] FATAL:  terminating connection due to administrator command
Aug  1 17:39:24 pleroma postgres[47073]: [10-1] 2023-08-01 17:39:24.116 [47073] FATAL:  terminating connection due to administrator command
Aug  1 17:39:24 pleroma postgres[47076]: [10-1] 2023-08-01 17:39:24.116 [47076] FATAL:  terminating connection due to administrator command
Aug  1 17:39:24 pleroma pg-flush[40334]: freeze pg after sync
Aug  1 17:39:24 pleroma pg-flush[40343]: thaw pg starting service
Aug  1 17:39:24 pleroma pg-flush[40365]: thaw pg service pg_ctl: server is running (PID: 41234) /usr/local/bin/postgres "-D" "/var/db/postgres/data12"

Нотатки: ZFS for postgresql database, Restore pleroma database, ZFS Snapshot for postgresql db

FreeBSD, Postgress SQL Database v14.
Відновлення бази даних Pleroma на сервері з резервної копії.

pkg install postgresql14-server
pkg install postgresql14-contrib
zfs create -o mountpoint=none zroot/var/db
zfs create -o mountpoint=/var/db/postgres
zroot/var/db/postgres
zfs list
zfs zfs set atime=off zroot/var/db/postgres
zfs set atime=off zroot/var/db/postgres
zfs set relatime=on zroot/var/db/postgres
zfs set recordsize=8k zroot/var/db/postgres
chown -R postgres:postrgres /var/db/postgres
chmod 750 /var/db/postgres
service postgresql initdb
pkg install sudo

restore_db.sh

#!/usr/bin/env sh
DBSAVE=.
#DBFILE=pleroma_2021-11-09-0000.sql.gz
sudo -u postgres psql < pleroma_init.sql
cd ${DBSAVE}
DBFILE=$( ls -1 *.sql.gz | tail -1 )
echo ${DBFILE}
zcat ${DBSAVE}/${DBFILE} | sudo -u postgres psql -d pleroma

pleroma_init.sql:

REVOKE CONNECT ON DATABASE  pleroma FROM public;
SELECT pg_terminate_backend(pg_stat_activity.pid)
 FROM pg_stat_activity
 WHERE pg_stat_activity.datname = 'pleroma';
CREATE USER pleroma WITH ENCRYPTED PASSWORD '.......................................';
DROP DATABASE pleroma;
CREATE DATABASE pleroma OWNER pleroma;
\c pleroma;
--Extensions made by ecto.migrate that need superuser access
CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
GRANT CONNECT ON DATABASE pleroma TO public;

zfs-snapshot-pgsql.sh

#!/usr/bin/env sh
DATASQL=data14
DATABASE=/var/db/postgres/${DATASQL}
DBDATASET=zroot/var/db/postgres
DBDATASET_CLONE=zroot/var/db/postgres-clonedb
DATABASE_CLONE=/var/db/postgres-clonedb
SHAREDSQL=/usr/local/share/postgresql
zfs list -t snapshot
zfs destroy ${DBDATASET}@snap1
touch ${DATABASE}/backup_in_progress
#START BACKUP SQL by SQL SNAPSHOT
sql="SELECT pg_start_backup('snapshot', true);"
sudo -u postgres psql -c "$sql"
#Fix state of DATABSE DIRECTORY to ZFS snapshot
zfs snapshot ${DBDATASET}@snap1
sql="SELECT pg_stop_backup();"
sudo -u postgres psql -c "$sql"
rm ${DATABASE}/backup_in_progress
#STOP BACKUP SQL by SQL SNAPSHOT
zfs list -t snapshot 
#CLONE ZFS SNAPSHOT to new SQL DATABSE DIRECTORY
zfs clone -o mountpoint=${DATABASE_CLONE} ${DBDATASET}@snap1 ${DBDATASET_CLONE}
#Backup by file archive
tar -cf ~/backup.tar ${DATABASE_CLONE}
#Backup by pg_dump to SQL by start tmp instance of pgsql on port 5433
rm ${DATABASE_CLONE}/${DATASQL}/postmaster.*
cp ${SHAREDSQL}/postgresql.conf.sample ${DATABASE_CLONE}/${DATASQL}/postgresql.conf
sudo -u postgres pg_ctl -D ${DATABASE_CLONE}/${DATASQL} -o "-p 5433" start
umask 077
sudo -u postgres pg_dump pleroma -p 5433 --clean --create | gzip >  pleroma_`date +%F-%H%M`.sql.gz
sudo -u postgres pg_ctl -D ${DATABASE_CLONE}/${DATASQL} stop -m immediate
umount  ${DATABASE_CLONE}
zfs destroy ${DBDATASET_CLONE}

 

 Ref.: 

• PG Phriday: Postgres on ZFS | Ways ZFS Can Make Postgres an Even Better Database