PROBLEM SOLVING ON UNIX/LINUX SYSTEMS: PostgreSQL: Backup/Restore Parallels

#!/bin/bash
#/opt/script/postgres.[db].[backup].[full].sh
#LastUpdate: #15:28 2019.08.08
###########################################
#______________POSTGRESQLDB_BACKUP:BEGIN
#BACKUP POSTGRESQLDB daily:
#10 00 * * * /opt/script//opt/script/postgres.[db].[backup].[full].sh DB_NAME
#______________POSTGRESQLDB_BACKUP:END
###########################################
source /opt/postgres/POSTGRES_VERSION.txt
#____DBNAME_WANT_TO_BACKUP:BEGIN <<<<<-----
DB_NAME=$1
LAN_IP=$(ip a s|sed -ne '/127.0.0.1/!{s/^[ \t]*inet[ \t]*$[0-9.]\+$\/.*$/\1/p}' | grep 2.2.2)
#____DBNAME_WANT_TO_BACKUP:END <<<<<-----
if [ "$#" -eq "0" ]
then
echo "Usage: $0 DB_NAME"
else
mkdir -p $PG_ARCHIVED$DB_NAME
chown -R postgres.postgres $PG_ARCHIVED
now1="$(date +'%Y.%m.%d-%H.%M.%S.%3N')"
DB_RESULT=$PG_ARCHIVED$DB_NAME/$LAN_IP.$DB_NAME.[db].[backup].[full].[parallel].[$now1]
echo "########################################" >> $DB_RESULT.log
echo "BACKUP FULL DB_NAME: [$DB_NAME]" >> $DB_RESULT.log
echo "-------------------" >> $DB_RESULT.log
echo "SERVER INFORMATION:" >> $DB_RESULT.log
echo "-------------------" >> $DB_RESULT.log
ip a s | grep inet >> $DB_RESULT.log
echo "----------------------------------------" >> $DB_RESULT.log
hostnamectl status >> $DB_RESULT.log
echo "----------------------------------------" >> $DB_RESULT.log
df -h >> $DB_RESULT.log
echo "----------------------------------------" >> $DB_RESULT.log
timedatectl | grep Time >> $DB_RESULT.log
echo "CurrentTime: $(date)">> $DB_RESULT.log
echo "----------------------------------------" >> $DB_RESULT.log
echo "CPU STATS: ">> $DB_RESULT.log
top -b -n 1 | head -n 5 >> $DB_RESULT.log
echo "----------------------------------------" >> $DB_RESULT.log
printf "RAM FREE: \n $(free -h)\n" >> $DB_RESULT.log
echo "----------------------------------------" >> $DB_RESULT.log
echo "CPU INFO: $(cat /proc/cpuinfo | grep Xeon | head -n 1)" >> $DB_RESULT.log
#____BACKUP_SCRIPT_parallel:BEGIN
su - postgres -c "\
source /opt/postgres/POSTGRES_VERSION.txt;\
$PG_SQL/bin/pg_dump --host 127.0.0.1 \
-Fd -j $(cat /proc/cpuinfo | grep Xeon | wc -l) \
--verbose \
--dbname=$DB_NAME \
-f $DB_RESULT" >> $DB_RESULT.log.detail 2>&1
#____BACKUP_SCRIPT_parallel:END
#____COMPRESS_BACKUP_FOLDER:BEGIN
cd $PG_ARCHIVED$DB_NAME
tar -cf $LAN_IP.$DB_NAME.[db].[backup].[full].[parallel].[$now1].tar.gz $LAN_IP.$DB_NAME.[db].[backup].[full].[parallel].[$now1]
/bin/rm -rf $LAN_IP.$DB_NAME.[db].[backup].[full].[parallel].[$now1]
#____COMPRESS_BACKUP_FOLDER:END
md5sum $LAN_IP.$DB_NAME.[db].[backup].[full].[parallel].[$now1].tar.gz > $LAN_IP.$DB_NAME.[db].[backup].[full].[parallel].[$now1].tar.gz.md5.txt

echo "########################################" >> $DB_RESULT.log
echo "BACKUP RESULTED:" >> $DB_RESULT.log
cat $LAN_IP.$DB_NAME.[db].[backup].[full].[parallel].[$now1].tar.gz.md5.txt >> $DB_RESULT.log
ls -lh "$DB_RESULT"* >> $DB_RESULT.log
echo "BACKUP FULL DB_NAME START: [$DB_NAME]-[$now1]" >> $DB_RESULT.log
now2="$(date +'%Y.%m.%d-%H.%M.%S.%3N')"
echo "BACKUP FULL DB_NAME END : [$DB_NAME]-[$now2]" >> $DB_RESULT.log
echo "########################################" >> $DB_RESULT.log
echo "" >> $DB_RESULT.log

#______________________DELETE_OLD_DATA:BEGIN
cd $PG_ARCHIVED$DB_NAME
NumberOfFileWantToKeep=24
echo "PG_ARCHIVED: [$PG_ARCHIVED]" >> $DB_RESULT.log
echo "Current Dir: [$(pwd)]" >> $DB_RESULT.log
echo "NumberOfFileWantToKeep: [$NumberOfFileWantToKeep]" >> $DB_RESULT.log
#Giu lai [24] file moi nhat trong thu muc [$FOLDER_DST]:
echo "Truncate Old Data In: [$PG_ARCHIVED]" >> $DB_RESULT.log
echo "rm -rf `ls -t \"$LAN_IP.$DB_NAME.[db].[backup].[full]\"* | awk 'NR>24'`" >> $DB_RESULT.log
/bin/rm -rf `ls -t "$LAN_IP.$DB_NAME.[db].[backup].[full]"* | awk 'NR>24'`
#______________________DELETE_OLD_DATA:END
#____FREE_RAM:BEGIN
free && sync && echo 3 > /proc/sys/vm/drop_caches && free
free
# printf "RAM FREE: \n $(free -h)\n" >> $DB_RESULT.log
#____FREE_RAM:END
echo "" >> $DB_RESULT.log
echo "BACKUP JOB DONE AT [$(date +'%Y.%m.%d-%H.%M.%S.%3N')]" >> $DB_RESULT.log
echo "" >> $DB_RESULT.log
chown -R postgres.postgres $PG_ARCHIVED
fi

#THE-END
# #BACKUP: OK
# # -Fd: Phai cung version:
# # http://evol-monkey.blogspot.com/2013/10/parallel-pgdump-backups.html
# # I was talking with a friend about this feature and he said: Who does pg_dump now days anyway? Everyone is doing live backups
date1=$(date);
su - postgres -c "\
source /opt/postgres/POSTGRES_VERSION.txt;
/opt/postgres/bin/pg_dump \
-h 127.0.0.1 \
-p 5432 \
-Fd \
-j $(cat /proc/cpuinfo | grep Xeon | wc -l) \
--dbname='PostgreSQL_DB_NAME' \
--verbose \
-f /opt/temp/PostgreSQL_DB_NAME_db\
"
#
date2=$(date); echo $date1; echo $date2;
#
Tue Nov 10 15:08:50 +07 2020
Tue Nov 10 15:09:54 +07 2020

#RESTORE: OK
date1=$(date);
su - postgres -c "\
source /opt/postgres/POSTGRES_VERSION.txt;
/opt/postgres/bin/pg_restore \
-h 127.0.0.1 \
-p 5432 \
-Fd \
-j $(cat /proc/cpuinfo | grep Xeon | wc -l) \
--dbname='PostgreSQL_DB_NAME_beta_20201110' \
--verbose \
'/opt/temp/PostgreSQL_DB_NAME_db' \
"
#
date2=$(date); echo $date1; echo $date2;
#

#RESTORE TO NEW DB, WITH FULL DATA:
#postgres-connect-dba.sh
#CREATE DATABASE "PostgreSQL_DB_NAME";
#\q
#su - postgres -c 'pg_restore --dbname="PostgreSQL_DB_NAME" --verbose /db/bk/PostgreSQL_DB_NAME_dev.full-2016.12.05-16.27.13.293.tar'
# #DROP OLD DB:
# postgres=# select pg_terminate_backend(pid) from pg_stat_activity where datname='PostgreSQL_DB_NAME';
# postgres=# drop database "PostgreSQL_DB_NAME";
# #CREATE NEW DB NAME:
# postgres=# CREATE DATABASE "PostgreSQL_DB_NAME";
# postgres=# \q
# #RESTORE OLD DBNAME TO NEW DB NAME, WITH FULL DATA:
# #11:46 2019.01.13
# ##################
# #Souce:
# https://www.postgresql.org/docs/10/app-pgdump.html
# https://www.postgresql.org/docs/10/app-pgrestore.html

# ###########
# #THE-END###
# ###########

PROBLEM SOLVING ON UNIX/LINUX SYSTEMS

2020-11-10

PostgreSQL: Backup/Restore Parallels

No comments:

Post a Comment