Bash Script Backup Database Postgresql

Script Backup Full Database 

#!/bin/bash PATH_FILE=/var/lib/pgsql/12/backups #sesuaikan directory, testing menggunakan postgres12 di linux rhel LOG_PATH=/var/lib/pgsql/12/backups #sesuaikan directory, testing menggunakan postgres12 di linux rhel DATE=`date +%Y%m%d` export PGPASSWORD=password_akses_postgres   echo START `date` >> ${LOG_PATH}/backup_full_one_day.log pg_dumpall -U postgres -h 127.0.0.1 -p 5432 -v --roles-only -f ${PATH_FILE}/full_backup/"all_roles_db_${DATE}_dc1.sql" 2>> ${LOG_PATH}/backup_full_one_day.log 2>&1   echo START BACKUP Schema database_A `date` >> ${LOG_PATH}/backup_full_one_day.log 2>&1 pg_dump -U postgres -h 127.0.0.1 -p 5432 -s database_A | gzip -9 > ${PATH_FILE}/full_backup/database_A_${DATE}_dc1_schema.sql.gz 2>> ${LOG_PATH}/backup_full_one_day.log 2>&1 pg_dump -U postgres -h 127.0.0.1 -p 5432 -a database_A | gzip -9 > ${PATH_FILE}/full_backup/database_A_${DATE}_dc1_data.sql.gz 2>> ${LOG_PATH}/backup_full_one_day.log 2>&1 echo DONE BACKUP Schema database_A `date` >> ${LOG_PATH}/backup_full_one_day.log 2>&1   echo START BACKUP Schema database_B `date` >> ${LOG_PATH}/backup_full_one_day.log 2>&1 pg_dump -U postgres -h 127.0.0.1 -p 5432 -s database_B | gzip -9 > ${PATH_FILE}/full_backup/database_B_${DATE}_dc1_schema.sql.gz 2>> ${LOG_PATH}/backup_full_one_day.log 2>&1 pg_dump -U postgres -h 127.0.0.1 -p 5432 -a database_B | gzip -9 > ${PATH_FILE}/full_backup/database_B_${DATE}_dc1_data.sql.gz 2>> ${LOG_PATH}/backup_full_one_day.log 2>&1 echo DONE BACKUP Schema database_B `date` >> ${LOG_PATH}/backup_full_one_day.log 2>&1   echo START BACKUP Schema database_C `date` >> ${LOG_PATH}/backup_full_one_day.log 2>&1 pg_dump -U postgres -h 127.0.0.1 -p 5432 -s database_C | gzip -9 > ${PATH_FILE}/full_backup/database_C_${DATE}_dc1_schema.sql.gz 2>> ${LOG_PATH}/backup_full_one_day.log 2>&1 pg_dump -U postgres -h 127.0.0.1 -p 5432 -a database_C | gzip -9 > ${PATH_FILE}/full_backup/database_C_${DATE}_dc1_data.sql.gz 2>> ${LOG_PATH}/backup_full_one_day.log 2>&1 echo DONE BACKUP Schema database_C`date` >> ${LOG_PATH}/backup_full_one_day.log 2>&1   echo START BACKUP Schema database_D `date` >> ${LOG_PATH}/backup_full_one_day.log 2>&1 pg_dump -U postgres -h 127.0.0.1 -p 5432 -s database_D | gzip -9 > ${PATH_FILE}/full_backup/database_D_${DATE}_dc1_schema.sql.gz 2>> ${LOG_PATH}/backup_full_one_day.log 2>&1 pg_dump -U postgres -h 127.0.0.1 -p 5432 -a database_D | gzip -9 > ${PATH_FILE}/full_backup/database_D_${DATE}_dc1_data.sql.gz 2>> ${LOG_PATH}/backup_full_one_day.log 2>&1 echo DONE BACKUP Schema database_D `date` >> ${LOG_PATH}/backup_full_one_day.log 2>&1   echo START BACKUP Schema database_E `date` >> ${LOG_PATH}/backup_full_one_day.log 2>&1 pg_dump -U postgres -h 127.0.0.1 -p 5432 -s database_E | gzip -9 > ${PATH_FILE}/full_backup/database_E_${DATE}_dc1_schema.sql.gz 2>> ${LOG_PATH}/backup_full_one_day.log.log 2>&1 pg_dump -U postgres -h 127.0.0.1 -p 5432 -a database_E | gzip -9 > ${PATH_FILE}/full_backup/database_E_${DATE}_dc1_data.sql.gz 2>> ${LOG_PATH}/backup_full_one_day.log 2>&1 echo DONE BACKUP Schema database_E `date` >> ${LOG_PATH}/backup_full_one_day.log 2>&1   cd ${PATH_FILE}/full_backup gzip -9 all_roles_db_${DATE}_dc1.sql   echo END `date` >> ${LOG_PATH}/backup_full_one_day.log 2>&1 find ${PATH_FILE}/full_backup -type f -iname "*.sql.gz" -mtime +2 -exec rm {} \; #menyisakan backup 2 hari   echo ======================================================================================================================= >> ${LOG_PATH}/backup_full_one_day.log

Script Backup Database per Schema

#!/bin/bash PATH_FILE=/var/lib/pgsql/12/backups LOG_PATH=/var/lib/pgsql/12/backups DATE=`date +%Y%m%d` export PGPASSWORD=password_login_postgres     echo ======================================================================================================================= >> ${LOG_PATH}/backup_full.log echo START `date` >> ${LOG_PATH}/backup_full.log   echo START BACKUP Schema NAMA_DATABASE `date` >> ${LOG_PATH}/backup_full.log pg_dump -U postgres -h 127.0.0.1 -p 5432 --schema=public -s NAMA_DATABASE | gzip -9 > ${PATH_FILE}/full_backup/NAMA_DATABASE_public_${DATE}_dc1_schema.sql.gz 2>> ${LOG_PATH}/backup_full.log pg_dump -U postgres -h 127.0.0.1 -p 5432 --schema=NAMA_SCHEMA -s NAMA_DATABASE | gzip -9 > ${PATH_FILE}/full_backup/NAMA_DATABASE_NAMA_SCHEMA_${DATE}_dc1_schema.sql.gz 2>> ${LOG_PATH}/backup_full.log pg_dump -U postgres -h 127.0.0.1 -p 5432 --schema=NAMA_SCHEMA_2 -s NAMA_DATABASE | gzip -9 > ${PATH_FILE}/full_backup/NAMA_DATABASE_NAMA_SCHEMA_2_${DATE}_dc1_schema.sql.gz 2>> ${LOG_PATH}/backup_full.log pg_dump -U postgres -h 127.0.0.1 -p 5432 --schema=NAMA_SCHEMA_3 -s NAMA_DATABASE | gzip -9 > ${PATH_FILE}/full_backup/NAMA_DATABASE_NAMA_SCHEMA_3_${DATE}_dc1_schema.sql.gz 2>> ${LOG_PATH}/backup_full.log pg_dump -U postgres -h 127.0.0.1 -p 5432 --schema=NAMA_SCHEMA_4 -s NAMA_DATABASE | gzip -9 > ${PATH_FILE}/full_backup/NAMA_DATABASE_NAMA_SCHEMA_4_${DATE}_dc1_schema.sql.gz 2>> ${LOG_PATH}/backup_full.log pg_dump -U postgres -h 127.0.0.1 -p 5432 --schema=NAMA_SCHEMA_5 -s NAMA_DATABASE | gzip -9 > ${PATH_FILE}/full_backup/NAMA_DATABASE_NAMA_SCHEMA_5_${DATE}_dc1_schema.sql.gz 2>> ${LOG_PATH}/backup_full.log echo END BACKUP Schema NAMA_DATABASE `date` >> ${LOG_PATH}/backup_full.log   echo START BACKUP Data NAMA_DATABASE schema public `date` >> ${LOG_PATH}/backup_full.log pg_dump -U postgres -h 127.0.0.1 -p 5432 --schema=public -a NAMA_DATABASE | gzip -9 > ${PATH_FILE}/full_backup/NAMA_DATABASE_public_${DATE}_dc1_data.sql.gz 2>> ${LOG_PATH}/backup_full.log echo END BACKUP Data NAMA_DATABASE schema public `date` >> ${LOG_PATH}/backup_full.log   echo START BACKUP Data NAMA_DATABASE schema NAMA_SCHEMA `date` >> ${LOG_PATH}/backup_full.log pg_dump -U postgres -h 127.0.0.1 -p 5432 --schema=NAMA_SCHEMA -a NAMA_DATABASE | gzip -9 > ${PATH_FILE}/full_backup/NAMA_DATABASE_NAMA_SCHEMA_${DATE}_dc1_data.sql.gz 2>> ${LOG_PATH}/backup_full.log echo END BACKUP Data NAMA_DATABASE schema NAMA_SCHEMA `date` >> ${LOG_PATH}/backup_full.log   echo START BACKUP Data NAMA_DATABASE schema NAMA_SCHEMA_2 `date` >> ${LOG_PATH}/backup_full.log pg_dump -U postgres -h 127.0.0.1 -p 5432 --schema=NAMA_SCHEMA_2 -a NAMA_DATABASE | gzip -9 > ${PATH_FILE}/full_backup/NAMA_DATABASE_NAMA_SCHEMA_2_${DATE}_dc1_data.sql.gz 2>> ${LOG_PATH}/backup_full.log echo END BACKUP Data NAMA_DATABASE schema NAMA_SCHEMA_2 `date` >> ${LOG_PATH}/backup_full.log   echo START BACKUP Data NAMA_DATABASE schema NAMA_SCHEMA_3 `date` >> ${LOG_PATH}/backup_full.log pg_dump -U postgres -h 127.0.0.1 -p 5432 --schema=NAMA_SCHEMA_3 -a NAMA_DATABASE | gzip -9 > ${PATH_FILE}/full_backup/NAMA_DATABASE_NAMA_SCHEMA_3_${DATE}_dc1_data.sql.gz 2>> ${LOG_PATH}/backup_full.log echo END BACKUP Data NAMA_DATABASE schema NAMA_SCHEMA_3 `date` >> ${LOG_PATH}/backup_full.log   echo START BACKUP Data NAMA_DATABASE schema NAMA_SCHEMA_4 `date` >> ${LOG_PATH}/backup_full.log pg_dump -U postgres -h 127.0.0.1 -p 5432 --schema=NAMA_SCHEMA_4 -a NAMA_DATABASE | gzip -9 > ${PATH_FILE}/full_backup/NAMA_DATABASE_NAMA_SCHEMA_4_${DATE}_dc1_data.sql.gz 2>> ${LOG_PATH}/backup_full.log echo END BACKUP Data NAMA_DATABASE schema NAMA_SCHEMA_4 `date` >> ${LOG_PATH}/backup_full.log   echo START BACKUP Data NAMA_DATABASE schema NAMA_SCHEMA_5 `date` >> ${LOG_PATH}/backup_full.log pg_dump -U postgres -h 127.0.0.1 -p 5432 --schema=NAMA_SCHEMA_5 -a NAMA_DATABASE | gzip -9 > ${PATH_FILE}/full_backup/NAMA_DATABASE_NAMA_SCHEMA_5_${DATE}_dc1_data.sql.gz 2>> ${LOG_PATH}/backup_full.log echo END BACKUP Data NAMA_DATABASE schema NAMA_SCHEMA_5 `date` >> ${LOG_PATH}/backup_full.log   echo END `date` >> ${LOG_PATH}/backup_full.log echo ======================================================================================================================= >> ${LOG_PATH}/backup_full.log