Postgresql select error: could not read block, Invalid argument SQL state: XX000

postgresql

Postgres 9.6 on Windows 10

I'm creating a table filtered_table1 via a simple select operation on another table table1 (around 1.8 billion rows) and filtering it by some field

create table filtered_table1  as (
 select * 
    from table1
    where epoch_time >= 1510227000000 and epoch_time <= 1510230600000
);

I get the error pasted below on running this query after sometime.

ERROR:  could not read block 4585496 in file "base/16393/1949151.34": Invalid argument
SQL state: XX000

I followed some suggestions especially the one given here where it was suggested to try setting zero_damaged_pages = on in postgresql.conf file. I continue to get this error even after doing this. Request help on finding and fixing this issue. Note: I have other tables in the same database and do not see such error while working with those.

Best Answer

I hope my post won't sound too rude, but you have corruptions issues and are trying rather desperate measures to fix it. Did you have a look at the documentation regarding zero_damaged_pages ? Make it a habit of looking at the official documentation for such configuration:

Setting zero_damaged_pages to on causes the system to instead report a warning, zero out the damaged page in memory, and continue processing. This behavior will destroy data

Please have a look at https://wiki.postgresql.org/wiki/Corruption first. As stated on the wiki, you need to do a file system backup, and work on a copy. If you do not deeply understand what commands and configurations like zero_damaged_pages, pg_reset_xlog, ... do, save yourself a LOT of troubles and avoid using them.

But all is not grey and horrible... If you want a safe way out, do a restore from a safe backup (because you do have backups), and as Christopher Pettus said on one of his talks on PostgreSQL corruption, receive the praise of a grateful nation.

Related Question