【实验手册】PostgreSQL—PITR实验

 备份恢复

COPY table_name [ ( column_name [, ...] ) ]
   FROM { 'filename' | PROGRAM 'command' | STDIN }
[ [ WITH ] ( option [, ...] ) ]

COPY { table_name [ ( column_name [, ...] )] | ( query ) }
    TO{ 'filename' | PROGRAM 'command' | STDOUT }
    [[ WITH ] ( option [, ...] ) ]

二、逻辑备份与恢复

1. 逻辑备份

示例：
pg_dump --table=tbl -d db
pg_dump --schema=scm -d db


pg_dump --file=postgres.sql --blobs --schema=public --dbname=postgres --host=127.0.0.1  --port=5432 --username=postgres


psql -h 127.0.0.1 -p 5433 -U postgres -d postgres -f postgres.sql


pg_dump 把一个数据库转储为纯文本文件或者是其它格式.

用法:
  pg_dump [选项]... [数据库名字]




一般选项:
  -f, --file=FILENAME          输出文件或目录名
  -F, --format=c|d|t|p         输出文件格式 (定制, 目录, tar)
                               明文 (默认值))
  -j, --jobs=NUM               执行多个并行任务进行备份转储工作
  -v, --verbose                详细模式
  -V, --version                输出版本信息，然后退出
  -Z, --compress=0-9           被压缩格式的压缩级别
  --lock-wait-timeout=TIMEOUT  在等待表锁超时后操作失败
  --no-sync                    do not wait for changes to be written s
  -?, --help                   显示此帮助, 然后退出




控制输出内容选项:
  -a, --data-only              只转储数据,不包括模式
  -b, --blobs                  在转储中包括大对象
  -B, --no-blobs               exclude large objects in dump
  -c, --clean                  在重新创建之前，先清除（删除）数据库对象
  -C, --create                 在转储中包括命令,以便创建数据库
  -E, --encoding=ENCODING      转储以ENCODING形式编码的数据
  -n, --schema=SCHEMA          只转储指定名称的模式
  -N, --exclude-schema=SCHEMA  不转储已命名的模式
  -o, --oids                   在转储中包括 OID
  -O, --no-owner               在明文格式中, 忽略恢复对象所属者


  -s, --schema-only            只转储模式, 不包括数据
  -S, --superuser=NAME         在明文格式中使用指定的超级用户名
  -t, --table=TABLE            只转储指定名称的表
  -T, --exclude-table=TABLE    不转储指定名称的表
  -x, --no-privileges          不要转储权限 (grant/revoke)
  --binary-upgrade             只能由升级工具使用
  --column-inserts             以带有列名的INSERT命令形式转储数据
  --disable-dollar-quoting     取消美元 (符号) 引号, 使用 SQL 标准引号
  --disable-triggers           在只恢复数据的过程中禁用触发器
  --enable-row-security        启用行安全性（只转储用户能够访问的内容）
  --exclude-table-data=TABLE   不转储指定名称的表中的数据
  --if-exists              当删除对象时使用IF EXISTS
  --inserts                    以INSERT命令，而不是COPY命令的形式转储数
  --no-publications            do not dump publications
  --no-security-labels         不转储安全标签的分配
  --no-subscriptions           do not dump subscriptions
  --no-synchronized-snapshots  在并行工作集中不使用同步快照
  --no-tablespaces             不转储表空间分配信息
  --no-unlogged-table-data     不转储没有日志的表数据
  --quote-all-identifiers      所有标识符加引号，即使不是关键字
  --section=SECTION            备份命名的节 (数据前, 数据, 及 数据后)
  --serializable-deferrable   等到备份可以无异常运行
  --snapshot=SNAPSHOT          为转储使用给定的快照
  --strict-names               要求每个表和/或schema包括模式以匹配至少
  --use-set-session-authorization
                               使用 SESSION AUTHORIZATION 命令代替
                ALTER OWNER 命令来设置所有权




联接选项:
  -d, --dbname=DBNAME       对数据库 DBNAME备份
  -h, --host=主机名        数据库服务器的主机名或套接字目录
  -p, --port=端口号        数据库服务器的端口号
  -U, --username=名字      以指定的数据库用户联接
  -w, --no-password        永远不提示输入口令
  -W, --password           强制口令提示 (自动)
  --role=ROLENAME          在转储前运行SET ROLE




pg_dump仅导出数据库结构：
pg_dump -U TestRole1 -s -f TestDb1.sql TestDb1

备份某个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_payroll.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

参数选项
-data-only              提供没有对象定义的数据转储
-globals-only       备份转储角色和表空间
-clean              包括删除数据库，角色和表空间

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


如果仅需备份角色定义而无需备份表空间，那么可以加上--roles-only选项：
pg_dumpall -h localhost -U postgres --port=5432 -f myroles.sql --roles-only

文本格式的备份文件, 直接使用用户连接到对应的数据库执行备份文本；
psql dbname -f filename
psql dbname -U username < filename


二进制格式的备份文件只能使用pg_restore来还原；可以指定还原的表, 编辑TOC文件, 定制还原的顺序, 表, 索引等；
pg_restore [option] ... [filename]
pg_restore -d dbname bakfile




pg_dump --host=127.0.0.1 --port=5432 --dbname=postgres --schema=public --username=postgres --format=c --encoding=UTF8 --file= file.backup --password --blobs --inserts --column-inserts --table=
pg_restore -U user -d database file. backup>a.txt 2>&1
psql -d database -h IP -p 5432 -U user -f file.sql

逻辑备份恢复示例：
pg_dump备份恢复示例
1）创建数据库
createdb testdb


2）连入数据库testdb
psql testdb1


3）创建测试表，插入数据
testdb=# create table tt(a int) tablespace tbls_t;
testdb=# insert into tt(a) values(1);
testdb=# insert into tt(a) values(2);


4）查看数据
testdb=# select * from tt;


5）备份
pg_dump testdb1>/dbbak/testdb.sql   #简单语法,可结合选项灵活备份
6）删除数据库testdb
dropdb testdb


7）创建新数据库（恢复之前需创建数据库）
createdb testdb1


8）恢复数据
psql testdb1


9）查看数据是否回复
psql testdb
testdb=# select * from tt

不同格式的恢复:
pg_restore dbbak/testdb.dmp|less                 # 可以解析二进制格式的备份文件
pg_restore -l dbbak/testdb.dmp                 #生成二进制备份集合的TOC列表  
pg_restore -d testdb1 dbbak/testdb.dmp        #需要先创建目标库
psql -d testdb2 < dbbak/p.dmp

pg_restore -l -f dbbak/toc1 dbbak/testdb.dmp

pg_restore -Fc -L dbbak/toc -d testdb dbbak/testdb.dmp
-l --list
列出归档的内容的表格。这个操作的输出能被用作-L选项的输入。注意如果把-n或-t这样的过滤开关与-l一起使用，它们将会限制列出的项。
-f filename
--file=filename
为生成的脚本指定输出文件，或在与-l选项一起使用时为列表指定输出文件。为 stdout用 -。

pg_dump testdb| gzip > dbbak/testdbbak.sql.gz
gunzip -c dbbak/testdbbak.sql.gz | psql testdb2
pg_dump testdb | psql testdb1

pg_dump -Fd -j4 -f dbbak/db.dir testdb
pg_restore -d testdb3 -j4 dbbak/db.dir
-j参数指定同时几个进程来同时执行，每个进程同时只处理一个表的数据。


使用pg_dump的directory-format   对应选项  -Fd
使用pg_dump的custom-format
-j参数指定并发的数量(job),pg_restore恢复custom-format格式也可以使用此参数,并非只适用directory-format
file toc.dat
cat toc.dat
cat 2866.dat.gz |gunzip
一个表对应一个标号文件
select oid from pg_class where relname='pitr_test';

三、物理备份与恢复

1. 冷备份恢复 

备份示例：tar -jcv -f home/postgres/bak/dbbak0817.tar.bz2 $PGDATA
恢复示例：tar -jxv -f home/postgres/bak/dbbak0817.tar.bz2 -C 

-h 指定连接的数据库的主机名或IP地址，这里就是主库的ip。
-U 指定连接的用户名，此处是我们刚才创建的专门负责流复制的repl用户。
-F 指定了输出的格式，支持p（原样输出）或者t（tar格式输出）。
-x 表示备份开始后，启动另一个流复制连接从主库接收WAL日志。
-P 表示允许在备份过程中实时的打印备份的进度。
-R 表示会在备份结束后自动生成recovery.conf文件，这样也就避免了手动创建。
-D 指定把备份写到哪个目录，这里尤其要注意一点就是做基础备份之前从库的数据目录（$PGDATA）目录需要手动清空。
-l 表示指定一个备份的标识

mkdir -p opt/arch
chown -R postgres:postgres opt/arch

vi $PGDATA/postgresql.conf
archive_mode = on
archive_command = 'DATE=`date +%Y%m%d`; DIR="/opt/arch/$DATE"; (test -d $DIR || mkdir -p $DIR) && cp %p $DIR/%f'
wal_level = replica

checkpoint;       #备注1
select pg_switch_wal();


[root@pg01 20211124]# pwd
/opt/arch/20211124
[root@hgdb01 20211124]# ls
000000020000000000000003  000000020000000000000004

create role repl nosuperuser replication login connection limit 32 encrypted password '111111';

------
host replication repl 0.0.0.0/0 md5
------

pg_ctl reload  #执行加载配置的命令
mkdir `date +%F` ;
pg_basebackup -Ft -v -P -D home/postgres/bak/`date +%F` -h IP地址 -p 5432 -U repl

[postgres@hgdb01 ~]$ cd home/postgres/bak/2021-11-24
postgres@pg01-> ll
total 115M
-rw-rw-r-- 1 postgres postgres 99M Nov 24 03:04 base.tar
-rw------- 1 postgres postgres 17M Nov 24 03:04 pg_wal.tar
[postgres@hgdb01 2021-11-24]$  tar -tvf base.tar |less       #查看备份包内容




backup_label
pg_start_backup，会生成 backup_label文件。pg_stop_backup，会删除backup_label文件。
而如果StartupXLOG函数运行时，发现了backup_label文件，那么意味着它处正在从online backup中恢复的过程中。

1）停止数据库并删除数据目录，将pg_basebackup生成的备份包分别解压到相应目录
[root@pg01 2021-11-24]# tar -xvf base.tar -C opt/pg_root
[root@pg01 2021-11-24]# tar -xvf pg_wal.tar -C opt/pg_root/pg_wal


2）recovery.conf文件配置还原参数
$cp $PGHOME/share/recovery.conf.sample $PGDATA/recovery.conf
vi $PGDATA/recovery.conf   #备注
restore_command = 'cp opt/arch/20211124/%f %p'
recovery_target_timeline = 'latest'


3）启动数据库并做数据查看验证是否恢复完成
pg_ctl start

select pg_start_backup(now()::text);
backup_label
pg_start_backup，会生成 backup_label文件。pg_stop_backup，会删除backup_label文件。
而如果StartupXLOG函数运行时，发现了backup_label文件，那么意味着它处正在从online backup中恢复的过程中。




------
 pg_start_backup
-----------------
 0/E000028
(1 row)

select pg_is_in_backup();
 pg_is_in_backup
-----------------
 t
(1 row)




也可以通过$PGDATA目录下生成的标签文件查看备份信息
cat $PGDATA/backup_label
------
START WAL LOCATION: 0/E000028 (file 00000002000000000000000E)
CHECKPOINT LOCATION: 0/E000060
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2018-01-17 11:17:18 CST
LABEL: 2018-01-17 11:17:18.151543+08

使用操作系统工具比如 tar 或 cp -ra 等，或直接把数据目录复制到备份位置。过程中既不需要关闭数据库，也不需要停止数据库的任何操作。
tar -jcv -f ~/bak/pgdata.tar.bz2 $PGDATA
注意手动备份表空间路径，即目录的pg_tblspc软连接指向目录。
tar -jcv -f ~/bak/dbbak/tbls.tar.bz2 /pgtbls/tbls01

select pg_stop_backup();
这将终止备份模式并自动切换到下一个 WAL 段。

vi $PGDATA/postgresql.conf
archive_mode = on
archive_command = 'DATE=`date +%Y%m%d`; DIR="/opt/arch/$DATE"; (test -d $DIR || mkdir -p $DIR) && cp %p $DIR/%f'
wal_level = replica

pg_basebackup -D /home/postgres/bak/ -Ft -P -R -Upostgres

create table t_rec (id int,time timestamp);
insert into t_rec values (1,now());
insert into t_rec values (2,now());
insert into t_rec values (3,now());
insert into t_rec values (4,now());
insert into t_rec values (5,now());
select pg_switch_wal();
checkpoint;
select * from t_rec;

tar -xvf base.tar -C /opt/pg_root
tar -xvf pg_wal.tar -C /opt/pg_root/pg_wal
[root@pg01 opt]# chmod 0700 /opt/pg_root

cp /opt/pgsql/share/recovery.conf.sample /opt/pg_root/recovery.conf

restore_command = 'cp /opt/arch/20211124/%f %p'
recovery_target_time = '2021-11-24 04:28:27.860105'

%p 表示wal文件名$PGDATA的相对路径, 如pg_wal/00000001000000190000007D
%f 表示wal文件名, 如00000001000000190000007D

select pg_wal_replay_resume();


pg_wal_replay_resume()       void       如果恢复被暂停，重启之（默认仅限于超级用户，但是可以授予其他用户 EXECUTE 特权来执行该函数）。




postgres=# show archive_mode ;
archive_mode
--------------
on
(1 row)
postgres=# show archive_command ;
archive_command
-----------------------------
cp %p /home/postgres/archive/%f
(1 row)


pg_basebackup -D /home/postgres/bak/ -Ft -P -R -Upostgres
postgres=# create table t_rec (id int,time timestamp);
CREATE TABLE
postgres=# insert into t_rec values (1,now());
INSERT 0 1
postgres=# insert into t_rec values (2,now());

postgres=# show archive_mode ;
archive_mode
--------------
on
(1 row)
postgres=# show archive_command ;
archive_command
-----------------------------
cp %p /home/postgres/archive/%f
(1 row)
pg_basebackup -D /home/postgres/bak/ -Ft -P -R -Upostgres
postgres=# create table t_rec (id int,time timestamp);
CREATE TABLE
postgres=# insert into t_rec values (1,now());
INSERT 0 1
postgres=# insert into t_rec values (2,now());

INSERT 0 1
postgres=# insert into t_rec values (3,now());
INSERT 0 1
postgres=# select pg_switch_wal();
pg_switch_wal
---------------
0/1E0141B8
(1 row)
postgres=# insert into t_rec values (4,now());
INSERT 0 1
postgres=# insert into t_rec values (5,now());
INSERT 0 1
postgres=# select * from t_rec ;
id | time
----+----------------------------
1 | 2020-05-29 17:28:03.347296
2 | 2020-05-29 17:28:07.951033
3 | 2020-05-29 17:28:14.955285
4 | 2020-05-29 17:29:12.431425
5 | 2020-05-29 17:29:22.629481
(5 rows)

cp /home/postgres/psql/share/recovery.conf.sample
/home/postgres/pgdata/recovery.conf

restore_command = 'cp /home/postgres/archive/%f %p'
recovery_target_time = '2020-05-29 17:29:20.431425 +08'

select pg_wal_replay_resume();