PostgreSQL 备份恢复 多参数 Dokcer版本 多库for循环

#!/bin/bash

#操作类型，backup、restore
type=$1
#造作数据库schema名
dbname=$2
#备份文件名，格式为：注册名_yyyyMMddHHmmss.sql
backupFileName=$3
#数据库所在服务器ip
dbhost=$4

#固定存储目录/home/backup/
if [ ! -d "/home/backup/" ];then
    mkdir "/home/backup/"
fi

backupFile="/home/backup/"${backupFileName}
echo ${backupFile}

cd /usr/pgsql-14/bin

if [ $type == "backup" ];then
    PGPASSWORD="postgres" ./pg_dump -h ${dbhost} -U postgres ${dbname}  > ${backupFile}
elif [ $type == "restore" ];then
    #先清理掉schema，不然相对于恢复点有新增数据，恢复时不会清理
    PGPASSWORD="postgres" ./psql -h ${dbhost} -U postgres -d ${dbname} -c "drop schema public cascade;create schema public;"
    PGPASSWORD='postgres' ./psql -h ${dbhost} -U postgres -d ${dbname}  -f ${backupFile}
else
    echo "没有合适的操作类型"
    exit 1
fi

exit 0

sh -x backup.sh backup a a.2023 localhost
sh -x backup.sh restore a a.2023 localhost

1.1 SQL转储
1.1.1 pg_dump命令
pg_dump是一个普通的PostgreSQL客户端应用，可以访问该数据库的远端主机上进行备份工作。
pg_dump每次只转储一个数据库，而且它不会转储关于角色或表空间（因为它们是集簇范围的）的信息。
a.转储

需要先登陆数据库: su - postgres

# 本机
pg_dump dbname > outfile
 
# 远程
pg_dump -h 1.1.1.1 -p 1234 dbname > dbname.bak
 
# 实时自动备份数据库
   a. 登录数据库
        su - postgres
   b.创建备份目录
        mkdir -p ~/dbbackups
   c.创建定时任务
        crontab -e
    编辑文件:
        0 0 * * 0 pg_dump -U postgres laozuo> ~/dbbackups/laozuo.org.bak
b.恢复 

# infile为pg_dump命令的输出文件，命令不会创建数据库dbname，
# 在执行psql前需要先从template0创建dbname（
# 例如，用命令createdb -T template0 dbname）。
psql dbname < infile
 
 
# ON_ERROR_STOP的作用: 遇到一个SQL错误后让psql退出
psql --set ON_ERROR_STOP=on dbname < infile
c.转储恢复一体化 

# pg_dump和psql使用管道结合，直接从一个服务器转储一个数据库到另一个服务器
pg_dump -h host1 dbname | psql -h host2 dbname
1.1.2 pg_dump命令处理大型数据库
对于非常大型的数据库，可以将将split配合其他两种方法(gizip,并行度)之一进行使用。

# 处理大型数据库时,可以压缩存储
    pg_dump dbname | gzip > filename.gz
    
    # 此时的恢复:
        gunzip -c filename.gz | psql dbname
        # 或者
        cat filename.gz | gunzip | psql dbname
 
# 大型数据库，分割存储
    # 让每一块的大小为1兆字节
        pg_dump dbname | split -b 1m - filename
   
    # 恢复
        cat filename* | psql dbname
 
# 自定义转储格式
    # 如果PostgreSQL所在的系统上安装了zlib压缩库，
    # 自定义转储格式将在写出数据到输出文件时对其压缩。这
    # 将产生和使用gzip时差不多大小的转储文件，
    # 但是这种方式的一个优势是其中的表可以被有选择地恢复。
    pg_dump -Fc dbname > filename
 
    # 恢复
    # 自定义格式的转储不是psql的脚本，只能通过pg_restore恢复
    pg_restore -d dbname filename
 
# 使用并行模式转储
    # 使用-j参数控制并行度，并行转储只支持“目录”归档格式
    pg_dump -j num -F d -f out.dir dbname
 
#目录格式备份：
pg_dump -h localhost -p 5432 -U someuser -F d -f /somepath/a_directory mydb
 
# 从9.3版本开始支持并行备份选项--jobs (-j),
# 此选项只有在按目录格式进行备份时才会生效，每个写线程只负责写一个单独的文件，
# 因此一定是输出结果为多个独立的文件时才可以并行。
pg_dump -h localhost -p 5432 -U someuser -j 3 -Fd -f /somepath/a_directory mydb
 1.1.3 pg_dumpall命令
pg_dumpall备份一个给定集簇中的每一个数据库，并且也保留了集簇范围的数据，如角色和表空间定义。
pg_dump只备份数据库集群中的某个数据库的数据，它不会导出角色和表空间相关的信息，因为这些信息是整个数据库集群共用的，不属于某个单独的数据库。pg_dumpall，对集簇中的每个数据库调用pg_dump来完成该工作,还会还转储对所有数据库公用的全局对象（pg_dump不保存这些对象）。
pg_dumpall工作时会发出命令重新创建角色、表空间和空数据库，接着为每一个数据库pg_dump。这意味着每个数据库自身是一致的，但是不同数据库的快照并不同步。

集簇范围的数据可以使用pg_dumpall的–globals-only选项来单独转储。

建议 :

建议每天对角色和表空间定义等全局对象进行备份，但不建议每天使用pg_dumpall来备份全库数据，因为pg_dumpall仅支持导出为SQL文本格式，而使用这种庞大的SQL文本备份来进行全库级别的数据库恢复时及其耗时的，所以一般只建议使用pg_dumpall来备份全局对象而非全库数据。

pg_dumpall可实现仅备份角色和表空间定义：
pg_dumpall -h localhost -U postgres --port=5432 -f myglobals.sql --globals-only

cat myglobals.sql

--
-- PostgreSQL database cluster dump
--

SET default_transaction_read_only = off;

SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;

--
-- Roles
--

CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;
CREATE ROLE test;
ALTER ROLE test WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE user01;
ALTER ROLE user01 WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;

--
-- PostgreSQL database cluster dump complete

如果仅需备份角色定义而无需备份表空间，添加--roles-only选项：
pg_dumpall -h localhost -U postgres --port=5432 -f myroles.sql --roles-only  14版本没有

-bash-4.2$ pg_dumpall -h localhost -U postgres --port=5432 -f myroles.sql --roles-only 
pg_dumpall: unrecognized option '--roles-only '
Try "pg_dumpall --help" for more information.

---------------------------------------------------------------

#!/bin/bash
#stgreSQL备份用户
postgreSQL_user="root"
#stgreSQL备份用户密码及pg环境变量
export PGPASSWORD=123
export PGDATA=/mnt/pgsql/data/postgresql/data
export PGHOME=/mnt/pgsql/data/postgresql
export PATH=$PGHOME/bin:$PATH
#数据库地址
postgreSQL_host="127.0.0.1"
#端口号
postgreSQL_port="5432"
#postgreSQL编码
postgreSQL_charset="utf8"
#要备份的数据库名称，多个用空格分开隔开 如("db1" "db2" "db3")
backup_db_arr=("db_test")
#备份数据存放位置，末尾请不要带"/",此项可以保持默认，程序会自动创建文件夹
backup_location=/home/db_bak
#是否开启过期备份删除 ON为开启 OFF为关闭
expire_backup_delete="ON"
#过期时间天数 默认为三十天，此项只有在expire_backup_delete开启时有效
expire_days=30
#定义备份详细时间
backup_time=`date +%Y%m%d%H%M`
#定义备份目录中的年月日时间
backup_Ymd=`date +%Y-%m-%d`
#30天之前的日期
backup_30ago=`date -d '30 days ago' +%Y-%m-%d`
#备份文件夹全路径
backup_dir=$backup_location/$backup_Ymd
#欢迎语
welcome_msg="Welcome to use PostgreSQL backup tools!"
postgreSQL_ps=`ps -ef |grep PostgreSQL|wc -l`
postgreSQL_listen=`netstat -an |grep LISTEN |grep $postgreSQL_port|wc -l`
if [ [$postgreSQL_ps == 0] -o [$postgreSQL_listen == 0] ]; then
        echo "ERROR:postgreSQL is not running! backup stop!"
        exit
else
        echo $welcome_msg
fi
psql -h $postgreSQL_host -p $postgreSQL_port -U $postgreSQL_user -d ${backup_db_arr[0]} <<end
end
flag=`echo $?`
if [ $flag != "0" ]; then
        echo "ERROR:Can't connect postgreSQL server! backup stop!"
        exit
else
        echo "postgreSQL connect ok! Please wait......"
        # 判断有没有定义备份的数据库，如果定义则开始备份，否则退出备份
        if [ "$backup_db_arr" != "" ];then
                #dbnames=$(cut -d ',' -f1-5 $backup_database)
                #echo "arr is (${backup_db_arr[@]})"
                for dbname in ${backup_db_arr[@]}
                do
                        echo "database $dbname backup start..."
                        `mkdir -p $backup_dir`
                        `pg_dump -h $postgreSQL_host -p $postgreSQL_port -U $postgreSQL_user -d dbname -c  | gzip > $backup_dir/$dbname-$backup_time.sql.gz`
                        flag=`echo $?`
                        if [ $flag == "0" ];then
                                echo "database $dbname success backup to $backup_dir/$dbname-$backup_time.sql.gz"
                        else
                                echo "database $dbname backup fail!"
                        fi
                done
        else
                echo "ERROR:No database to backup! backup stop"
                exit
        fi
        # 如果开启了删除过期备份，则进行删除操作
        if [ "$expire_backup_delete" == "ON" -a  "$backup_location" != "" ];then
                 #`find $backup_location/ -type d -o -type f -ctime +$expire_days -exec rm -rf {} \;`
                 `find $backup_location/ -type d -mtime +$expire_days | xargs rm -rf`
                 echo "Expired backup data delete complete!"
        fi
        echo "All database backup success! Thank you!"
        exit
fi
 

1.上为pg备份脚本，目录 /home/db_bak/sqlBackup.sh

2.计划任务

        a.执行命令： crontab -e

        b.编写定时任务：（注意不要用中文，包括空格）

                0 23 * * * /home/db_bak/sqlBackup.sh 

                每天23点指定脚本
 

在使用此脚本的时候有可能遇到错误,不要慌张,用了此命令一定让你健步如飞

 sed -i 's/\r//' 文件名.sh 
 

2. 以下脚本生成bak文件 对postgresql 数据库进行备份 生成bak文件
**

#!/bin/bash
echo "开始执行 PostgreSql 数据库  的备份！"
nowtime=$(date +%Y-%m-%d-%H:%M:%S)   # 时间
export PGPASSWORD=数据库密码
echo "时间：" $nowtime
set timeout 500
/usr/pgsql-9.6/bin/pg_dump --file "/opt/data/文件名-$nowtime.bak" --host "127.0.0.1" --port "5432" --username "postgres" --dbname "数据库名" --verbose --role "postgres" --blobs --encoding "UTF8"
echo "数据库   备份完成！"
exit;

 

 postgresql数据备份与恢复在实际工作中可能会用到，这里记录一下自己整理的备份与恢复的过程，备份一般使用pg_dump来做，但是它备份的结果有两种格式，默认不加-Fc参数，产生的备份文件就是原始的sql，可以直接执行，所以这种恢复，直接运行psql就行。另一种增加了-Fc参数，这里-F表示格式化，它有四个选项c|d|t|p，本别表示定制、目录、tar、明文。而默认就是p，也即是明文，所以最终结果是原始的sql，增加了-Fc参数的备份文件，在进行恢复的时候，需要使用pg_restore命令。

    为了演示上面的两种备份与恢复方式，先准备一个test数据库，里面有一个表xx_user,三条记录：

 
     第一种备份与恢复方式：

D:\tools\pgsql>bin\pg_dump.exe test > test.dump
    在进行恢复的时候，直接运行psql test  < test.dump 

 

  第二种备份与恢复方式

D:\tools\pgsql>bin\pg_dump -Fc -f test2.dump test
 这种方式生成的备份文件，虽然也有sql，但是内容里面包含了一些二进制的东西：

    这种方式的恢复，需要使用pg_restore命令，同样的先删除数据库，然后新建一个数据库实例。

D:\tools\pgsql>bin\pg_restore -d test test2.dump
 
 
     
 

PostgreSQL Dokcer版本备份脚本

cat >> /etc/crontab << EOF
# 定时导出，0 1 * * * 表示每天1点执行一次
0 1 * * * root /opt/pgsql_backup_linux/backup_shell/pgsql-docker-bak-backend.sh
EOF

（2）运行以上命令后，重启crond 使定时任务生效。
service crond restart 
pgsql-docker-bak-backend.sh

#!/bin/bash
# 此脚本适用于备份单用户下多个数据库的备份，使用的是pg_dump自带的压缩备份
# ---------------------------------------------------------------------

### 数据库配置 ###
# pg_dump路径，-i 后面跟的是容器的名称或容器的ID
dump_path="docker exec -i postgres pg_dump -F c"

db_link="host=127.0.0.1 port=5432 user=test password=test"

# 指定要备份的数据库名称，多个用空格分开
dbnames=(test postgres)

### 文件配置 ###
# 备份文件存放路径
bakpath=/opt/pgsql_backup_linux/db_data/
# 目录不存在就创建
mkdir $bakpath -p

# 当前时间
cur_time=$(date '+%Y%m%d%H%M%S')
# 保留多少天的文件
days=30
# 删除指定天数之前的文件
find $bakpath -mtime +$days -name "*.*" -exec rm -rf {} \;

for dbname in ${dbnames[@]};
do
	# 数据库链接，user用户名，password密码
	dblink=$db_link" dbname=$dbname"
	# 文件完整路径
	filepath=$bakpath/$dbname-$cur_time
	echo "开始备份 PostgreSQL ..."
	# 导出文件
	$dump_path "$dblink" > "$filepath.bak"
done