^p^‎ > ‎

Backup postgresql script/skrypt shell

opublikowane: 19 lis 2013, 03:58 przez Piotr Trochymiak   [ zaktualizowane 18 gru 2018, 04:10 ]
Poniżej bardzo fajny kod ze strony, która może zniknąć: wiec nie będę ściemniał że to ja go pisałem :)
#!/bin/sh ############################## #Thanks goes to ANAS for this script ############################## #Substitute your postgresql root username in the below given line PGUSER=postgres #Substitute your postgresql root password in the below given line #PGPASSWORD=mypostgre PGPASSWORD=ppgt23 export PGUSER PGPASSWORD tdate=`date +%d-%b-%Y` if [ $# -lt 1 ] # Check if there is atleast one argument [i.e the database whose dump is to be taken] #First argument is mandatory - Databse name #Second argument is optional - Destination path to save dump then echo "Bad Arguments" echo "-----------------------------------" echo "USAGE : pg_dmp.sh [outputfile]" echo "-----------------------------------" exit 1 else #if one or more arguments were provided if [ $# -ge 2 ] #if arguments provided is greater than or equal to 2 then #Comment out the below given file exist check and it's associated messages # if you want to run the pg_dmpsh script to run silently. i.e from a cron or at job # without any user interaction. Then the output dump file will be rewritten if a file # already exists. if [ -f $2 ] # if destination file ie argument 2 is already existing then #Show confirmation message to confirm whether replace file with new one or exit dialog --title "Confirm File Replace" --backtitle "pg_dmp.sh"\ --yesno "\nFile already exist, Do you want to replace it with '$2' file" 7 90 sel=$? case $sel in #if Yes then take dump and replace the existing file with new dump 0) pg_dump $1 -f $2 -i -x -O -R;; #if No then exit 1) exit 1 ;; #if escape then exit 255) exit 1;; esac else #if destination file does not exist then create and save the dump in destination path pg_dump $1 -f $2 -i -x -O -R fi else if [ $# -eq 1 ] #if arguments provided is equal to 1 then if [ -d $HOME/pg_backup_$1 ] #if folder name pg_backup_'databsename' exist in the current users home directory then if [ -f $HOME/pg_backup_$1/$1_$tdate ] #if destination file name exist in pg_backup_'databasename' folder in current users home dierectory then #Show confirmation message for replacing the file with new dump dialog --title "Confirm File Replace" --backtitle "pg_dump.sh"\ --yesno "\nFile already exist, Do you want to replace it with '$HOME/pg_backup_$1/$1_$tdate' file" 7 90 sel=$? case $sel in #if Yes then replace the file with new dump file 0) pg_dump $1 -f $HOME/pg_backup_$1/$1_$tdate -i -x -O -R;; #if No then exit 1) exit 1 ;; #if escape thenexit 255) exit 1;; esac else #if destination file does not exist then create and save the dump pg_dump $1 -f $HOME/pg_backup_$1/$1_$tdate -i -x -O -R fi else #if folder pg_backup_'databsename' does not exist in the current users home dierectory then #Create a new folder mkdir $HOME/pg_backup_$1 #then create dump and save it pg_dump $1 -f $HOME/pg_backup_$1/$1_$tdate -i -x -O -R #if databse to take does not exist then Delete the folder created if [ $? -ne 0 ] then rmdir $HOME/pg_backup_$1 fi fi fi fi if [ $# -gt 2 ] #if arguments passed where greater than 2 then show message then echo "Extra Arguments ignored" fi fi #reset PGUSER and PGPASSWORD PGUSER="" PGPASSWORD="" export PGUSER PGPASSWORD #End


Dodatkowo w cronie dopisałem takie ze backup ma wykonywać się w soboty o  godzinie 12:12.
12 12 * * 6 /home/backup/pgBack.sh nazwabazy /home/backup/$(date +%Y-%m-%d)nazwabazy.psql

Wiem, można to później tarować, przebierać jak w ulęgałkach i takie tam, ale to .......... ..
-= Errata 
Tym razem moja produkcja 

#!/bin/sh

PGUSER=postgres
PGPASSWORD=

export PGUSER PGPASSWORD

LOGFILE=/opt/backup/scripts/backup.log
PG_DUMP="/usr/pgsql-9.1/bin/pg_dump" # tu podajemy pelena sciezke do

PG_DUMP_OPTS="-h localhost -p 5432 -U ${PGUSER} -w ${PASSWORD} -F c --column-inserts"

#TIMESTAMP=`date +%Y%m%d%H%M%S`
TIMESTAMP=`date +%Y%m%d%H%M`

# 1 zmienna z linni komend
BACKUP_ROOT="$1"
# 2 zmienna z linni komend
DB_NAME="$2"

# wjazd od katalogu
cd "${BACKUP_ROOT}"
DB_BACKUP="${DB_NAME}-${TIMESTAMP}.backup"
# budujemy plik
DB_ARCHIVE="${DB_BACKUP}.tar.gz"
${PG_DUMP} ${PG_DUMP_OPTS} -f "${DB_BACKUP}" "${DB_NAME}"
# laczymy sie z baza

# jesli brak np hasla - koniec
while test $? != 0
do
#echo "Brak Hasla"
echo " LIPA -" `date "+%F_%H:%M:%S:"` "Backup bazy '${DB_NAME}' niepowiodla sie!!" >> $LOGFILE
rm "${DB_BACKUP}"
exit 1;
done

if [ ! -r "${DB_BACKUP}" ];
then
logger "File '${DB_BACKUP}' not found"
else
tar -czf "${DB_ARCHIVE}" "${DB_BACKUP}"
         echo "GIT -" `date "+%F_%H:%M:%S:"` "Backup bazy '${DB_NAME}' powiodl sie." >> $LOGFILE
rm "${DB_BACKUP}"
fi


#kasujemy starsze pliki niz 7 dni

find /sciezka/backup/ -type f -name "nazwa_bakupu*" -atime +7 -print0 | xargs -0 -r rm


cronowe ustawki
12 12 * * 5 /root/bk-p.sh /opt/backup/ nazwabazy


Kilka przydatnych komend przy pg 
#vmstat 1/60

#free -m
#ps -e -o pid,ppid,wchan:60,cmd | grep post

ps -el | grep post



#time sh -c "dd if=/dev/zero of=bigfile bs=8k count=250000 && sync"
#time dd if=bigfile of=/dev/null bs=8k

$vacuumdb -h 127.0.0.1 -a

# pmap -x nr_procesu "sprawdza ile proces pamieci pochlania"



Bardzo ciekawy link - 01


Bardzo ciekawy linka - 02
Bardzo ciekawy linka  - 03

https://linuxpolska.pl/jak-zautomatyzowac-prace-administratora-postgresql/
https://github.com/linuxpolska/psql-automatyzacja
https://www.youtube.com/watch?v=zzwmOToWKOY
 Jeśli coś tu się Ci przydało nie krępuj się wyślij 1 zł jako --> Donacje! - przyda się :) 



Comments