PolarDB开源数据库进阶课8 任意时间点恢复(PITR)

postgres=# create table tbl_digoal (id int, info text, ts timestamp);  
CREATE TABLE  
postgres=# insert into tbl_digoal select generate_series(1,1000), md5(random()::text), clock_timestamp();  
INSERT 0 1000  
postgres=# select * from tbl_digoal limit 5;  
 id |               info               |             ts               
----+----------------------------------+----------------------------  
  1 | 45085479f931ea4bb0c39ec73522cdb4 | 2024-12-19 16:27:37.748317  
  2 | afc44037b7fac9feaa8d3567f331d0db | 2024-12-19 16:27:37.750813  
  3 | 37bcea4e172f13590c5bce35fc965880 | 2024-12-19 16:27:37.750821  
  4 | 0a09b7c73a8994d5023b796cb7aa7e13 | 2024-12-19 16:27:37.750824  
  5 | 37b98e2e52ddd1c6287ceb8624d7bf0a | 2024-12-19 16:27:37.750826  
(5 rows)  
  
postgres=# select sum(hashtext(t::text)) from tbl_digoal t;  
     sum        
--------------  
 -71405674348  
(1 row)

postgres=# select now();  
              now                
-------------------------------  
 2024-12-19 16:28:27.517757+08  
(1 row)  
  
postgres=# select pg_create_restore_point('2024-12-19 16:28:27.517757+08');  
 pg_create_restore_point   
-------------------------  
 2/8003E6C0  
(1 row)

postgres=# delete from tbl_digoal where id=10;  
DELETE 1  
postgres=# update tbl_digoal set info='new' where id=1;  
UPDATE 1  
postgres=# select sum(hashtext(t::text)) from tbl_digoal t;  
     sum        
--------------  
 -70843053399  
(1 row)

postgres=# select pg_current_wal_lsn(),* from pg_stat_replication where application_name='pg_receivewal';  
-[ RECORD 1 ]------+------------------------------  
pg_current_wal_lsn | 2/8003E9A0  
pid                | 5255  
usesysid           | 10  
usename            | postgres  
application_name   | pg_receivewal  
client_addr        | 172.17.0.5  
client_hostname    |   
client_port        | 58302  
backend_start      | 2024-12-19 16:32:26.754692+08  
backend_xmin       |   
state              | streaming  
sent_lsn           | 2/8003E9A0  
write_lsn          | 2/8003E9A0  
flush_lsn          | 2/8003E9A0  
replay_lsn         |   
write_lag          | 00:00:00.002957  
flush_lag          | 00:00:00.002957  
replay_lag         | 00:00:00.002957  
sync_priority      | 0  
sync_state         | async  
reply_time         | 2024-12-19 16:32:26.762024+08

mkdir /data/recovery  
cp -r /data/backup/primary /data/recovery/  
cp -r /data/backup/shared_data /data/recovery/

cd /data/recovery/primary    
  
vi postgresql.conf    
    
# 修改和注释如下
# 使用本地盘模拟共享存储的, 使用如下配置. 未来如果你想用zfs来搭建standby, 可以参考这份配置   
polar_disk_name='home'      
polar_datadir='file-dio:///data/recovery/shared_data'      
polar_vfs.localfs_mode=true    
# polar_storage_cluster_name='disk'     
  
# 增加如下, 恢复目标是前面创建的恢复点, 拷贝wal的命令
# 也可以恢复到指定时间、wal lsn位置, 详情可参考文末文章. 
restore_command = 'cp /data/polardb_wal_archive/%f %p || cp /data/polardb_wal_archive/%f.partial %p'  
recovery_target_timeline = latest   
recovery_target_name = '2024-12-19 16:28:27.517757+08'  
recovery_target_action = 'pause'   
  
# 其他配置保持不变即可    
huge_pages=off   
port=5432    
polar_hostid=1    
polar_enable_shared_storage_mode=on    
shared_preload_libraries='$libdir/polar_vfs,$libdir/polar_worker'    
logging_collector=on    
log_line_prefix='%p\t%r\t%u\t%m\t'    
log_directory='pg_log'    
listen_addresses='0.0.0.0'    
max_connections=200
# 下面几个参数解决replica不能promote的问题, 因为RO依赖logindex.
polar_logindex_mem_size=64MB
polar_xlog_queue_buffers=64MB
polar_xlog_page_buffers=64MB
# 使用pfs时可以关掉 full page write 和 polar_has_partial_write , 否则请打开这两 
full_page_writes = off
polar_has_partial_write = off
polar_resource_manager.enable_resource_manager=off
# 纯粹的PolarDB 单机版(不使用pfs接口), 不需要剥离shared dir,
# 参考: 202501/20250121_01.md

rm /data/recovery/primary/standby.signal   
rm /data/recovery/shared_data/standby.signal   
  
touch /data/recovery/primary/recovery.signal  
# 理论上signal标记文件只需要放在本地数据目录中. shared data目录不需要signal文件, 删掉即可.

pg_ctl start -D /data/recovery/primary

cd /data/recovery/primary/pg_log  
  
less postgresql-2024-12-19_165344_error.log  
  
4279                    2024-12-19 16:53:47.757 CST     LOG:  recovery stopping at restore point "2024-12-19 16:28:27.517757+08", time 2024-12-19 16:28:34.938012+08  
4279                    2024-12-19 16:53:47.757 CST     LOG:  pausing at the end of recovery  
4279                    2024-12-19 16:53:47.757 CST     HINT:  Execute pg_wal_replay_resume() to promote.

$ psql  
psql (PostgreSQL 15.10 (PolarDB 15.10.2.0 build d4f5477d debug) on aarch64-linux-gnu)  
Type "help" for help.  
  
postgres=# select count(*) from tbl_digoal ;  
 count   
-------  
  1000  
(1 row)  
  
postgres=# select sum(hashtext(t::text)) from tbl_digoal t;  
     sum        
--------------  
 -71405674348  
(1 row)

$ pg_ctl stop -m fast -D /data/recovery/primary