Пример скриптов для сохранения всех баз PostgreSQL
На Windows
1 2 3 4 5 6 7 8 9 10 11 |
New-Item -Path C:\pgbackup\$((Get-Date).ToString('yyyyMMdd')).log foreach ($s in psql -U postgres --list | select -Skip 3 | Select -SkipLast 7 ) { $database = $s.split(" ")[1] $database >> C:\pgbackup\$((Get-Date).ToString('yyyyMMdd')).log Measure-Command -Expression { pg_dump -U postgres -F c -b -f C:\pgbackup\$database$((Get-Date).ToString('yyyyMMdd')).bak $database} >> C:\pgbackup\$((Get-Date).ToString('yyyyMMdd')).log } Send-MailMessage -To admin@domain.com -Subject PGBACKUP -Attachments C:\pgbackup\$((Get-Date).ToString('yyyyMMdd')).log -from postgres@domain.com -SmtpServer smtp.domain.com if (Test-Path C:\pgbackup\$database$((Get-Date).ToString('yyyyMMdd')).bak) { Get-ChildItem -Path C:\pgbackup\ -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt (Get-Date).AddDays(-10) } | Remove-Item -Force } |
Пример скрипта для сохранения всех PostgreSQL баз 1С на Linux
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
#!/bin/bash apath="/mnt/files/backup/" alog=$apath$(date +%Y%m%d)"log.txt" echo "*** $(date +%Y%m%d) ***" >> $alog df -ha /pg/data/ >> $alog psql -U postgres -c "select * from pg_database" | head -n -2 | tail -n +3 | sed '/^ template/d' | sed '/^ postgres/d' | while read -r str ; do IFS=' ' read -ra ADDR <<< "$str" echo -e "\n${ADDR[0]}" >> $alog /usr/bin/time -p -f %E -o $alog -a pg_dump -U postgres -F c -b -f $apath$(date +%Y%m%d)${ADDR[0]}.bak ${ADDR[0]} 2>>$apath$(date +%Y%m%d)err.txt du -sh $apath$(date +%Y%m%d)${ADDR[0]}.bak >> $alog done echo "*** *** ***" >> $alog if [ ! -s $apath$(date +%Y%m%d)err.txt ] then echo -e "\n*** DELETED FILES ***" >> $alog find $apath -type f -mtime +5 -delete -print >> $alog else echo -e "\n***BACKUP ERRORS ***" >> $alog cat $apath$(date +%Y%m%d)err.txt >> $alog fi echo "*** *** ***" >> $alog echo -e "\n*** BACKUP FOLDER ***" >> $alog du -sh /mnt/files/backup/ >> $alog echo "*** *** ***" >> $alog echo -e "\n*** BACKUP DISK ***" >> $alog df -ha /mnt/files/backup/ >> $alog echo "*** *** ***" >> $alog echo -e "\n*** VACUUM ***" >> $alog /usr/bin/time -p -f %E -o $alog -a vacuumdb -U postgres -a -z -F -j 4 >> $alog echo "*** *** ***" >> $alog cat $alog | mail -s "PostgreSQL backup $(date +%Y%m%d)" -r 'PostgreSQL Backup <backup@domain.com>' admin@domain.com |
Пример скрипта для восстановления PostgreSQL баз 1С на Windows
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
do {$base = read-host "Database name" $day = read-host "Day to restore as 20201231" if ((Test-Path C:\pgbackup\$base$day.bak) -eq $false){ echo "Check the database name and date to restore. C:\pgbackup\$base$day.bak is not found" } } until (Test-Path C:\pgbackup\$base$day.bak) do {$newbase = read-host "New database name" foreach ($s in psql -U postgres --list | select -Skip 3 | Select -SkipLast 7 ){ $database = $s.split(" ")[1] if ($newbase -eq $database) { $exist = $true $enter = read-host "database $newbase already exists, rewrite?" } } } until (($exist -ne $true) -or (($exist -eq $true) -and (($enter -eq "yes") -or ($enter -eq "y")))) if ((($enter -eq "yes") -or ($enter -eq "y"))) { psql -U postgres -c "UPDATE pg_database SET datallowconn = 'false' WHERE datname = '$base';" psql -U postgres -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '$base' AND pid <> pg_backend_pid();" > $null echo "Droping $base and creating $newbase..." dropdb -U postgres $base createdb -U postgres $newbase echo "Restoring C:\pgbackup\$base$day.bak to $newbase..." $ts = Measure-Command -Expression {pg_restore -j 12 -d $newbase -U postgres C:\pgbackup\$base$day.bak} } if ($exist -ne $true){ echo "Creating $newbase..." createdb -U postgres $newbase echo "Restoring C:\pgbackup\$base$day.bak to $newbase..." $ts = Measure-Command -Expression {pg_restore -j 12 -d $newbase -U postgres C:\pgbackup\$base$day.bak} } echo "Lapsed time is $ts" |
Пример скрипта для восстановления PostgreSQL баз 1С на Linux
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
#!/bin/bash apath="/mnt/files/backup/" if [ $# -eq 0 ] then echo "No archive database file supplied" while [ ! -f "$apath$day$base.bak" ] do read -p "Archive database name " -r base read -p "Archive database name date as 20201231 " -r day if [ ! -f "$apath$day$base.bak" ] ; then echo "Check backup file"; fi done fi while [ "$exist" != "n" ] && [ "$over" != "y" ] do read -p "New database " -r newbase while read -r str do IFS=' ' read -ra ADDR <<< "$str" if [[ " ${ADDR[0]} " == " $newbase " ]] then exist=y break else exist=n fi done < <(psql -U postgres -c "select * from pg_database" | head -n -2 | tail -n +3 | sed '/^ template/d' | sed '/^ postgres/d') if [ "$exist" == "y" ] then read -p "database $newbase already exists, rewrite? y/n" -r over fi done if [ "$over" == "y" ] then #psql -U postgres -c "ALTER DATABASE $newbase CONNECTION LIMIT 0;" #psql -U postgres -c "ALTER DATABASE $newbase WITH ALLOW_CONNECTIONS false;" psql -U postgres -c "UPDATE pg_database SET datallowconn = 'false' WHERE datname = '$newbase';" psql -U postgres -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '$newbase' AND pid <> pg_backend_pid();" dropdb -U postgres $newbase fi createdb -U postgres $newbase if [ $# -eq 0 ] then pg_restore -j 4 -d $newbase -U postgres $apath$day$base.bak else if [ ! -f "$1" ] then echo "Check backup file" else pg_restore -j 4 -d $newbase -U postgres $1 fi fi |