Skip to content

Instantly share code, notes, and snippets.

@aeciojr
Created January 26, 2016 18:09
Show Gist options
  • Save aeciojr/c1181d79fe705d0cdce4 to your computer and use it in GitHub Desktop.
Save aeciojr/c1181d79fe705d0cdce4 to your computer and use it in GitHub Desktop.
#!/bin/sh
#
# AdministradorBackupPG.sh
# Autor : Aecio Paes Barreto <aecio.junior@centralit.com.br>
# Descricao : Backup PostgreSQL.
# Observacao : Nenhuma.
#
# Versao 1 : Criado o script, 15 de outubro de 2015.
#
# Versao 2 : <aecio.junior@centralit.com.br>, 27 de outubro 2015.
# Funcoes alteradas para exeucao com usuario postgres.
# Permitindo execucao do script com o superuser/root;
#
##################################################################
################ Variaveis de Configuracao ######################
##################################################################
DirBase=/backup
DirPostgreSQL=/var/lib/pgsql
PGDataBase=citsmart
Versao='1.1'
##################################################################
################ Variaveis do Script ######################
##################################################################
DirDumps=$DirBase/dumps
DirLogs=$DirBase/logs
DirTmp=$DirBase/tmp
BaseName=$( basename $0 | sed 's/.sh//g' )
ArquivoLock=$DirTmp/$BaseName.lck
BinPGDump=/usr/pgsql-9.3/bin/pg_dump
BinPGDumpAll=/usr/pgsql-9.3/bin/pg_dumpall
BinPGVacuumdb=/usr/pgsql-9.3/bin/vacuumdb
BinPSQL=/usr/pgsql-9.3/bin/psql
##################################################################
################ Funcoes ######################
##################################################################
_ChecaExecucao(){
_GaranteDiretorios
RC=0
if [ -f $ArquivoLock ]; then
PID=$( cat $ArquivoLock )
echo O script ja esta em execucao PID $PID
ps aux | grep -v grep | grep --color $PID
RC=1
else
touch $ArquivoLock
echo $$ > $ArquivoLock
fi
return $RC
}
_Usage(){
echo -e "
\033[1m`basename $0`\033[0m (Versao $Versao) - Utilitario para backup do banco de dados do CITSmart
Uso: \033[1m`basename $0`\033[0m [OPCAO]
Opcoes:
\033[1mBACKUP\033[0m \e[33m(executado pelo usuario root)\e[0m
-bd, --backup-datatabase Realiza backup do database;
-bc, --backup-configuracoes Realiza backup dos arquivos de configuracoes;
-bu, --backup-usuarios Realiza backup dos usuarios/roles;
\033[1mRESTORE\033[0m \e[33m(executado pelo usurario root, respeitando a sequencia)\e[0m
1. -rc, --restore-configuracoes [/dir/DumpConfiguracoes.tgz] 1. Realiza restore dos arquivos de configuracao;
2. -ru, --restore-usuarios [/dir/DumpUsuarios.gz] 2. Realiza restore dos usuarios;
3. -rd, --restore-database [/dir/DumpDatabase.gz] 3. Realiza restore do database;
\033[1mMANUTENCAO\033[0m \e[33m(executado pelo usuario root)\e[0m
-a, --vacumm-all Reclama area em disco ocupado por registros removidos (performance);
-f, --vacumm-full Reclama area em disco ocupado por registros removidos (performance);
(limpeza completa, deve ser executada uma vez na semana)
-h, --help Imprime esta ajuda e finaliza;
"
}
_TimeStamp(){ date "+%Y%m%d.%H%M%S"; }
_DataHora(){ date "+%Y/%m/%d,%H:%M:%S"; }
_GaranteDiretorios(){
{
[ ! -d $DirDumps ] && { mkdir $DirDumps && chown postgres:postgres --recursive --verbose $DirDumps; }
[ ! -d $DirLogs ] && { mkdir $DirLogs && chown postgres:postgres --recursive --verbose $DirLogs; }
[ ! -d $DirTmp ] && { mkdir $DirTmp && chown postgres:postgres --recursive --verbose $DirTmp; }
chown --verbose postgres:postgres $DirBase
} 1>/dev/null
}
_BackupDatabase(){
local TimeStamp=$( _TimeStamp )
local PrefixoArquivoSaida="Backup.Database.$PGDataBase.$TimeStamp"
local ArquivoLog=$DirLogs/$PrefixoArquivoSaida.log
local ArquivoDump=$DirDumps/$PrefixoArquivoSaida.gz
su -c "touch $ArquivoLog; touch $ArquivoDump" postgres
echo -e "Backup: INICIO [ Database: $PGDataBase - `_DataHora` ]\n\n" | tee $ArquivoLog | strings
su -c "$BinPGDump --username postgres --create --verbose 2>>$ArquivoLog $PGDataBase | gzip --best > $ArquivoDump" postgres
echo -e "\n\nBackup: TERMINO [ Database: $PGDataBase - `_DataHora` ]" | tee --append $ArquivoLog | strings
echo -e "\nArquivos de saída:\n\n1. Log [ $ArquivoLog ]\n2. Dump [ $ArquivoDump ]\n\n" | tee --append $ArquivoLog
}
_BackupUsuarios(){
local TimeStamp=$( _TimeStamp )
local PrefixoArquivoSaida="Backup.Usuarios.$PGDataBase.$TimeStamp"
local ArquivoLog=$DirLogs/$PrefixoArquivoSaida.log
local ArquivoDump=$DirDumps/$PrefixoArquivoSaida.gz
su -c "touch $ArquivoLog" postgres
echo -e "Backup: INICIO [ Usuarios/Rules - `_DataHora` ]\n\n" | tee $ArquivoLog | strings
su -c "$BinPGDumpAll --username postgres --clean --globals-only --ignore-version --verbose 2>>$ArquivoLog | gzip --best > $ArquivoDump" postgres
echo -e "\n\nBackup: TERMINO [ Usuarios/Rules - `_DataHora` ]" | tee --append $ArquivoLog | strings
echo -e "\nArquivos de saída:\n\n1. Log [ $ArquivoLog ]\n2. Dump [ $ArquivoDump ]\n\n" | tee --append $ArquivoLog
}
_BackupConfiguracoes(){
local TimeStamp=$( _TimeStamp )
local ArquivoGZIP="$DirDumps/Backup.Configuracoes.$PGDataBase.$TimeStamp.tgz"
local ArquivoLog="$DirLogs/Backup.Configuracoes.$PGDataBase.$TimeStamp.log"
echo -e "Backup: INICIO [ Configuracoes - `_DataHora` ]\n\n" | tee $ArquivoLog | strings
tar czvf $ArquivoGZIP `find $DirPostgreSQL -type f -iname "*.conf"` 2> /dev/null >> $ArquivoLog
echo -e "\n\nBackup: TERMINO [ Configuracoes - `_DataHora` ]" | tee --append $ArquivoLog | strings
echo -e "\nArquivos de saída:\n\n1. Log [ $ArquivoLog ]\n2. Dump [ $ArquivoGZIP ]\n\n" | tee --append $ArquivoLog
}
_VacummAll(){
local TimeStamp=$( _TimeStamp )
local ArquivoLogDetalhes="$DirLogs/VacuumAll.Detalhes.$PGDataBase.$TimeStamp.log"
local ArquivoLogResumo="$DirLogs/VacuumAll.Resumo.$PGDataBase.$TimeStamp.log"
echo -e "Vacuum: INICIO [ All - `_DataHora` ]\n\n" | tee --append $ArquivoLogDetalhes | tee --append $ArquivoLogResumo | strings
su -c "$BinPGVacuumdb --all --analyze --verbose >> $ArquivoLogResumo 2>> $ArquivoLogDetalhes" postgres
echo -e "\n\nVacuum: TERMINO [ All - `_DataHora` ]" | tee --append $ArquivoLogDetalhes | tee --append $ArquivoLogResumo | strings
echo -e "\nArquivos de saída:\n\n1. Log Detalhado [ $ArquivoLogDetalhes ]\n2. Log Resumido [ $ArquivoLogResumo ]\n\n" | tee --append $ArquivoLog
}
_VacummFull(){
local TimeStamp=$( _TimeStamp )
local ArquivoLogDetalhes="$DirLogs/VacuumFull.Detalhes.$PGDataBase.$TimeStamp.log"
local ArquivoLogResumo="$DirLogs/VacuumFull.Resumo.$PGDataBase.$TimeStamp.log"
echo -e "Vacuum: INICIO [ Full - `_DataHora` ]\n\n" | tee --append $ArquivoLogDetalhes | tee --append $ArquivoLogResumo | strings
su -c "$BinPGVacuumdb --full --analyze --verbose >> $ArquivoLogResumo 2>> $ArquivoLogDetalhes" postgres
echo -e "\n\nVacuum: TERMINO [ Full - `_DataHora` ]" | tee --append $ArquivoLogDetalhes | tee --append $ArquivoLogResumo | strings
echo -e "\nArquivos de saída:\n\n1. Log Detalhado [ $ArquivoLogDetalhes ]\n2. Log Resumido [ $ArquivoLogResumo ]\n\n" | tee --append $ArquivoLog
}
_RestoreConfiguracoes(){
if [ $# -eq 1 ]; then
local DumpConfiguracoes=$1
if [ -f $DumpConfiguracoes ]; then
local TimeStamp=$( _TimeStamp )
local LogTemporario=$DirTmp/LogTemporario
tar xzvf $DumpConfiguracoes --directory $DirTmp > $LogTemporario
while read Arquivo
do
echo -e "\nBackup do /$Arquivo original"
cp -Rfav /$Arquivo{,.original.$TimeStamp}
echo -e "\n Substituicao do /$Arquivo original pelo backupeado"
cp -Rfav $DirTmp/$Arquivo /$Arquivo
echo -e "\n Mudando dono:grupo do arquivo para postgres:postgres"
chown --verbose postgres:postgres /$Arquivo
done < $LogTemporario
echo -e "\nRestartando o banco de dados"
/etc/init.d/postgres restart
sleep 10
echo -e "\nStatus o banco de dados"
/etc/init.d/postgres status
rm -rf $DirTmp/* > /dev/null 2>&1
else
echo "Nao identificado o arquivo. Forneca o path completo/correto"
fi
else
echo "Forneca o arquivo compactado como parametro"
fi
}
_RestoreDump(){
if [ $# -eq 1 ]; then
local Dump=$1
local DumpDescompactado=$DirTmp/DumpDescompactado
if [ -f $Dump ]; then
echo -e "\nDescompactando o dump $Dump"
gzip --decompress --verbose --to-stdout $Dump > $DumpDescompactado
echo -e "\nRestaurando $DumpDescompactado"
sudo -u postgres $BinPSQL --username postgres --file $DumpDescompactado
rm -rf $DumpDescompactado > /dev/null 2>&1
else
echo "Nao identificao o arquivo. Forneca o path completo/correto"
fi
else
echo "Forneca o arquivo compactado como parametro"
fi
}
##################################################################
################ Inicio do Script ######################
##################################################################
_ChecaExecucao && {
if [ $# -ge 1 ]; then
Parametro=$( echo $1 | tr [:upper:] [:lower:] )
case $Parametro in
--backup-datatabase|-bd) { _BackupDatabase ; } ;;
--backup-configuracoes|-bc) { _BackupConfiguracoes ; } ;;
--backup-usuarios|-bu) { _BackupUsuarios ; } ;;
--restore-database|-rd) { _RestoreDump $2 ; } ;;
--restore-configuracoes|-rc) { _RestoreConfiguracoes $2 ; } ;;
--restore-usuarios|-ru) { _RestoreDump $2 ; } ;;
--vacumm-all|-a) { _VacummAll ; } ;;
--vacumm-full|-f) { _VacummFull ; } ;;
--help|-h) { _Usage ; } ;;
*) { _Usage ; } ;;
esac
else
_Usage
fi
[ -f $ArquivoLock ] && rm -rf $ArquivoLock > /dev/null 2>&1
}
##################################################################
################ Fim do Script ######################
################################################################
@richardsonlima
Copy link

I thinking all times that I see your scripts that you have a best programming feeling and then I'll use this script to manage my EC2 and Droplets machines that contains PostgreSQL because I am very motivated to write many shell scripts after read your codes. Thanks to contribute for a best F/OSS world !!!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment