postgresql的时间点还原。

实验目的，模拟假设每天凌晨2点做一次数据库的完全备份，但是在第二天上午10点的时候出现故障，凌晨2点到上午10点之间产生的数据不丢失，可以正常恢复 。

先确保主数据库postgresql.conf 配置文件中，开启了归档备份功能

 在主数据库上建测试数据 

新建一个数据库

postgres=# create database test1;
CREATE DATABASE

在备份服务上对主数据库进行完全备份 

[postgres@localhost ~]$pg_basebackup -D /backup/ -h 10.0.0.7 -Ft -Pv -U postgres -p 5432 -R 

在主数据库上继续写数据 

postgres=# create database test1;
CREATE DATABASE
postgres=# \c test1 
You are now connected to database "test1" as user "postgres".
test1=# create table t1(id int);
CREATE TABLE
test1=# insert into t1 values(1);
INSERT 0 1
test1=# 

模拟出现故障，删库！！！！！！！

db1=# drop database test1 ;
DROP DATABASE
db1=# 

发现故障，停止用户访问 

查看日志 事务ID   当前的日志编号 000000010000000000000009    事务ID754

db1=# select pg_walfile_name(pg_current_wal_lsn());
     pg_walfile_name      
--------------------------
 000000010000000000000009
(1 row)

db1=# select txid_current();
 txid_current 
--------------
          754

在主数据库上刷新日志 

db1=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/90192B0
(1 row)

故障还原

在要还原的服务器上还原数据   停服 清空dada和archive目录  还原刚刚完全备份的数据 

[postgres@localhost ~]$pg_ctl stop
waiting for server to shut down.... done
server stopped

[root@localhost ~]#rm -rf  /pgsql/data/*
[root@localhost ~]#rm -rf  /archive/*

[root@localhost ~]#tar xf /backup/base.tar -C /pgsql/data/
[root@localhost ~]#tar xf /backup/pg_wal.tar -C /archive/

复制主数据库的最新的归档日志到备份数据上

[root@localhost ~]#rsync -a 10.0.0.7:/archive /archive/
root@10.0.0.7's password: 

查看看最新的归档日志 定位到故障语句的lsn编号是753  有drop dir 

pg_waldump /archive/archive/000000010000000000000009 

 修改备份数据库的postgres.conf配置文件

restore_command = 'cp /archive/%f %p'      

recovery_target_xid = '752'     表示指定到故障操作语句之前的一个点的编号故障操作时753

启动数据库 验证数据 

[postgres@localhost ~]$pg_waldump /archive/archive/000000010000000000000009 ^C
[postgres@localhost ~]$pg_ctl start
waiting for server to start....2022-11-20 11:47:55.568 CST [37069] LOG:  redirecting log output to logging collector process
2022-11-20 11:47:55.568 CST [37069] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@localhost ~]$psql
psql (14.6)
Type "help" for help.

postgres=# lc
postgres-# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 db1       | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 db2       | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 hellodb   | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test1     | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
(7 rows)

postgres-# \c test1 
You are now connected to database "test1" as user "postgres".
test1-# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t1   | table | postgres
(1 row)

test1-# 

最后恢复数据之后 数据库时只读状态 需要切换状态   才可以正常写数据 

postgres=# select pg_wal_replay_resume();
 pg_wal_replay_resume 
----------------------
 
(1 row)