合 PG之闪回数据库
误操作恢复的文章参考:
- PG中通过pg_waldump来分析pg_wal日志:https://www.xmmup.com/pgzhongtongguopg_waldumplaifenxipg_walrizhi.html
- PG日志挖掘工具之WalMiner:https://www.xmmup.com/pgrizhiwajuegongjuzhiwalminer.html
- PG工具pg_resetwal介绍:https://www.xmmup.com/postgresql11xiugaiwal-segsizedaxiao.html
在Oracle中,若发生重大的误操作,那么我们可以使用flashback database
命令来把数据库整体闪回到过去的误操作的时间点,当然前提是需要打开数据库的闪回功能。
在PG中,能否也可以执行类型的操作呢,答案是肯定的。
闪回数据库过程示例
误操作恢复的过程命令参考:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | \c lhrdb select pg_switch_wal(); select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn()); create table f_lhr (id int, name varchar(20)); insert into f_lhr (id,name) values (1,'l'); insert into f_lhr (id,name) values (2,'h'); insert into f_lhr (id,name) values (3,'r'); -- 误操作 delete from f_lhr where id =2 ; select * from f_lhr; create table f_lhr2 (id int, name varchar(20)); insert into f_lhr (id,name) values (1,'l'); select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn()); pg_waldump 0000000100000002000000B2 | grep DELETE pg_ctl stop pg_resetwal -x 452490 -D /pg13/pgdata/ pg_ctl start |
1、误操作
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | C:\Users\lhrxxt>psql -U postgres -h192.168.1.35 -p15433 Password for user postgres: psql (13.3) Type "help" for help. postgres=# select now(); now ------------------------------- 2021-09-28 08:48:11.150389+08 (1 row) postgres=# \c lhrdb You are now connected to database "lhrdb" as user "postgres". lhrdb=# lhrdb=# lhrdb=# select pg_switch_wal(); pg_switch_wal --------------- 2/B100C498 (1 row) lhrdb=# select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn()); pg_current_wal_lsn | pg_walfile_name | pg_walfile_name_offset --------------------+--------------------------+------------------------------- 2/B2000060 | 0000000100000002000000B2 | (0000000100000002000000B2,96) (1 row) lhrdb=# create table f_lhr (id int, name varchar(20)); CREATE TABLE lhrdb=# insert into f_lhr (id,name) values (1,'l'); INSERT 0 1 lhrdb=# insert into f_lhr (id,name) values (2,'h'); INSERT 0 1 lhrdb=# insert into f_lhr (id,name) values (3,'r'); INSERT 0 1 lhrdb=# -- 误操作 lhrdb=# delete from f_lhr where id =2 ; DELETE 1 lhrdb=# select * from f_lhr; id | name ----+------ 1 | l 3 | r (2 rows) lhrdb=# create table f_lhr2 (id int, name varchar(20)); CREATE TABLE lhrdb=# insert into f_lhr2 (id,name) values (1,'l'); INSERT 0 1 |