0

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.

1

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.