PG最典型和實用的熱備指令碼實戰
對於DBA或運維人員來講,備份恢復是最基本也是最必要的技能之一,一個通用並且穩定的熱備指令碼能夠極大的釋放工程師的時間和精力,本篇提供PostgreSQL資料庫最常用的熱備指令碼,希望能夠幫助到大家。
1、PostgreSQL邏輯熱備指令碼(Linux版)
指令碼程式碼:
#!/bin/bash #Need PostgreSQL 9.0+ #Only modify the following variables!! #pg_dump exec directory PGBIN=/postgres/app/pg12/bin #host address HOST=192.168.51.241 #backup username, pleause use high privileges user USER=postgres #backup username password PASSWORD=Abcd321# export PGPASSWORD=$PASSWORD #database port PORT=5432 #database name for connect test DBNAME=postgres #backup db,example: DBARRAY=('db1' 'db2') DBARRAY=('db01' 'db02') #backup file directory DUMPPATH=/backup #backup temp log TEMPLOG=$DUMPPATH/pg_logicbak.out #backup log file LOGFILE=$DUMPPATH/pg_logicbak.log #backup file save time, DAYS SAVETIME=2 #whether parallel execution is required? 'y' is required, 'n' isn's required ISPARALLEL='n' #parallel level PARALLEL=4 shopt -s expand_aliases alias cdate='date "+%Y-%m-%d %H:%M:%S"' CONN="-h $HOST -p $PORT -U $USER" stdopt=`$PGBIN/psql $CONN -d $DBNAME -t -c "select 123" 2> /dev/null|awk '{print $NF}'` if [ -n "$stdopt" ] && [ "$stdopt" = "123" ];then echo "$(cdate) [INFO] Connect is successed, Take it easy." >> $LOGFILE else echo "$(cdate) [ERROR] Connect is failed, Please check." >> $LOGFILE exit 1 fi for(( i=0;i<${#DBARRAY[@]};i++ ))do if [ ${ISPARALLEL} = 'n' ];then $PGBIN/pg_dump -h${HOST} -U${USER} -Fc -p ${PORT} -v -f ${DUMPPATH}/pglogicbak_$(date "+%Y%m%d%H%M%S")_${DBARRAY[i]}'.dump' ${DBARRAY[i]} > $TEMPLOG 2>&1 count=`grep 'pg_dump: saving' $TEMPLOG | wc -l` if [ ${count} = 4 ];then echo -e "$(cdate) [INFO] ${DBARRAY[i]} logical backuped successfully." >> $LOGFILE else echo -e "$(cdate) [ERROR] ${DBARRAY[i]} logical backuped failed. Please Check!" >> $LOGFILE fi find $DUMPPATH -type f -name "pglogicbak_*.dump" -ctime +$SAVETIME | xargs rm -f elif [ ${ISPARALLEL} = 'y' ];then $PGBIN/pg_dump -h${HOST} -U${USER} -Fd -j ${PARALLEL} -p ${PORT} -v -f ${DUMPPATH}/pglogicbak_$(date "+%Y%m%d%H%M%S")_${DBARRAY[i]}/ ${DBARRAY[i]} > $TEMPLOG 2>&1 count=`grep 'pg_dump: saving' $TEMPLOG | wc -l ` if [ ${count} = 4 ];then echo -e "$(cdate) [INFO] ${DBARRAY[i]} logical backuped successfully." >> $LOGFILE else echo -e "$(cdate) [ERROR] ${DBARRAY[i]} logical backuped failed. Please Check!" >> $LOGFILE fi find $DUMPPATH -type d -name "pglogicbak_*" -ctime +$SAVETIME | xargs rm -rf else exit 1 fi done; 正常輸出: 2022-12-29 16:01:24 [INFO] Connect is successed, Take it easy. 2022-12-29 16:01:24 [INFO] db01 logical backuped successfully. 2022-12-29 16:01:24 [INFO] db02 logical backuped successfully. 2022-12-29 16:01:31 [INFO] Connect is successed, Take it easy. 2022-12-29 16:01:32 [INFO] db01 logical backuped successfully. 2022-12-29 16:01:32 [INFO] db02 logical backuped successfully. 錯誤輸出: 2022-12-29 15:59:28 [ERROR] Connect is failed, Please check. 2022-12-29 15:59:54 [ERROR] Connect is failed, Please check. 2022-12-29 16:00:10 [ERROR] Connect is failed, Please check. 2022-12-29 16:00:30 [ERROR] Connect is failed, Please check.
2、PostgreSQL物理熱備指令碼(Linux版)
指令碼程式碼:
#!/bin/bash #Need PostgreSQL 9.0+ #Only modify the following variables!! #pg_basebackup exec directory PGBIN=/postgres/app/pg12/bin #host address HOST=127.0.0.1 #backup username, pleause use replication privileges user USER=postgres #backup username password PASSWORD=Abcd321# export PGPASSWORD=$PASSWORD #database port PORT=5432 #database name for connect test DBNAME=postgres #backup file directory DUMPPATH=/backup #backup temp log TEMPLOG=$DUMPPATH/pg_physicbak.out #backup log file LOGFILE=$DUMPPATH/pg_physicbak.log #Backup mode: 'p' is normal mode, and 't' is packaging mode (packaging the data directory) DUMPTYPE='p' #whether compression is required, 'y' means compression, and 'n' means no compression ISCOMPASS='n' #compression level (0-9, default 5). The higher the level, the slower the compression, #and the higher compression ratio ('5' is about 1/6 of the size of the data directory) COMPASS_LEVEL=5 #backup file save time, DAYS SAVETIME=2 shopt -s expand_aliases alias cdate='date "+%Y-%m-%d %H:%M:%S"' alias cdate2='date "+%Y%m%d%H%M%S"' CONN="-h $HOST -p $PORT -U $USER" stdopt=`$PGBIN/psql $CONN -d $DBNAME -t -c "select 123" 2> /dev/null|awk '{print $NF}'` if [ -n "$stdopt" ] && [ "$stdopt" = "123" ];then echo "$(cdate) [INFO] Connect is successed, Take it easy." >> $LOGFILE else echo "$(cdate) [ERROR] Connect is failed, Please check." >> $LOGFILE exit 1 fi if [ ${DUMPTYPE} = 'p' ];then $PGBIN/pg_basebackup $CONN -Fp -P -v -D ${DUMPPATH}/full_physicbak_$(cdate2) > $TEMPLOG 2>&1 elif [ ${DUMPTYPE} = 't' ];then if [ ${ISCOMPASS} = 'n' ];then $PGBIN/pg_basebackup $CONN -Ft -P -v -D ${DUMPPATH}/full_physicbak_$(cdate2) > $TEMPLOG 2>&1 elif [ ${ISCOMPASS} = 'y' ];then $PGBIN/pg_basebackup $CONN -Ft -z -Z ${COMPASS_LEVEL} -P -v -D ${DUMPPATH}/full_physicbak_$(cdate2) > $TEMPLOG 2>&1 else exit 1 fi else exit 1 fi count=`grep 'pg_basebackup: base backup completed' $TEMPLOG | wc -l` if [ ${count} = 1 ];then echo "$(cdate) [INFO] PostgreSQL physical backuped successfully." >> $LOGFILE else echo "$(cdate) [ERROR] PostgreSQL physical backuped failed. Please Check!" >> $LOGFILE fi find $DUMPPATH -type d -name "full_physicbak_*" -ctime +$SAVETIME | xargs rm -rf 正常輸出: 2022-12-29 16:06:26 [INFO] Connect is successed, Take it easy. 2022-12-29 16:06:34 [INFO] PostgreSQL physical backuped successfully. 2022-12-29 16:07:05 [INFO] Connect is successed, Take it easy. 2022-12-29 16:07:13 [INFO] PostgreSQL physical backuped successfully. 錯誤輸出: 2022-12-29 16:05:03 [ERROR] Connect is failed, Please check. 2022-12-29 16:05:21 [ERROR] Connect is failed, Please check. 2022-12-29 16:05:34 [ERROR] Connect is failed, Please check. 2022-12-29 16:05:57 [ERROR] Connect is failed, Please check.
以上兩種熱備場景,指令碼中均內建的備份成功關鍵字的判斷,只有真正成功完成備份,才會在日誌中顯示備份成功,因此在備份可靠性方面是有保障的。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30310891/viewspace-2930066/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL最典型和實用的熱備指令碼實戰MySql指令碼
- oracle的熱備指令碼Oracle指令碼
- oracle 熱備指令碼 .Oracle指令碼
- 生成熱備份指令碼指令碼
- 10個典型實用的PHP程式碼片段PHP
- 能用js實現的最終用js實現,Shell指令碼也不例外JS指令碼
- Oracle物理熱備份指令碼(ZT)Oracle指令碼
- Oracle自動備份指令碼的實現Oracle指令碼
- 基於資料庫的熱備指令碼資料庫指令碼
- 日常實用指令碼整理指令碼
- rman實用指令碼集指令碼
- 幾例實用的Shell指令碼指令碼
- 常用的幾個典型指令碼指令碼
- Steam 推出全新實時和周度榜單,展示最熱銷和最熱玩遊戲遊戲
- PG 資料庫恢復實戰資料庫
- linux下oracle熱備份指令碼LinuxOracle指令碼
- 基於表空間的熱備份指令碼指令碼
- 一站看完全網熱文,目前最實用的熱點工具!
- EJB實用原理和實戰示例
- 基於Groovy的規則指令碼引擎實戰指令碼
- 9個實用shell指令碼指令碼
- 分享兩個實用的shell指令碼指令碼
- 實用的linux下指令碼!(轉)Linux指令碼
- 9個實用的Javascript程式碼高亮指令碼JavaScript指令碼
- 行業實踐:RocketMQ 業務整合典型行業應用和實踐行業MQ
- SpringBoot整合RabbitMQ之典型應用場景實戰二Spring BootMQ
- SpringBoot整合RabbitMQ之典型應用場景實戰一Spring BootMQ
- 歸檔模式下熱備原理及指令碼模式指令碼
- EJB實用原理和實戰示例 and more?
- 《EJB實用原理和實戰示例》中的實戰示例問題請教
- 超級實用的 iptables 防火牆指令碼防火牆指令碼
- AE實用指令碼之——typemonkey指令碼
- 【實用】需要收藏備用的JQuery程式碼片段jQuery
- Unity3D熱更新全書-指令碼(四) 用C#LightEvil搭建實際開發使用的指令碼框架Unity3D指令碼C#框架
- rman備份和增量備份指令碼指令碼
- Redis 實戰 —— 14. Redis 的 Lua 指令碼程式設計Redis指令碼程式設計
- Appium iOS 測試指令碼開發實戰APPiOS指令碼
- 指令碼之美│VBS 入門互動實戰指令碼