postgres 基于基础备份的恢复操作 (二)

[postgres@kafka01 pgdata]$ pg_ctl -D /data/pgdata/ -l log.log start
waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.

--发现是起不来的,那缺少什么呢

 

01,环境模拟

postgres=# create table test20200605 (id int,name varchar(4));
CREATE TABLE
postgres=# insert into test20200605 values (1,'xx');
INSERT 0 1
postgres=# insert into test20200605 values (1,'ww');
INSERT 0 1
postgres=# select * from test20200605
postgres-# ;
id|name
1|xx
1|ww
(2 rows)
postgres=# select pg_switch_wal();  --日志转写
pg_switch_wal
1/F80165F8
(1 row)

创建基础备份
pg_basebackup

[postgres@kafka01 data]$ pg_basebackup -Ft -Pv -Xf -z -Z5 -p 5432 -D /data/backup/
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 1/FC000028 on timeline 1
4581748/4581748 kB (100%), 2/2 tablespaces
pg_basebackup: write-ahead log end point: 1/FC000130
pg_basebackup: base backup completed

　　生成新的数据

postgres=# insert into test20200605 values (1,'test');
INSERT 0 1
postgres=# select * from test20200605
postgres-# ;
 id | name
----+------
  1 | xx
  1 | ww
  1 | test
(3 rows)

postgres=#

查看这个时间点

postgres=# select now();
              now
-------------------------------
 2020-06-08 14:30:03.170239+08
(1 row)

　　删除这个表~~

postgres=# drop table test20200605;
DROP TABLE
postgres=#

二,时间点恢复操作

　　01,停数据库

[postgres@kafka01 data]$ /data/postgres/bin/pg_ctl stop -D /data/pgdata -mi
waiting for server to shut down.... done
server stopped

　　02,移除就数据,使用新数据

[postgres@kafka01 data]$ mkdir old_20200608
[postgres@kafka01 data]$ mv kingle_tbs/ old_20200608/
[postgres@kafka01 data]$ mv pgdata/ old_20200608/
[postgres@kafka01 data]$ ll
total 5171524
-rw-rw-r-- 1 postgres postgres 5295636480 Jun  8 14:03 20200608postgre.tar.gz
drwxrwxr-x 2 postgres postgres       4096 Jun  8 14:26 archive
drwxrwxr-x 2 postgres postgres         45 Jun  8 14:26 backup
drwxrwxr-x 4 postgres postgres         38 Jun  8 14:35 old_20200608
drwxrwxr-x 8 postgres postgres         81 Apr 21 14:32 postgres
drwxrwxr-x 2 postgres postgres         54 Apr 21 15:00 psql
[postgres@kafka01 data]$ mkdir kingle_tbs pgdata -p

[postgres@kafka01 data]$ chmod 0700  kingle_tbs pgdata

　　03,使用旧备份恢复到对应的目录下

[postgres@kafka01 data]$ tar xf backup/16386.tar.gz -C kingle_tbs/
--pg_basebackup 生成的表空间备份
[postgres@kafka01 data]$ tar xf backup/base.tar.gz -C pgdata/
[postgres@kafka01 data]$
---pg_basebackup 生成的元数据备份

　　04,配置recover.conf

[postgres@kafka01 data]$ cp postgres/share/recovery.conf.sample pgdata/
--一般再安装目录上有
[postgres@kafka01 pgdata]$ mv recovery.conf.sample recovery.conf
[postgres@kafka01 pgdata]$ chmod 0600 recovery.conf
[postgres@kafka01 pgdata]$ vim recovery.conf

--修改两个参数即可
restore_command = 'cp /data/archive/%f %p'              # e.g. 'cp /mnt/server/archivedir/%f %p'
--这个对应的是你的旧归档在哪里, 然后你的命令就对应这拷贝什么
recovery_target_timeline = 'latest' --恢复最近的最新的

#recovery_target_name = ''      # e.g. 'daily backup 2011-01-26'
#
recovery_target_time = '2020-06-08 14:30:03.170239+08'      # e.g. '2004-07-14 22:39:00 EST'
#
#recovery_target_xid = ''
#
#recovery_target_lsn = ''       # e.g. '0/70006B8'
#
#recovery_target_inclusive = true
#
#
# Alternatively, you can request stopping as soon as a consistent state
# is reached, by uncommenting this option.
#
#recovery_target = 'immediate'
#
#
# If you want to recover into a timeline other than the "main line" shown in
# pg_control, specify the timeline number here, or write 'latest' to get
# the latest branch for which there's a history file.
#
#recovery_target_timeline = 'latest'


-- 每一个恢复策略都支持,时间 事务等等都在这个文件配置,详细配置见上一个文章

05,启动数据库 然后同步观测日志情况

[postgres@kafka01 pgdata]$ pg_ctl -D /data/pgdata/ -l log.log start
waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.

可以查看日志:
[postgres@kafka01 pgdata]$ cat log.log
2020-06-08 15:11:45.193 CST [24524] FATAL:  data directory "/data/pgdata" has group or world access
2020-06-08 15:11:45.193 CST [24524] DETAIL:  Permissions should be u=rwx (0700).
[postgres@kafka01 pgdata]$ ls


发现是权限问题我们重新设置下权限然后启动

[postgres@kafka01 data]$ chmod  0700 pgdata
[postgres@kafka01 data]$ chmod  0700 kingle_tbs

启动数据库即可恢复