Hi,
I received a question about inconsistent state after crash recovery.
When a table file is broken (or just lost), PostgreSQL can not recover
a whole table, and does not show any notice while recoverying.
I think it means "inconsistent" state.
(1) create a table, and fill records.
(2) process a checkpoint.
(3) fill more records.
(4) force a crash, and delete the table file.
(5) run recovery on restarting.
(6) only records after the checkpoint can be recoverd.
For example, the attached log shows that PostgreSQL can recover
only 1058 records in the table which contains 2000 records
before the crash, and does not tell anything in the server log.
----------------------------------------------------------
insert into t1 values (trim(to_char(generate_series(1,1000),
'00000000000000000000')) );
INSERT 0 1000
select count(*) from t1;
count
-------
1000
(1 row)
checkpoint;
CHECKPOINT
insert into t1 values (trim(to_char(generate_series(1001,2000),
'00000000000000000000')) );
INSERT 0 1000
select count(*) from t1;
count
-------
2000
(1 row)
(delete the table file)
(kill postgres)
(restart postgres with recovery)
select count(*) from t1;
count
-------
1058
(1 row)
----------------------------------------------------------
Is this expected or acceptable?
I think, at least, PostgreSQL should say something about this
situation in the server log, because DBA can not recognize
this situation if no server log exists.
To reproduce it, please check the attached test script.
Any comments?
Regards,
--
Satoshi Nagayasu <snaga@uptime.jp>
Uptime Technologies, LLC. http://www.uptime.jp
