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.:
