PostgreSQL error:could not read block * in file "base/*/*": read only * of * bytes

  • KM03787890
  • 26-Feb-2021
  • 19-May-2021

This document has not been formally reviewed for accuracy and is provided "as is" for your convenience.

Summary

ERROR: could not read block 24 in file "base/16384/16845": read only 0 of 8192 bytes

Error

In the log under $NNM_DATA/databases/Postgres/pg_log and nnm-trace.log:

ERROR: could not read block 24 in file "base/16384/16845": read only 0 of 8192 bytes

Cause

base/16384/16845: base represents the tablespace, 16385 is the database, and 16845 is the file.

The default tablespace, known as pg_default. On disk, this will be the directory named base underneath the data_directory

nnm=# select oid, * from pg_tablespace
nnm-# ;
 oid  |  spcname   | spcowner | spcacl | spcoptions
------+------------+----------+--------+------------
 1663 | pg_default |       10 |        |
 1664 | pg_global  |       10 |        |
(2 rows)

nnm=# show data_directory;
              data_directory
-------------------------------------------
 /var/opt/OV/shared/nnm/databases/Postgres
(1 row)

# ll /var/opt/OV/shared/nnm/databases/Postgres/base
total 160
drwx------. 2 nmsdbmgr nmsdb  8192 Feb 26 02:04 1
drwx------. 2 nmsdbmgr nmsdb  8192 Feb  4 14:30 13286
drwx------. 2 nmsdbmgr nmsdb  8192 Feb  8 14:36 13287
drwx------. 2 nmsdbmgr nmsdb 69632 Feb 26 02:05 16384

This query below will provide the details of the relation for the given file.

nnm=# select n.nspname AS schema, c.relname AS realtion  from pg_class c inner join pg_namespace n on (c.relnamespace = n.oid) where c.relfilenode = ‘16845’  ;

To check the sanity of the table do a pg_dump of the table nms_disco_fdb  to which the index belongs, as pg_dump doesn’t use any indexes but reads the table data directly.

/opt/OV/nonOV/Postgres/bin/pg_dump -U postgres -d nnm -v  -t nms_disco_fdb > backup_pgbench_accounts.sql >> backup_nms_disco_fdb.log 2>&1

Now open the file backup_nms_disco_fdb.log  and check if there are entries. Which should indicate that the table data is fine and we have issues with index.
 

 

Fix

To do a vaccumdb and reindex.

1)    Take a backup using the nnmbackupemd.ovpl . We are executing this so that we have a backup of the database.
/opt/OV/bin/nnmbackupembdb.ovpl -target /tmp/nnmdbbackup
2)    Then vaccumdb followed by reindex
/opt/OV//nonOV/Postgres/bin/vacuumdb -U postgres --full --verbose --analyze --all > /tmp/nnmVacuumOutput.txt 2>&1
/opt/OV/nonOV/Postgres/bin/reindexdb -U postgres -d nnm

In case the issue is not resolved after performing the steps mentioned above then collect
•    the database backup using /opt/OV/bin/nnmbackupembdb.ovpl -target /tmp/nnmdbbackup
•     file backup of /var/opt/OV/shared/nnm/databases/Postgres/
•    /opt/OV/bin/nnmbackup.ovpl -target /tmp/nnmbackup
and then we will restore this in Lab and try reproducing the issue.