postgresql基于备份点PITR恢复
实验目的：
01、基于备份点直接恢复数据库
02、基于备份点后续增量wal日志恢复到特定的时间点
实验环境：
centos7
postgresql9.5
01、安装postgresql9.5
注意：源码编译操作性更加强，也可以rpm安装
02、初始化数据库及创建归档目录
su - postgres
[postgres@lab-210 ~]$ mkdir archivedir
[postgres@lab-210 ~]$ initdb -D data1 -E utf-8
03、修改postgresql配置
开启归档
tee <<-'EOF' >>data1/postgresql.auto.conf
listen_addresses = '*'
port = 5432
wal_level = hot_standby
max_wal_senders = 2
archive_mode = on
archive_command = 'cp %p /home/postgres/archivedir/%f'
logging_collector = on
EOF
添加host认证
tee <<-'EOF' >>data1/pg_hba.conf
local replication repl trust
host replication repl 172.24.0.0/16 trust
EOF
04、启动数据，添加repl账户
[postgres@lab-210 ~]$ pg_ctl -D data1/ start
server starting
[postgres@lab-210 ~]$ LOG: redirecting log output to logging collector process
HINT: Future log output will appear in directory "pg_log".
[postgres@lab-210 ~]$
[postgres@lab-210 ~]$ ss -lnt |grep 5432
LISTEN 0 128 *:5432 *:*
LISTEN 0 128 :::5432 :::*
postgres=# create user repl with password '123123' replication login;
CREATE ROLE
postgres=#
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
repl | Replication | {}
05、创建基线备份
postgres=# create table tab1(uuid int);
CREATE TABLE
postgres=# insert into tab1 values (1),(100);
INSERT 0 2
postgres=# select * from tab1;
uuid
------
1
100
(2 rows)
mkdir data2 //plain形式
[postgres@lab-210 ~]$ pg_basebackup -h 172.24.0.210 -p 5432 -U repl -Fp -Xs -Pv -D data2/
mkdir base //压缩的形式
[postgres@lab-210 ~]$ pg_basebackup -h 172.24.0.210 -p 5432 -U repl -Ft -Pv -D base/
06、新增数据
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | tab1 | table | postgres
(1 row)
postgres=# create table tab2(uuid int);
CREATE TABLE
postgres=# insert into tab2 select generate_series(1,100);
INSERT 0 100
postgres=#
postgres=# select count(1) from tab2;
count
-------
100
(1 row)
postgres=# select pg_switch_xlog();
pg_switch_xlog
----------------
0/A018090
(1 row)
[postgres@lab-210 ~]$ pg_ctl -D data1/ stop
waiting for server to shut down.... done
server stopped
07、基于创建点直接恢复
[postgres@lab-210 base]$ cat backup_label //创建备份点的时间戳记录
START WAL LOCATION: 0/9000028 (file 000000010000000000000009)
CHECKPOINT LOCATION: 0/9000060
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2019-11-02 19:11:40 UTC
LABEL: pg_basebackup base backup
[postgres@lab-210 base]$ cat recovery.conf //恢复
restore_command = 'cp /home/postgres/archivedir/%f "%p"'
recovery_target_time='2019-11-02 19:11:40 UTC'
[postgres@lab-210 base]$ ll pg_xlog/ //基于pg_basearchive备份xlog已经处理了
total 0
drwx------. 2 postgres postgres 6 Nov 2 19:11 archive_status
[postgres@lab-210 ~]$ chmod 700 base
[postgres@lab-210 ~]$ pg_ctl -D base start
server starting
[postgres@lab-210 ~]$ cat base/pg_log/postgresql-2019-11-02_192416.log
LOG: database system was interrupted; last known up at 2019-11-02 19:11:40 UTC
LOG: starting point-in-time recovery to 2019-11-02 19:11:40+00
LOG: restored log file "000000010000000000000009" from archive
LOG: redo starts at 0/9000028
LOG: consistent recovery state reached at 0/9000130
LOG: restored log file "00000001000000000000000A" from archive
LOG: recovery stopping before commit of transaction 1833, time 2019-11-02 19:13:24.96014+00
LOG: redo done at 0/A013C20
cp: cannot stat ‘/home/postgres/archivedir/00000002.history’: No such file or directory
LOG: selected new timeline ID: 2 //创建新的时间线，开天地
LOG: archive recovery complete
cp: cannot stat ‘/home/postgres/archivedir/00000001.history’: No such file or directory
LOG: MultiXact member wraparound protections are now enabled
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
[postgres@lab-210 ~]$ ll base
total 64
-rw-------. 1 postgres postgres 206 Nov 2 19:11 backup_label.old //备份点文件
......
-rw-rw-r--. 1 postgres postgres 105 Nov 2 19:24 recovery.done //恢复配置文件
验证恢复情况
[postgres@lab-210 ~]$ psql
psql (9.5.19)
Type "help" for help.
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | tab1 | table | postgres
(1 row)
postgres=# select * from tab1;
uuid
------
1
100
(2 rows)
08、基于基线实现全量恢复
注意：基于备份点，还是存在新增的归档日志
[postgres@lab-210 ~]$ mkdir data3
[postgres@lab-210 ~]$ tar xf base.tar -C data3
[postgres@lab-210 data3]$ cat recovery.conf ###默认恢复存储存的归档文件
restore_command = 'cp /home/postgres/archivedir/%f "%p"'
[postgres@lab-210 ~]$ chmod 700 data3
[postgres@lab-210 ~]$ pg_ctl -D data3/ start
[postgres@lab-210 ~]$ psql
psql (9.5.19)
Type "help" for help.
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | tab1 | table | postgres
public | tab2 | table | postgres
(2 rows)
[postgres@lab-210 pg_log]$ cat postgresql-2019-11-02_193834.log
LOG: database system was interrupted; last known up at 2019-11-02 19:11:40 UTC //备份点创建的时间
LOG: starting archive recovery
LOG: restored log file "000000010000000000000009" from archive
LOG: redo starts at 0/9000028
LOG: consistent recovery state reached at 0/9000130
LOG: restored log file "00000001000000000000000A" from archive
LOG: restored log file "00000001000000000000000B" from archive
cp: cannot stat ‘/home/postgres/archivedir/00000001000000000000000C’: No such file or directory
LOG: redo done at 0/B000060
LOG: last completed transaction was at log time 2019-11-02 19:14:49.458612+00 //事务发生的最后时间，恢复的目标点
LOG: restored log file "00000001000000000000000B" from archive
LOG: restored log file "00000002.history" from archive
cp: cannot stat ‘/home/postgres/archivedir/00000003.history’: No such file or directory
LOG: selected new timeline ID: 3
LOG: archive recovery complete //恢复完毕
cp: cannot stat ‘/home/postgres/archivedir/00000001.history’: No such file or directory
LOG: MultiXact member wraparound protections are now enabled
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
PS：清理归档点旧的日志
