postgresql-模拟丢失数据文件
postgresql-模拟丢失数据文件
环境准备
--创建测试表
postgres=# create table test (n_bh int4 primary key,c_name varchar(300));
CREATE TABLE
Time: 1162.555 ms
--插入数据
postgres=# insert into test select generate_series(1,100000),'zhangsan';
INSERT 0 100000
Time: 2181.978 ms
--查看文件路径
postgres=# select pg_relation_filepath('test');
pg_relation_filepath
----------------------
base/13287/3983070
(1 row)
Time: 0.611 ms
--查看索引
postgres=# select schemaname,tablename,indexname,indexdef from pg_indexes where tablename = 'test';
schemaname | tablename | indexname | indexdef
------------+-----------+-----------+---------------------------------------------------------------
--
public | test | test_pkey | CREATE UNIQUE INDEX test_pkey ON public.test USING btree (n_bh
)
(1 row)
Time: 2.076 ms
--查看索引路径
postgres=# select pg_relation_filepath('test_pkey');
pg_relation_filepath
----------------------
base/13287/3983073
(1 row)
Time: 0.655 ms
--查看所对应文件
[root@sqlfx 13287]# ll
total 13987
...
-rw-------. 1 postgres postgres 4431872 Oct 21 15:38 3983070
-rw-------. 1 postgres postgres 24576 Oct 21 15:38 3983070_fsm
-rw-------. 1 postgres postgres 2260992 Oct 21 15:38 3983073
to
vm还没生成，如果是无日志表可能还有init文件
索引文件丢失
--1.索引文件丢失
[postgres@sqlfx 13287]$ touch 3983073bak
[postgres@sqlfx 13287]$ mv 3983073 3983073bak
[postgres@sqlfx 13287]$ ll
-rw-------. 1 postgres postgres 4431872 Oct 21 15:41 3983070
-rw-------. 1 postgres postgres 24576 Oct 21 15:38 3983070_fsm
-rw-------. 1 postgres postgres 2260992 Oct 21 15:41 3983073bak
--2.删除索引文件后在没有重启数据库前还是可以访问的，这应该是从缓存获取的
postgres=# select pg_relation_filepath('test_pkey');
pg_relation_filepath
----------------------
base/13287/3983073
(1 row)
Time: 0.655 ms
--3.插入一条数据
postgres=# insert into test values(200000,'lisi');
INSERT 0 1
Time: 253.284 ms
--4.也能够访问
postgres=# select * from test where n_bh = 200000;
n_bh | c_name
--------+--------
200000 | lisi
(1 row)
Time: 0.803 ms
--5.查询也可以正常走索引
postgres=# explain analyze select * from test where n_bh = 200000;
QUERY PLAN
----------------------------------------------------------------------------------------------------
--------------
Index Scan using test_pkey on test (cost=0.28..8.30 rows=1 width=520) (actual time=0.019..0.019 ro
ws=1 loops=1)
Index Cond: (n_bh = 200000)
Planning Time: 0.133 ms
Execution Time: 0.095 ms
(4 rows)
Time: 0.873 ms
--6.但是在执行checkpint的时候就会失败
postgres=# checkpoint;
错误: 检查点请求失败
HINT: 详细信息请参考服务器日志.
Time: 301.294 ms
--7.这个时候查询仍然可以走索引
postgres=# explain analyze select * from test where n_bh = 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Index Scan using test_pkey on test (cost=0.28..8.30 rows=1 width=520) (actual time=0.022..0.023 ro
ws=1 loops=1)
Index Cond: (n_bh = 1)
Planning Time: 0.150 ms
Execution Time: 0.105 ms
(4 rows)
Time: 0.998 ms
--8.查看数据库日志报错，日志文件中大量的报错
错误 58P01 无法打开文件 "base/13287/3983073": 没有那个文件或目录 写入关系base/13287/3983073的块275
警告 58030 无法写入base/13287/3983073的块275 多次失败 --- 写错误可能是永久性的
--9.关闭数据库
background worker "logical replication launcher" (PID 23073) 已退出, 退出代码 1
正在关闭
无法打开文件 "base/13287/3983073": 没有那个文件或目录
无法写入base/13287/3983073的块275
检查点(checkpointer)进程 (PID 23068) 已退出, 退出代码 1
中断任何其它已激活的服务器进程
归档进程 (PID 23071) 已退出, 退出代码 1
数据库系统异常关闭
数据库系统已关闭
--10.重启数据库后访问
postgres=# select * from test where n_bh = 200000;
错误: 索引"test_pkey"在块0上包含未期望的零页
HINT: 请重建索引 (REINDEX).
--11.重建索引后可以继续访问
postgres=# reindex index test_pkey;
REINDEX
postgres=# select * from test where n_bh = 200000;
n_bh | c_name
--------+--------
200000 | lisi
(1 row)
索引文件丢失，由于数据文件存在可以重建，所以问题不大
fsm文件丢失
[postgres@sqlfx 13287]$ touch 3983070_fsmbak
[postgres@sqlfx 13287]$ mv 3983070_fsm 3983070_fsmbak
执行vacuum或重启数据库后fsm文件又会重新生成
mv文件丢失
postgres=# update test set c_name ='lisi' where n_bh > 10000;
UPDATE 90001
postgres=# vacuum test;
VACUUM
[postgres@sqlfx 13287]$ ll
total 24840
...
-rw-------. 1 postgres postgres 8421376 Oct 21 16:04 3983070
-rw-------. 1 postgres postgres 24576 Oct 21 16:01 3983070_fsmbak
-rw-------. 1 postgres postgres 8192 Oct 21 16:08 3983070_vm
-rw-------. 1 postgres postgres 2260992 Oct 21 15:41 3983073bak
-rw-------. 1 postgres postgres 6307840 Oct 21 16:04 3991251
[postgres@sqlfx 13287]$ touch 3983070_vm_bak
[postgres@sqlfx 13287]$ mv 3983070_vm 3983070_vm_bak
[postgres@sqlfx 13287]$ ll
...
-rw-------. 1 postgres postgres 8421376 Oct 21 16:10 3983070
-rw-------. 1 postgres postgres 24576 Oct 21 16:10 3983070_fsm
-rw-------. 1 postgres postgres 24576 Oct 21 16:01 3983070_fsmbak
-rw-------. 1 postgres postgres 8192 Oct 21 16:10 3983070_vm_bak
-rw-------. 1 postgres postgres 2260992 Oct 21 15:41 3983073bak
重启后vm也正常生成了
fsm和vm丢失后，执行vacuum也会恢复，执行更新表里面的数据也会重新生成。
数据文件丢失
--模拟数据丢失
[postgres@sqlfx 13287]$ touch 3983070bak
[postgres@sqlfx 13287]$ mv 3983070 3983070bak
--执行查询
postgres=# select * from test limit 10;
致命错误: 由于管理员命令中断联接
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
--再次执行
postgres=# select * from test limit 10;
错误: 无法打开文件 "base/13287/3983070": 没有那个文件或目录
--pg_class中还能查到
postgres=# select oid,relname from pg_class where relname = 'test';
oid | relname
---------+---------
3983070 | test
(1 row)
--可以看到直接删除文件，还没有清理
postgres=# select relid,relname,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze from pg_stat_all_tables where relname= 'test';
relid | relname | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze
---------+---------+-------------+-----------------+--------------+------------------
3983070 | test | | | |
(1 row)
--尝试重启试试
postgres=# select oid,relname from pg_class where relname = 'test';
oid | relname
---------+---------
3983070 | test
(1 row)
postgres=# select relid,relname,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze from pg_stat_all_tables where relname= 'test';
relid | relname | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze
---------+---------+-------------+-----------------+--------------+------------------
3983070 | test | | | |
(1 row)
postgres=# select schemaname,tablename,indexname,indexdef from pg_indexes where tablename = 'test';
schemaname | tablename | indexname | indexdef
------------+-----------+-----------+---------------------------------------------------------------
--
public | test | test_pkey | CREATE UNIQUE INDEX test_pkey ON public.test USING btree (n_bh
)
(1 row)
--表字段
postgres=# select attname,atttypid from pg_attribute where attrelid = '3983070';
attname | atttypid
----------+----------
tableoid | 26
cmax | 29
xmax | 28
cmin | 29
xmin | 28
ctid | 27
n_bh | 23
c_name | 1043
(8 rows)
postgres=# select * from test limit 10;
错误: 无法打开文件 "base/13287/3983070": 没有那个文件或目录
索引文件也还在，表的定义也还在，但是文件丢失了
单个表文件丢失，不会影响数据库启动，但是访问表的时候就会出现问题，这种物理删除文件是没法恢复的，不会记录日志，只能做好备份
pg_wal日志丢失
--丢失wal，启动数据库报错
日志 0 数据库上次关闭时间为 2019-10-28 04:06:35 EDT
日志 0 正在创建丢失的WAL目录"pg_wal/archive_status"
日志 0 无效的主 checkpoint 记录
比致命错误还过分的错误 XX000 无法找到一个有效的 checkpoint 记录
致命错误 57P03 数据库系统启动中
XX000:内部错误（INTERNAL ERROR）
--重置数据文件：
[postgres@sqlfx bin]$ ./pg_resetwal -f /home/postgres/abdata/6.0/abase1
Write-ahead log reset
--重置后数据库可以正常启动
--可以看到还原了最后一个wal日志文件
[postgres@sqlfx bin]$ cd /home/postgres/abdata/6.0/abase1/pg_wal
[postgres@sqlfx pg_wal]$ ll
total 16384
-rw-------. 1 postgres postgres 16777216 Oct 28 16:33 000000010000001500000063
drwx------. 2 postgres postgres 6 Oct 28 16:06 archive_status
[postgres@sqlfx pg_wal]$
删除所有wal文件，然后正常停止(fast模式)数据库，再启动数据库发现开始插入的数据还在（说明正常停止的时候数据从缓存中写入了磁盘）
如果是kill掉进程（Immediate模式）那么开始插入的数据实际上是没有写入数据文件的，再次启动的时候找不到wal日志恢复，所以会丢失这部分数据
--删除控制文件后，要先启动才能生成pg_wal/archive_status，不然直接执行pg-resetwal会直接报错：
pg_resetwal: could not open directory "pg_wal/archive_status": No such file or directory
如果缺失wal文件，启动数据库会和以前缺少license一样一直生成core.xx这样的core文件，大小和shared_buffers一样大。
pg_xact日志丢失
--先关闭数据库，然后删除pg_xact日志，再启动数据库
--如果正常关闭数据库会生成一个pg_xact日志，可以kill一个数据库，然后删掉就能复现
数据库上次关闭时间为 2019-10-28 06:07:59 EDT
无法处理事物 12207234 的状态 无法打开文件 "pg_xact/000B": 没有那个文件或目录
启动进程 (PID 32291) 已退出, 退出代码 1
由于启动进程失败, 终止启动
数据库系统已关闭
--将开始的备份文件000B还原到pg_xact下面，可以启动数据库，但是在建表的时候报错了
--创建数据库会报错文件不存在，但是在postgres数据库下面是可以创建的
abase=# create table test1(c_bh varchar(300), primary key(c_bh));
错误: 无法处理事物 11002481 的状态
DETAIL: 无法打开文件 "pg_xact/000A": 没有那个文件或目录
abase=# \q
--新建文件，确保创建的文件有权限
[postgres@sqlfx pg_xact]$ dd if=/dev/zero of=/home/postgres/abdata/6.0/abase1/pg_xact/000A bs=256k count=1
1+0 records in
1+0 records out
262144 bytes (262 kB) copied, 0.000834625 s, 314 MB/s
--创建表成功
abase=# create table test1(c_bh varchar(300), primary key(c_bh));
CREATE TABLE
当然这样并不能完全保证所有表都没有问题，最好的做法是将数据库做一个备份，然后重新还原，找到问题表
删除一些表的时候报错：
db_imp=# drop table db_xfzx.t_zfxx;
错误: 试图删除不可见元组
db_imp=# truncate db_xfzx.t_zfxx;
错误: unexpected chunk number 1 (expected 0) for toast value 3172498 in pg_toast_2619
--执行vacuum full后还是报错
db_imp=# vacuum full db_xfzx.t_zfxx;
VACUUM
db_imp=# vacuum (analyze,verbose) db_xfzx.t_zfxx;
信息: 正在清理 (vacuum) "db_xfzx.t_zfxx"
信息: 索引"t_zfxx_pkey"在7609个页中包含了行版本号1055308
......
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
信息: "pg_toast_3172473": 在超出0页的0中找到可删除版本号0, 不可删除的版本号0
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 12207554
有0个未用的项指针。
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 个页面完全为空。
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
信息: 正在分析 "db_xfzx.t_zfxx"
信息: "t_zfxx": 已经扫描了59417页的30000, 包含532769可用的记录和0不能用的记录; 在示例中有30000条记录,估算所有记录为1055185 .
错误: unexpected chunk number 1 (expected 0) for toast value 3172498 in pg_toast_2619
--重建整个表的索引
db_imp=# reindex table db_xfzx.t_zfxx;
--还是报一样的错
db_imp=# select count(*) from db_xfzx.t_zfxx;
错误: missing chunk number 1 for toast value 3172503 in pg_toast_2619
--查看t_zfxx表的toast发现是pg_toast_3172473，并不是pg_toast_2619
db_imp=# select reltoastrelid::regclass from pg_class where relname = 't_zfxx';
reltoastrelid
---------------------------
pg_toast.pg_toast_3172473
(1 row)
db_imp=# select relname,relfilenode,reltoastrelid from pg_class where relname='t_zfxx';
relname | relfilenode | reltoastrelid
---------+-------------+---------------
t_zfxx | 4532733 | 3172476
(1 row)
--原来pg_toast_2619对应的表是pg_statistic，是统计信息表的toast丢失了
db_imp=# select reltoastrelid::regclass,relname from pg_class;
reltoastrelid | relname
---------------------------+-----------------------------------------------
- | i_t_zfxx_c_zybz
- | i_t_zfxx_c_zyjq
pg_toast.pg_toast_3172473 | t_zfxx
pg_toast.pg_toast_2619 | pg_statistic
--再次尝试vacuum full，发现还缺少pg_xact
db_imp=# vacuum (analyze,verbose) pg_statistic;
信息: 正在清理 (vacuum) "pg_catalog.pg_statistic"
错误: 无法处理事物 595 的状态
DETAIL: 无法打开文件 "pg_xact/0000": 没有那个文件或目录
db_imp=# vacuum full pg_statistic;
错误: 无法处理事物 595 的状态
DETAIL: 无法打开文件 "pg_xact/0000": 没有那个文件或目录
--不如做一次全的pg_dump看看缺少那些
[postgres@sqlfx bin]$ ./pg_dumpall -Usa -p 6543 -f /home/postgres/pg_dumpall.dump
pg_dump: [archiver (db)] query failed: 错误: 无法处理事物 582 的状态
LINE 1: SELECT pg_catalog.pg_is_in_recovery()
^
DETAIL: 无法打开文件 "pg_xact/0000": 没有那个文件或目录
pg_dump: [archiver (db)] query was: SELECT pg_catalog.pg_is_in_recovery()
pg_dumpall: pg_dump failed on database "template1", exiting
pg_dump: [archiver (db)] query failed: 错误: 无法处理事物 10445722 的状态
DETAIL: 无法打开文件 "pg_xact/0009": 没有那个文件或目录
在备份过程中报了许多不存在的pg_xact
--创建缺失的文件
[postgres@sqlfx pg_xact]$ dd if=/dev/zero of=/home/postgres/abdata/6.0/abase1/pg_xact/0000 bs=256k count=1
1+0 records in
1+0 records out
262144 bytes (262 kB) copied, 0.000790407 s, 332 MB/s
[postgres@sqlfx pg_xact]$ dd if=/dev/zero of=/home/postgres/abdata/6.0/abase1/pg_xact/0009 bs=256k count=1
1+0 records in
1+0 records out
262144 bytes (262 kB) copied, 0.00077558 s, 338 MB/s
[postgres@sqlfx pg_xact]$ dd if=/dev/zero of=/home/postgres/abdata/6.0/abase1/pg_xact/0006 bs=256k count=1
1+0 records in
1+0 records out
262144 bytes (262 kB) copied, 0.000763672 s, 343 MB/s
[postgres@sqlfx pg_xact]$ dd if=/dev/zero of=/home/postgres/abdata/6.0/abase1/pg_xact/0007 bs=256k count=1
1+0 records in
1+0 records out
262144 bytes (262 kB) copied, 0.000707374 s, 371 MB/s
[postgres@sqlfx pg_xact]$ dd if=/dev/zero of=/home/postgres/abdata/6.0/abase1/pg_xact/0008 bs=256k count=1
1+0 records in
1+0 records out
262144 bytes (262 kB) copied, 0.000749594 s, 350 MB/s
--在尝试vacuum full pg_statistic和t_zfxx
db_imp=# vacuum full pg_statistic;
错误: uncommitted xmin 11359036 from before xid cutoff 12207600 needs to be frozen
目前还是报错，那么现在有两个方案，一是处理上面的报错，二是还原备份出来的数据。先尝试处理上面的错误。
另外能不能重启数据库让数据库自动解决这个问题呢？试了下，重启之后还是报上面的错误，没能解决
--重启后：
db_imp=# vacuum full pg_statistic;
错误: uncommitted xmin 11359036 from before xid cutoff 12207644 needs to be frozen
db_imp=# reindex table pg_statistic;
REINDEX
db_imp=# vacuum freeze pg_statistic;
错误: uncommitted xmin 11359036 from before xid cutoff 12207711 needs to be frozen
--t_zfxx表只能插入不能查询，
db_imp=# select count(*) from db_xfzx.t_zfxx;
错误: missing chunk number 0 for toast value 3172504 in pg_toast_2619
db_imp=# select c_bh from db_xfzx.t_zfxx where c_bh = '123';
错误: missing chunk number 0 for toast value 3172498 in pg_toast_2619
db_imp=# insert into db_xfzx.t_zfxx(c_bh) values('123');
INSERT 0 1
db_imp=# select c_bh from db_xfzx.t_zfxx where c_bh = '123';
错误: missing chunk number 0 for toast value 3172498 in pg_toast_2619
db_imp=#
db_imp=# explain select c_bh from db_xfzx.t_zfxx where c_bh = '123';
错误: missing chunk number 0 for toast value 3172498 in pg_toast_2619
db_imp=# select c_bh from db_xfzx.t_zfxx limit 1;
错误: missing chunk number 0 for toast value 3172498 in pg_toast_2619
--pg_statistic可以查询所有starelid，但是不能vacuum full,因为toast表损坏了
db_imp=# select starelid from pg_statistic limit 1;
starelid
----------
1260
(1 row)
db_imp=# select count(*) from pg_statistic;
count
-------
411
(1 row)
db_imp=# vacuum full pg_statistic;
错误: uncommitted xmin 11359036 from before xid cutoff 12207714 needs to be frozen
尝试跳过错误的行，继续vacuum full
--zero_damaged_pages:意思是说当系统检测到磁盘页损坏，并导致postgresql数据取消当前的事务并提交一份错误报告信息。这个参数是bool类型的，默认是off，意思是系统遇到这类因磁盘、内存等硬件引起的问题就会给出这样一份错误提示，当我们设置为on时，就可以忽略这些错误报告，并擦除掉这些损坏的数据，没受影响的数据还是正常的。
--这个参数有一个严重的坏处，就是会擦除到那些被损坏的数据，好处也有，你可以忽略掉那些错误信息，扫描时跨过那些错误块，使数据库能正常使用。所以为了数据完整性考虑是不建议打开这个参数的，只有当你的数据库真的打不开、宕机了，没有其他希望恢复数据库的时候再去使用这个参数。 我们是在回收大数据的时候出现的如下故障：
db_imp=# alter system set zero_damaged_pages=on;
ALTER SYSTEM
db_imp=# select pg_relaod_conf();
--仍然报错
db_imp=# vacuum freeze pg_statistic;
错误: uncommitted xmin 11359036 from before xid cutoff 12207711 needs to be frozen
zero_damaged_pages：这个参数一般处理这个报错：
invalid page header in block 59640 of relation base/175812/1077620; zeroing out page
如果pg_xact丢失导致系统表数据出现异常，最后只能靠pg_dumpall出来的数据然后还原。
pg_authid系统表数据丢失
pg_authid的用户信息被删除以后，数据库如果退出登录后，再登录就登录不了。
[postgres@sqlfx bin]$ psql -Usa postgres
psql: 致命错误: 角色 "sa" 不存在
--插入数据
[SQL]INSERT INTO pg_authid VALUES ('sa', 't', 't', 't', 't', 't', 't', 't', '-1', 'f', '0', '9', 'md53072dd49bbf18c5cbaae66711066bf14', NULL);
[Err] 错误: permission denied for table pg_authid
测试：
--先备份pg_authid
[postgres@sqlfx ~]$ pg_dump -Usa -d postgres -t pg_authid -p 7543 -f /home/postgres/pg_authid36.dump
[postgres@sqlfx ~]$ psql -Usa -d postgres -p 7543
psql (11.1, server 9.6.4)
Type "help" for help.
--系统表不能被删除
postgres=# drop table pg_authid;
ERROR: permission denied: "pg_authid" is a system catalog
--删除表数据
postgres=# delete from pg_authid;
DELETE 2
postgres=# select * from pg_authid;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | r
olpassword | rolvaliduntil
---------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--
-----------+---------------
(0 rows)
postgres=# \q
--连接失败
[postgres@sqlfx ~]$ psql -Usa -d postgres -p 7543
psql: FATAL: role "sa" does not exist
--删除了用户，psql还原也会失败
[postgres@sqlfx ~]$ psql -Usa -d postgres -p 7543 -f /home/postgres/pg_authid36.dump
psql: FATAL: role "sa" does not exist
--如果像navicat没有退出，那么连接还在，如果退出了，sa就连不上了
--考虑先用单用户模式创建一个用户
[postgres@sqlfx bin]$ ./postgres --single -D /opt/postgres/abdata/3.6/abase1/ postgres
2019-11-01 15:08:46.144 CST - - - - 17854: WARNING: no roles are defined in this database system
2019-11-01 15:08:46.144 CST - - - - 17854: HINT: You should immediately run CREATE USER "postgres" SUPERUSER;.
PostgreSQL stand-alone backend 9.6.4
backend> create user postgres superuser password '123456';
backend> create user sa1 superuser password '123456';
这样创建了两个用户postgres和sa1然后尝试还原备份的表
[postgres@sqlfx bin]$ psql -Usa1 -d postgres -p 7543 -f /home/postgres/pg_authid36.dump
SET
COPY 2
--数据还原
[postgres@sqlfx bin]$ psql -Usa1 postgres -p 7543
psql (11.1, server 9.6.4)
Type "help" for help.
postgres=# select rolname,rolsuper from pg_authid;
rolname | rolsuper
-------------------+----------
thunisot | f
postgres | t
sa1 | t
pg_signal_backend | f
sa | t
(5 rows)
--数据库还没有做自动清理前来恢复删除的数据
当然还有一些路子就是修改数据文件，但是修改数据文件后需要重建索引，因为索引文件不会自动修改。
总结
1.索引文件丢失或者损坏，可以靠重建索引恢复
2.fsm以及mv文件的丢失，重启数据库会自动恢复
3.单个表文件丢失，不会影响数据库启动，但是访问表的时候就会出现问题，这种物理删除文件是没法恢复的，不会记录日志，只能做好备份
4.pg_wal和pg_xact日志丢失可以恢复，但是可能会丢失部分数据
5.系统表pg_authid数据丢失后可以重新创建超级管理员，依靠备份恢复
