Re: Urgent !!!! Tables inaccessible postgres v17.6
Re: Urgent !!!! Tables inaccessible postgres v17.6
Hi Ron,
Thanks for the quick response.
Unfortunately, I do have backups, but they're also reporting the same checksum error.
full backup: 20260801-203001F
timestamp start/stop: 2026-08-01 20:30:01-07 / 2026-08-01 21:30:23-07
wal start/stop: 00000001000007F900000055 / 000000010000080800000030
database size: 1403.4GB, database backup size: 1403.4GB
repo1: backup set size: 197.7GB, backup size: 197.7GB
diff backup: 20260801-203001F_20260802-023002D
timestamp start/stop: 2026-08-02 02:30:02-07 / 2026-08-02 02:35:44-07
wal start/stop: 000000010000086A000000B6 / 000000010000086A000000B6
database size: 1407.7GB, database backup size: 221.5GB
repo1: backup set size: 198.8GB, backup size: 28.6GB
backup reference total: 1 full
error(s) detected during backup
Our backup strategy is weekly full backups with daily incremental backups.
The database is hosted on Azure Premium SSD v2, using four striped disks.
How serious does this look to you? Do you have any recommendations on the fastest way to recover from this? At the moment, I'm concerned that both the production copy and the backups appear to be affected.
On Thu, Aug 6, 2026 at 10:11 AM mahamood hussain <hussain.ieg@gmail.com> wrote:Hi Team,
I need some urgent help. I'm unable to access one of the tables. Even a simple
SELECTstatement fails with the error below. Could someone please help investigate and fix this issue?prod=# SELECT count(*) FROM schema.tablename; WARNING: page verification failed, calculated checksum 50897 but expected 50048 ERROR: invalid page in block 696770 of relation base/16388/447758 CONTEXT: parallel workerAny assistance would be greatly appreciated. Thanks!
* Have you tested your backup/restore process lately?* How old is the latest backup?* Do the system logs show any errors around that time?* What kind of disks do you have? One might be dying.--Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!
Re: Urgent !!!! Tables inaccessible postgres v17.6
Thanks for the suggestion. I confirmed that base/16388/447758 is the heap file for table, so unfortunately it isn't an index that can simply be rebuilt.
heap_size | index_size | total_size
-----------+------------+------------
11 GB | 5694 MB | 17 GB
I'm fairly new to PostgreSQL, so could you provide a bit more context on the binary search approach you mentioned? I'd appreciate it if you could explain the steps involved and how it helps identify the records on the corrupted page.
Regarding your last point, do you think this corruption could be related to the PostgreSQL version we're currently running? Is this a known issue that has been fixed in PostgreSQL 17.10, or are you recommending the upgrade simply because we're not on the latest minor release?
Also, could you help me understand what typically causes page corruption like this? Are there common root causes, and what best practices or preventive measures would you recommend to minimize the risk of this happening in the future?
Hmm.If base/16388/447758 is an index, then you could just drop it and recreate it, but you're probably not that lucky.How big is the table? Using a binary search method with ORDER BY and OFFSET, you can find the records on the offending page. Exclude them when doing a COPY TO, then recreate the table.And, of course, upgrade to 17.10.On Thu, Aug 6, 2026 at 10:27 AM mahamood hussain <hussain.ieg@gmail.com> wrote:Hi Ron,
Thanks for the quick response.
Unfortunately, I do have backups, but they're also reporting the same checksum error.
full backup: 20260801-203001F
timestamp start/stop: 2026-08-01 20:30:01-07 / 2026-08-01 21:30:23-07
wal start/stop: 00000001000007F900000055 / 000000010000080800000030
database size: 1403.4GB, database backup size: 1403.4GB
repo1: backup set size: 197.7GB, backup size: 197.7GB
diff backup: 20260801-203001F_20260802-023002D
timestamp start/stop: 2026-08-02 02:30:02-07 / 2026-08-02 02:35:44-07
wal start/stop: 000000010000086A000000B6 / 000000010000086A000000B6
database size: 1407.7GB, database backup size: 221.5GB
repo1: backup set size: 198.8GB, backup size: 28.6GB
backup reference total: 1 full
error(s) detected during backupOur backup strategy is weekly full backups with daily incremental backups.
The database is hosted on Azure Premium SSD v2, using four striped disks.
How serious does this look to you? Do you have any recommendations on the fastest way to recover from this? At the moment, I'm concerned that both the production copy and the backups appear to be affected.
On Thu, Aug 6, 2026 at 7:47 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:On Thu, Aug 6, 2026 at 10:11 AM mahamood hussain <hussain.ieg@gmail.com> wrote:Hi Team,
I need some urgent help. I'm unable to access one of the tables. Even a simple
SELECTstatement fails with the error below. Could someone please help investigate and fix this issue?prod=# SELECT count(*) FROM schema.tablename; WARNING: page verification failed, calculated checksum 50897 but expected 50048 ERROR: invalid page in block 696770 of relation base/16388/447758 CONTEXT: parallel workerAny assistance would be greatly appreciated. Thanks!
* Have you tested your backup/restore process lately?* How old is the latest backup?* Do the system logs show any errors around that time?* What kind of disks do you have? One might be dying.--Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!--Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!
Re: Urgent !!!! Tables inaccessible postgres v17.6
On Thu, 2026-08-06 at 19:57 +0530, mahamood hussain wrote: > The database is hosted on Azure Premium SSD v2, using four striped disks. > > > > > > prod=# SELECT count(*) FROM schema.tablename; > > > > > > WARNING: page verification failed, calculated checksum 50897 but expected 50048 > > > ERROR: invalid page in block 696770 of relation base/16388/447758 Looks like Microsoft's storage is not reliable. Yours, Laurenz Albe
Re: Urgent !!!! Tables inaccessible postgres v17.6
FROM pg_class
WHERE pg_relation_filepath(oid) LIKE '%447758%';
On Thu, 2026-08-06 at 19:57 +0530, mahamood hussain wrote:
> The database is hosted on Azure Premium SSD v2, using four striped disks.
>
> >
> > > prod=# SELECT count(*) FROM schema.tablename;
> > >
> > > WARNING: page verification failed, calculated checksum 50897 but expected 50048
> > > ERROR: invalid page in block 696770 of relation base/16388/447758
Looks like Microsoft's storage is not reliable.
Yours,
Laurenz Albe
Re: Urgent !!!! Tables inaccessible postgres v17.6
Hi,
We were using Premium SSD v2, which Microsoft states provides 99.999999999% durability. However, given the corruption issue we encountered, I’m not fully confident in the storage layer.
The corrupted page was a heap/table page, not an index page.
For recovery, I restored the database backup on a temporary server and performed a roll-forward until it caught up with production. I then exported the recovered table data, imported it into production, and replaced the corrupted table with the recovered copy.
I have not dropped the original corrupted table yet. I have kept it renamed for now so that we can investigate the corruption further and try some of the other recommended recovery methods before removing it.
Have you try to check these blocks are table related or index related?You are counting, could be a index corrupted.Try this:SELECT relname, relkind
FROM pg_class
WHERE pg_relation_filepath(oid) LIKE '%447758%';Licio MatosEm sex., 7 de ago. de 2026 às 18:56, Laurenz Albe <laurenz.albe@cybertec.at> escreveu:On Thu, 2026-08-06 at 19:57 +0530, mahamood hussain wrote:
> The database is hosted on Azure Premium SSD v2, using four striped disks.
>
> >
> > > prod=# SELECT count(*) FROM schema.tablename;
> > >
> > > WARNING: page verification failed, calculated checksum 50897 but expected 50048
> > > ERROR: invalid page in block 696770 of relation base/16388/447758
Looks like Microsoft's storage is not reliable.
Yours,
Laurenz Albe