Postgresql自动备份

经过一番测试验证，现将Postgresql自动备份的主要脚本和定时任务设置进行记录，以方便必要时候可借鉴使用，现初始约定如下：

　　PG版本号=13

脚本执行目录:

　　F:\PostgreBackup\

备份文件目录

　　F:\PostgreBackup\Fullbackup

一、基本操作

1.脚本准备

@ECHO OFF
@setlocal enableextensions
@cd /d "%~dp0"
 
SET PGPATH=C:\"Program Files"\PostgreSQL\13\bin\
SET SVPATH=F:\PostgreBackup\FullBackup\

SET DBUSR=postgres
SET PGPASSWORD=shty@2020

SET PRJDB=""
SET PRJDB1=HYLTunnelGis
SET PRJDB2=HYLTunnelNacos
SET PRJDB3=hyltunneljid

@ECHO OFF
FOR /F "TOKENS=1,2,3 DELIMS=/ " %%i IN ('DATE /T') DO SET d=%%i-%%j-%%k
FOR /F "TOKENS=1,2,3 DELIMS=: " %%i IN ('TIME /T') DO SET t=%%i-%%j-%%k

::backup-first-db 
echo BackupBegin
@ECHO OFF
SET PRJDB=%PRJDB1%
SET DBDUMP=%PRJDB%_%d%_%t%.backup
echo %PRJDB%
%PGPATH%pg_dump -h localhost -p 5432  -U %DBUSR% --disable-triggers   -c  -b -v  -d %PRJDB% -f %SVPATH%%DBDUMP%
echo BackupComplete 

echo %SVPATH%%DBDUMP%

::backup-secend-db
echo BackupBegin
@ECHO OFF
SET PRJDB=%PRJDB2%
SET DBDUMP=%PRJDB%_%d%_%t%.backup
echo %PRJDB%
%PGPATH%pg_dump -h localhost -p 5432  -U %DBUSR% --disable-triggers   -c  -b -v  -d %PRJDB% -f %SVPATH%%DBDUMP%
echo BackupComplete 

echo %SVPATH%%DBDUMP%

::backup-third-db
echo BackupBegin
@ECHO OFF
SET PRJDB=%PRJDB3%
SET DBDUMP=%PRJDB%_%d%_%t%.backup
echo %PRJDB%
%PGPATH%pg_dump -h localhost -p 5432  -U %DBUSR% --disable-triggers   -c  -b -v  -d %PRJDB% -f %SVPATH%%DBDUMP%
echo BackupComplete 

echo %SVPATH%%DBDUMP%

2.任务准备

2.1左侧新建PG13文件夹

2.2右侧新建基本任务

控制面板-任务计划程序-新建基本任务

2.3执行验证

删除备份文件存储目录，右键任务开始开始

 二、备份常用参数

-h host，指定数据库主机名，或者IP
-p port，指定端口号
-U user，指定连接使用的用户名
-W，按提示输入密码
dbname，指定连接的数据库名称，实际上也是要备份的数据库名称。
-a，–data-only，只导出数据，不导出表结构
-c，–clean，是否生成清理该数据库对象的语句，比如drop table
-C，–create，是否输出一条创建数据库语句
-f file，–file=file，输出到指定文件中
-n schema，–schema=schema，只转存匹配schema的模式内容
-N schema，–exclude-schema=schema，不转存匹配schema的模式内容
-O，–no-owner，不设置导出对象的所有权
-s，–schema-only，只导致对象定义模式，不导出数据
-t table，–table=table，只转存匹配到的表，视图，序列，可以使用多个-t匹配多个表
-T table，–exclude-table=table，不转存匹配到的表。
–inserts，使用insert命令形式导出数据，这种方式比默认的copy方式慢很多，但是可用于将数据导入到非PostgreSQL数据库。

备份常用命令

备份某个database，备份结果以自定义压缩格式输出：
pg_dump -h localhost -p 5432 -U someuser -F c -b -v -f mydb.backup mydb　　
备份某个database，备份结果以SQL文本格式输出，输出结果要包含CREATE DATABASE 语句：
pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb
备份某个database中所有名称以"pay"开头的表，备份结果以自定义压缩格式输出：
pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *.pay* -f pay.backup mydb　　
备份某个database中hr和payroll这两个schema中的所有数据，备份结果以自定义压缩格式输出：
pg_dump -h localhost -p 5432 -U someuser -F c -b -v -n hr -n payroll -f hr.backup mydb
备份某个database中除public schema中的数据以外的所有数据，备份结果以自定义压缩格式输出：
pg_dump -h localhost -p 5432 -U someuser -F c -b -v -N public -f all_sch_except_pub.backup mydb
将数据备份为SQL文本文件，且生成INSERT语句，该文件可以用于低版本的PostgreSQL或其它支持SQL的非PostgreSQL数据库
pg_dump -h localhost -p 5432 -U someuser -F p --column-inserts -f select_tables.backup mydb
目录备份格式：备份时会先创建一个新目录，然后逐一将一个gzip格式的压缩文件和一个列出所有包含结构的文件填充到该目录。
pg_dump -h localhost -p 5432 -U someuser -F d -f /root/test/ mydb
支持使用备份选项 --jobs（-j），会启动多个线程并行执行当前备份操作
pg_dump -h localhost -p 5432 -U someuser -j 4 -F d -f /root/test/ mydb
 使用pg_dumpall进行全库备份

pg_dumpall不支持导出SQL文件以外的其他格式
pg_dumpall可以将当前所有database数据导出为SQL文本
可以同时导出表空间定义和角色等全局对象
建议每天对角色和表空间定义等全局对象进行备份，但不建议每天使用pg_dumpall来备份全库数据

三、还原常用命令

# pg_restore --help
-d, --dbname=名字 连接数据库名字
-f, --file=文件名 输出文件名
-F, --format=c|d|t backup file format (should be automatic)
-l, --list 打印归档文件的 TOC 概述
-v, --verbose 详细模式
--help 显示此帮助信息, 然后退出
--version 输出版本信息, 然后退出恢复控制选项:
-a, --data-only 只恢复数据, 不包括模式
-c, --clean 在重新创建数据库对象之前需要清除（删除）数据库对象
-C, --create 创建目标数据库
-e, --exit-on-error 发生错误退出, 默认为继续
-I, --index=名称 恢复指定名称的索引
-j, --jobs=NUM 可以执行多个任务并行进行恢复工作
-L, --use-list=文件名 从这个文件中使用指定的内容表排序输出
-n, --schema=NAME 在这个模式中只恢复对象
-O, --no-owner 忽略恢复对象所属者
-P, --function=名字(参数) 恢复指定名字的函数
-s, --schema-only 只恢复模式, 不包括数据
-S, --superuser=NAME 使用指定的超级用户来禁用触发器
-t, --table=NAME 恢复指定命字的表
-T, --trigger=NAME 恢复指定命字的触发器
-x, --no-privileges 跳过处理权限的恢复 (grant/revoke)
-1, --single-transaction 作为单个事务恢复
--disable-triggers 在只恢复数据的过程中禁用触发器
--no-data-for-failed-tables 没有恢复无法创建表的数据
--no-security-labels do not restore security labels
--no-tablespaces 不恢复表空间的分配信息
--use-set-session-authorization 使用 SESSION AUTHORIZATION 命令代替ALTER OWNER命令来设置对象所有权
联接选项:
-h, --host=主机名 数据库服务器的主机名或套接字目录
-p, --port=端口号 数据库服务器的端口号
-U, --username=名字 以指定的数据库用户联接
-w, --no-password 永远不提示输入口令
-W, --password 强制口令提示 (自动)
--role=ROLENAME 在恢复前执行SET ROLE操作

还原常用命令

pg_dump -U postgres -Fc TestDb1 >TestDb1.dump