pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16894: PANIC:  WAL contains references to invalid pages
10+ messages / 4 participants
[nested] [flat]

* BUG #16894: PANIC:  WAL contains references to invalid pages
@ 2021-02-24 09:42 PG Bug reporting form <noreply@postgresql.org>
  2021-02-24 12:22 ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Антон Курочкин <antkurochkin@gmail.com>
  0 siblings, 1 reply; 10+ messages in thread

From: PG Bug reporting form @ 2021-02-24 09:42 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: antkurochkin@gmail.com

The following bug has been logged on the website:

Bug reference:      16894
Logged by:          Anton
Email address:      antkurochkin@gmail.com
PostgreSQL version: 9.6.20
Operating system:   Oracle Linux Server 7.6
Description:        

Good day,

We got error when PITR restoring individual databases from a backup using
pgbackrest.

Opened issue in pgbackrest repository:
https://github.com/pgbackrest/pgbackrest/issues/796
It would be great if you would look at this problem. All the information is
in the issue.

Pgbackrest developers offered to provide this information to the postgres
folks.

Turned out that the problem is playing WAL files on databases that are
restored with zero files.
The database files from the backup are restored successfully, but an error
occurs when starting PG and playing WAL:

```
< 2021-02-21 10:34:57.950 UTC > LOG:  starting archive recovery
< 2021-02-21 10:34:58.137 UTC > LOG:  restored log file
"000000010000007E0000007D" from archive
< 2021-02-21 10:34:58.151 UTC > LOG:  redo starts at 7E/7D0090A8
< 2021-02-21 10:34:58.521 UTC > LOG:  restored log file
"000000010000007E0000007E" from archive
< 2021-02-21 10:34:58.711 UTC > LOG:  consistent recovery state reached at
7E/7EF5E478
< 2021-02-21 10:34:58.915 UTC > LOG:  restored log file
"000000010000007E0000007F" from archive
< 2021-02-21 10:34:59.415 UTC > LOG:  restored log file
"000000010000007E00000080" from archive
< 2021-02-21 10:35:01.521 UTC > LOG:  restored log file
"000000010000007E00000081" from archive
< 2021-02-21 10:35:02.110 UTC > LOG:  restored log file
"000000010000007E00000082" from archive
< 2021-02-21 10:35:02.577 UTC > LOG:  restored log file
"000000010000007E00000083" from archive
< 2021-02-21 10:35:03.045 UTC > LOG:  restored log file
"000000010000007E00000084" from archive
< 2021-02-21 10:35:03.544 UTC > LOG:  restored log file
"000000010000007E00000085" from archive
< 2021-02-21 10:35:03.985 UTC > LOG:  restored log file
"000000010000007E00000086" from archive
< 2021-02-21 10:35:05.394 UTC > LOG:  restored log file
"000000010000007E00000087" from archive
< 2021-02-21 10:35:05.945 UTC > LOG:  restored log file
"000000010000007E00000088" from archive
< 2021-02-21 10:35:06.623 UTC > LOG:  restored log file
"000000010000007E00000089" from archive
< 2021-02-21 10:35:07.264 UTC > LOG:  restored log file
"000000010000007E0000008A" from archive
< 2021-02-21 10:35:07.831 UTC > LOG:  restored log file
"000000010000007E0000008B" from archive
< 2021-02-21 10:35:08.318 UTC > LOG:  restored log file
"000000010000007E0000008C" from archive
< 2021-02-21 10:35:08.892 UTC > LOG:  restored log file
"000000010000007E0000008D" from archive
< 2021-02-21 10:35:09.050 UTC > WARNING:  page 22659 of relation
base/17221/17557 is uninitialized
< 2021-02-21 10:35:09.050 UTC > CONTEXT:  xlog redo at 7E/8D9C04C0 for
Heap2/VISIBLE: cutoff xid 810424291 flags 1
< 2021-02-21 10:35:09.050 UTC > PANIC:  WAL contains references to invalid
pages
< 2021-02-21 10:35:09.050 UTC > CONTEXT:  xlog redo at 7E/8D9C04C0 for
Heap2/VISIBLE: cutoff xid 810424291 flags 1
< 2021-02-21 10:35:09.675 UTC > LOG:  startup process (PID 9) was terminated
by signal 6: Aborted
```

In WAL there is only one line for `page 27536`:

```
rmgr: Heap2       len (rec/tot):     59/    59, tx:          0, lsn:
7E/8D9C04C0, prev 7E/8D9BE508, desc: VISIBLE cutoff xid 810424291 flags 1,
blkref #0: rel 1663/17221/17557 fork vm blk 0, blkref #1: rel
1663/17221/17557 blk 22659
```

We tried to find another information in WALs for `xid 810424291` and `blk
22659` - nothing.

A few records before 7E/8D9C04C0 from last checkpoint:

```
rmgr: XLOG        len (rec/tot):    106/   106, tx:          0, lsn:
7E/8D99C4B0, prev 7E/8D99C478, desc: CHECKPOINT_ONLINE redo 7E/8D90D0D8; tli
1; prev tli 1; fpw    true; xid 0:810982162; oid 476718; multi 1; offset 0;
oldest xid 628489828 in DB 16386; oldest multi 1 in DB 17221; oldest/newest
commit timestamp xid: 0/0; oldest    running xid 810982162; online
rmgr: Heap2       len (rec/tot):     57/  7965, tx:          0, lsn:
7E/8D99C520, prev 7E/8D99C4B0, desc: FREEZE_PAGE cutoff xid 760982701
ntuples 3, blkref #0: rel    1663/17221/17557 blk 22557 FPW
rmgr: Heap2       len (rec/tot):     57/  8213, tx:          0, lsn:
7E/8D99E458, prev 7E/8D99C520, desc: FREEZE_PAGE cutoff xid 760982701
ntuples 1, blkref #0: rel    1663/17221/17557 blk 22631 FPW
rmgr: Heap2       len (rec/tot):     57/  8229, tx:          0, lsn:
7E/8D9A0488, prev 7E/8D99E458, desc: FREEZE_PAGE cutoff xid 760982701
ntuples 3, blkref #0: rel    1663/17221/17557 blk 22632 FPW
rmgr: Heap2       len (rec/tot):     57/  8117, tx:          0, lsn:
7E/8D9A24C8, prev 7E/8D9A0488, desc: FREEZE_PAGE cutoff xid 760982701
ntuples 3, blkref #0: rel    1663/17221/17557 blk 22633 FPW
rmgr: Heap2       len (rec/tot):     57/  8093, tx:          0, lsn:
7E/8D9A4498, prev 7E/8D9A24C8, desc: FREEZE_PAGE cutoff xid 760982701
ntuples 1, blkref #0: rel    1663/17221/17557 blk 22634 FPW
rmgr: Heap2       len (rec/tot):     64/  8256, tx:          0, lsn:
7E/8D9A6450, prev 7E/8D9A4498, desc: VISIBLE cutoff xid 0 flags 2, blkref
#0: rel 1663/17221/17557     fork vm blk 0 FPW, blkref #1: rel
1663/17221/17557 blk 22634
rmgr: Heap2       len (rec/tot):     57/  8101, tx:          0, lsn:
7E/8D9A84A8, prev 7E/8D9A6450, desc: FREEZE_PAGE cutoff xid 760982701
ntuples 4, blkref #0: rel    1663/17221/17557 blk 22639 FPW
rmgr: Heap2       len (rec/tot):     57/  8149, tx:          0, lsn:
7E/8D9AA468, prev 7E/8D9A84A8, desc: FREEZE_PAGE cutoff xid 760982701
ntuples 1, blkref #0: rel    1663/17221/17557 blk 22640 FPW
rmgr: Heap2       len (rec/tot):     57/  8213, tx:          0, lsn:
7E/8D9AC458, prev 7E/8D9AA468, desc: FREEZE_PAGE cutoff xid 760982701
ntuples 1, blkref #0: rel    1663/17221/17557 blk 22641 FPW
rmgr: Heap2       len (rec/tot):     57/  8093, tx:          0, lsn:
7E/8D9AE488, prev 7E/8D9AC458, desc: FREEZE_PAGE cutoff xid 760982701
ntuples 4, blkref #0: rel    1663/17221/17557 blk 22642 FPW
rmgr: Heap2       len (rec/tot):     57/  8141, tx:          0, lsn:
7E/8D9B0440, prev 7E/8D9AE488, desc: FREEZE_PAGE cutoff xid 760982701
ntuples 2, blkref #0: rel    1663/17221/17557 blk 22643 FPW
rmgr: Heap2       len (rec/tot):     57/  8125, tx:          0, lsn:
7E/8D9B2428, prev 7E/8D9B0440, desc: FREEZE_PAGE cutoff xid 760982701
ntuples 1, blkref #0: rel    1663/17221/17557 blk 22646 FPW
rmgr: Heap2       len (rec/tot):     57/  8245, tx:          0, lsn:
7E/8D9B4400, prev 7E/8D9B2428, desc: FREEZE_PAGE cutoff xid 760982701
ntuples 3, blkref #0: rel    1663/17221/17557 blk 22648 FPW
rmgr: Heap2       len (rec/tot):     57/  8237, tx:          0, lsn:
7E/8D9B6450, prev 7E/8D9B4400, desc: FREEZE_PAGE cutoff xid 760982701
ntuples 1, blkref #0: rel    1663/17221/17557 blk 22649 FPW
rmgr: Heap2       len (rec/tot):     57/  8221, tx:          0, lsn:
7E/8D9B8498, prev 7E/8D9B6450, desc: FREEZE_PAGE cutoff xid 760982701
ntuples 3, blkref #0: rel    1663/17221/17557 blk 22652 FPW
rmgr: Heap2       len (rec/tot):     57/  8229, tx:          0, lsn:
7E/8D9BA4D0, prev 7E/8D9B8498, desc: FREEZE_PAGE cutoff xid 760982701
ntuples 3, blkref #0: rel    1663/17221/17557 blk 22653 FPW
rmgr: Heap2       len (rec/tot):     57/  8157, tx:          0, lsn:
7E/8D9BC510, prev 7E/8D9BA4D0, desc: FREEZE_PAGE cutoff xid 760982701
ntuples 3, blkref #0: rel    1663/17221/17557 blk 22655 FPW
rmgr: Heap2       len (rec/tot):     57/  8093, tx:          0, lsn:
7E/8D9BE508, prev 7E/8D9BC510, desc: FREEZE_PAGE cutoff xid 760982701
ntuples 1, blkref #0: rel    1663/17221/17557 blk 22657 FPW
rmgr: Heap2       len (rec/tot):     59/    59, tx:          0, lsn:
7E/8D9C04C0, prev 7E/8D9BE508, desc: VISIBLE cutoff xid 810424291 flags 1,
blkref #0: rel 1663/    17221/17557 fork vm blk 0, blkref #1: rel
1663/17221/17557 blk 22659
```

File `base/17221/17557` - is a file for DB, which restored with zero
files.

recovery.conf:

```
restore_command = 'pgbackrest --stanza=foo-db archive-get %f "%p"'
recovery_target_time = '2021-02-20 07:00:00'
recovery_target_action = 'promote'
```



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: BUG #16894: PANIC: WAL contains references to invalid pages
  2021-02-24 09:42 BUG #16894: PANIC:  WAL contains references to invalid pages PG Bug reporting form <noreply@postgresql.org>
@ 2021-02-24 12:22 ` Антон Курочкин <antkurochkin@gmail.com>
  2021-02-25 17:53   ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
  0 siblings, 1 reply; 10+ messages in thread

From: Антон Курочкин @ 2021-02-24 12:22 UTC (permalink / raw)
  To: antkurochkin@gmail.com; pgsql-bugs@lists.postgresql.org

Good day,


There are inaccuracies in the description, correct data (
https://github.com/pgbackrest/pgbackrest/issues/796):


* Restore without using PITR, just from a backup.


* Correct recovery.conf:


restore_command = 'pgbackrest --stanza=foo-db archive-get %f "%p"'

Ср, 24 февр. 2021 г. в 12:43, PG Bug reporting form <noreply@postgresql.org
>:

> The following bug has been logged on the website:
>
> Bug reference:      16894
> Logged by:          Anton
> Email address:      antkurochkin@gmail.com
> PostgreSQL version: 9.6.20
> Operating system:   Oracle Linux Server 7.6
> Description:
>
> Good day,
>
> We got error when PITR restoring individual databases from a backup using
> pgbackrest.
>
> Opened issue in pgbackrest repository:
> https://github.com/pgbackrest/pgbackrest/issues/796
> It would be great if you would look at this problem. All the information is
> in the issue.
>
> Pgbackrest developers offered to provide this information to the postgres
> folks.
>
> Turned out that the problem is playing WAL files on databases that are
> restored with zero files.
> The database files from the backup are restored successfully, but an error
> occurs when starting PG and playing WAL:
>
> ```
> < 2021-02-21 10:34:57.950 UTC > LOG:  starting archive recovery
> < 2021-02-21 10:34:58.137 UTC > LOG:  restored log file
> "000000010000007E0000007D" from archive
> < 2021-02-21 10:34:58.151 UTC > LOG:  redo starts at 7E/7D0090A8
> < 2021-02-21 10:34:58.521 UTC > LOG:  restored log file
> "000000010000007E0000007E" from archive
> < 2021-02-21 10:34:58.711 UTC > LOG:  consistent recovery state reached at
> 7E/7EF5E478
> < 2021-02-21 10:34:58.915 UTC > LOG:  restored log file
> "000000010000007E0000007F" from archive
> < 2021-02-21 10:34:59.415 UTC > LOG:  restored log file
> "000000010000007E00000080" from archive
> < 2021-02-21 10:35:01.521 UTC > LOG:  restored log file
> "000000010000007E00000081" from archive
> < 2021-02-21 10:35:02.110 UTC > LOG:  restored log file
> "000000010000007E00000082" from archive
> < 2021-02-21 10:35:02.577 UTC > LOG:  restored log file
> "000000010000007E00000083" from archive
> < 2021-02-21 10:35:03.045 UTC > LOG:  restored log file
> "000000010000007E00000084" from archive
> < 2021-02-21 10:35:03.544 UTC > LOG:  restored log file
> "000000010000007E00000085" from archive
> < 2021-02-21 10:35:03.985 UTC > LOG:  restored log file
> "000000010000007E00000086" from archive
> < 2021-02-21 10:35:05.394 UTC > LOG:  restored log file
> "000000010000007E00000087" from archive
> < 2021-02-21 10:35:05.945 UTC > LOG:  restored log file
> "000000010000007E00000088" from archive
> < 2021-02-21 10:35:06.623 UTC > LOG:  restored log file
> "000000010000007E00000089" from archive
> < 2021-02-21 10:35:07.264 UTC > LOG:  restored log file
> "000000010000007E0000008A" from archive
> < 2021-02-21 10:35:07.831 UTC > LOG:  restored log file
> "000000010000007E0000008B" from archive
> < 2021-02-21 10:35:08.318 UTC > LOG:  restored log file
> "000000010000007E0000008C" from archive
> < 2021-02-21 10:35:08.892 UTC > LOG:  restored log file
> "000000010000007E0000008D" from archive
> < 2021-02-21 10:35:09.050 UTC > WARNING:  page 22659 of relation
> base/17221/17557 is uninitialized
> < 2021-02-21 10:35:09.050 UTC > CONTEXT:  xlog redo at 7E/8D9C04C0 for
> Heap2/VISIBLE: cutoff xid 810424291 flags 1
> < 2021-02-21 10:35:09.050 UTC > PANIC:  WAL contains references to invalid
> pages
> < 2021-02-21 10:35:09.050 UTC > CONTEXT:  xlog redo at 7E/8D9C04C0 for
> Heap2/VISIBLE: cutoff xid 810424291 flags 1
> < 2021-02-21 10:35:09.675 UTC > LOG:  startup process (PID 9) was
> terminated
> by signal 6: Aborted
> ```
>
> In WAL there is only one line for `page 27536`:
>
> ```
> rmgr: Heap2       len (rec/tot):     59/    59, tx:          0, lsn:
> 7E/8D9C04C0, prev 7E/8D9BE508, desc: VISIBLE cutoff xid 810424291 flags 1,
> blkref #0: rel 1663/17221/17557 fork vm blk 0, blkref #1: rel
> 1663/17221/17557 blk 22659
> ```
>
> We tried to find another information in WALs for `xid 810424291` and `blk
> 22659` - nothing.
>
> A few records before 7E/8D9C04C0 from last checkpoint:
>
> ```
> rmgr: XLOG        len (rec/tot):    106/   106, tx:          0, lsn:
> 7E/8D99C4B0, prev 7E/8D99C478, desc: CHECKPOINT_ONLINE redo 7E/8D90D0D8;
> tli
> 1; prev tli 1; fpw    true; xid 0:810982162; oid 476718; multi 1; offset 0;
> oldest xid 628489828 in DB 16386; oldest multi 1 in DB 17221; oldest/newest
> commit timestamp xid: 0/0; oldest    running xid 810982162; online
> rmgr: Heap2       len (rec/tot):     57/  7965, tx:          0, lsn:
> 7E/8D99C520, prev 7E/8D99C4B0, desc: FREEZE_PAGE cutoff xid 760982701
> ntuples 3, blkref #0: rel    1663/17221/17557 blk 22557 FPW
> rmgr: Heap2       len (rec/tot):     57/  8213, tx:          0, lsn:
> 7E/8D99E458, prev 7E/8D99C520, desc: FREEZE_PAGE cutoff xid 760982701
> ntuples 1, blkref #0: rel    1663/17221/17557 blk 22631 FPW
> rmgr: Heap2       len (rec/tot):     57/  8229, tx:          0, lsn:
> 7E/8D9A0488, prev 7E/8D99E458, desc: FREEZE_PAGE cutoff xid 760982701
> ntuples 3, blkref #0: rel    1663/17221/17557 blk 22632 FPW
> rmgr: Heap2       len (rec/tot):     57/  8117, tx:          0, lsn:
> 7E/8D9A24C8, prev 7E/8D9A0488, desc: FREEZE_PAGE cutoff xid 760982701
> ntuples 3, blkref #0: rel    1663/17221/17557 blk 22633 FPW
> rmgr: Heap2       len (rec/tot):     57/  8093, tx:          0, lsn:
> 7E/8D9A4498, prev 7E/8D9A24C8, desc: FREEZE_PAGE cutoff xid 760982701
> ntuples 1, blkref #0: rel    1663/17221/17557 blk 22634 FPW
> rmgr: Heap2       len (rec/tot):     64/  8256, tx:          0, lsn:
> 7E/8D9A6450, prev 7E/8D9A4498, desc: VISIBLE cutoff xid 0 flags 2, blkref
> #0: rel 1663/17221/17557     fork vm blk 0 FPW, blkref #1: rel
> 1663/17221/17557 blk 22634
> rmgr: Heap2       len (rec/tot):     57/  8101, tx:          0, lsn:
> 7E/8D9A84A8, prev 7E/8D9A6450, desc: FREEZE_PAGE cutoff xid 760982701
> ntuples 4, blkref #0: rel    1663/17221/17557 blk 22639 FPW
> rmgr: Heap2       len (rec/tot):     57/  8149, tx:          0, lsn:
> 7E/8D9AA468, prev 7E/8D9A84A8, desc: FREEZE_PAGE cutoff xid 760982701
> ntuples 1, blkref #0: rel    1663/17221/17557 blk 22640 FPW
> rmgr: Heap2       len (rec/tot):     57/  8213, tx:          0, lsn:
> 7E/8D9AC458, prev 7E/8D9AA468, desc: FREEZE_PAGE cutoff xid 760982701
> ntuples 1, blkref #0: rel    1663/17221/17557 blk 22641 FPW
> rmgr: Heap2       len (rec/tot):     57/  8093, tx:          0, lsn:
> 7E/8D9AE488, prev 7E/8D9AC458, desc: FREEZE_PAGE cutoff xid 760982701
> ntuples 4, blkref #0: rel    1663/17221/17557 blk 22642 FPW
> rmgr: Heap2       len (rec/tot):     57/  8141, tx:          0, lsn:
> 7E/8D9B0440, prev 7E/8D9AE488, desc: FREEZE_PAGE cutoff xid 760982701
> ntuples 2, blkref #0: rel    1663/17221/17557 blk 22643 FPW
> rmgr: Heap2       len (rec/tot):     57/  8125, tx:          0, lsn:
> 7E/8D9B2428, prev 7E/8D9B0440, desc: FREEZE_PAGE cutoff xid 760982701
> ntuples 1, blkref #0: rel    1663/17221/17557 blk 22646 FPW
> rmgr: Heap2       len (rec/tot):     57/  8245, tx:          0, lsn:
> 7E/8D9B4400, prev 7E/8D9B2428, desc: FREEZE_PAGE cutoff xid 760982701
> ntuples 3, blkref #0: rel    1663/17221/17557 blk 22648 FPW
> rmgr: Heap2       len (rec/tot):     57/  8237, tx:          0, lsn:
> 7E/8D9B6450, prev 7E/8D9B4400, desc: FREEZE_PAGE cutoff xid 760982701
> ntuples 1, blkref #0: rel    1663/17221/17557 blk 22649 FPW
> rmgr: Heap2       len (rec/tot):     57/  8221, tx:          0, lsn:
> 7E/8D9B8498, prev 7E/8D9B6450, desc: FREEZE_PAGE cutoff xid 760982701
> ntuples 3, blkref #0: rel    1663/17221/17557 blk 22652 FPW
> rmgr: Heap2       len (rec/tot):     57/  8229, tx:          0, lsn:
> 7E/8D9BA4D0, prev 7E/8D9B8498, desc: FREEZE_PAGE cutoff xid 760982701
> ntuples 3, blkref #0: rel    1663/17221/17557 blk 22653 FPW
> rmgr: Heap2       len (rec/tot):     57/  8157, tx:          0, lsn:
> 7E/8D9BC510, prev 7E/8D9BA4D0, desc: FREEZE_PAGE cutoff xid 760982701
> ntuples 3, blkref #0: rel    1663/17221/17557 blk 22655 FPW
> rmgr: Heap2       len (rec/tot):     57/  8093, tx:          0, lsn:
> 7E/8D9BE508, prev 7E/8D9BC510, desc: FREEZE_PAGE cutoff xid 760982701
> ntuples 1, blkref #0: rel    1663/17221/17557 blk 22657 FPW
> rmgr: Heap2       len (rec/tot):     59/    59, tx:          0, lsn:
> 7E/8D9C04C0, prev 7E/8D9BE508, desc: VISIBLE cutoff xid 810424291 flags 1,
> blkref #0: rel 1663/    17221/17557 fork vm blk 0, blkref #1: rel
> 1663/17221/17557 blk 22659
> ```
>
> File `base/17221/17557` - is a file for DB, which restored with zero
> files.
>
> recovery.conf:
>
> ```
> restore_command = 'pgbackrest --stanza=foo-db archive-get %f "%p"'
> recovery_target_time = '2021-02-20 07:00:00'
> recovery_target_action = 'promote'
> ```
>
> --
С уважением,
Антон

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: BUG #16894: PANIC: WAL contains references to invalid pages
  2021-02-24 09:42 BUG #16894: PANIC:  WAL contains references to invalid pages PG Bug reporting form <noreply@postgresql.org>
  2021-02-24 12:22 ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Антон Курочкин <antkurochkin@gmail.com>
@ 2021-02-25 17:53   ` David Steele <david@pgmasters.net>
  2021-03-01 04:31     ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 10+ messages in thread

From: David Steele @ 2021-02-25 17:53 UTC (permalink / raw)
  To: Антон Курочкин <antkurochkin@gmail.com>; pgsql-bugs@lists.postgresql.org

On 2/24/21 7:22 AM, Антон Курочкин wrote:
> 
>     We got error when PITR restoring individual databases from a backup
>     using
>     pgbackrest.
> 
>     Opened issue in pgbackrest repository:
>     https://github.com/pgbackrest/pgbackrest/issues/796
>     <https://github.com/pgbackrest/pgbackrest/issues/796;
>     It would be great if you would look at this problem. All the
>     information is
>     in the issue.
> 
>     Pgbackrest developers offered to provide this information to the
>     postgres
>     folks.
> 
>     Turned out that the problem is playing WAL files on databases that are
>     restored with zero files.
>     The database files from the backup are restored successfully, but an
>     error
>     occurs when starting PG and playing WAL:

<snip>

 > < 2021-02-21 10:35:09.050 UTC > WARNING:  page 22659 of relation
 > base/17221/17557 is uninitialized
 > < 2021-02-21 10:35:09.050 UTC > CONTEXT:  xlog redo at 7E/8D9C04C0 for
 > Heap2/VISIBLE: cutoff xid 810424291 flags 1
 > < 2021-02-21 10:35:09.050 UTC > PANIC:  WAL contains references to > 
invalid pages
 > < 2021-02-21 10:35:09.050 UTC > CONTEXT:  xlog redo at 7E/8D9C04C0 for
 > Heap2/VISIBLE: cutoff xid 810424291 flags 1

For context, when a user performs selective restore all the relations 
for databases *not* selected are restored as sparse zero files with the 
correct size.  WAL replay will update those relations but they are not 
expected to be consistent, and in fact we make it impossible to logon to 
those databases so the only option is to drop them.

Still, this is a fairly efficient way to get at a single database in a 
cluster that contains hundreds or thousands of databases.

Clearly the methodology is a bit unorthodox, but even so we don't expect 
to see an error here. This is the first time we've gotten a detailed 
analysis of the issue, so I haven't had time to really look into it, but 
perhaps there is a full page write missing? In the case of corruption 
the administrator might zero a page and we expect WAL replay to succeed, 
I think.

Regards,
-- 
-David
david@pgmasters.net





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: BUG #16894: PANIC: WAL contains references to invalid pages
  2021-02-24 09:42 BUG #16894: PANIC:  WAL contains references to invalid pages PG Bug reporting form <noreply@postgresql.org>
  2021-02-24 12:22 ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Антон Курочкин <antkurochkin@gmail.com>
  2021-02-25 17:53   ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
@ 2021-03-01 04:31     ` Michael Paquier <michael@paquier.xyz>
  2021-03-02 12:46       ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
  0 siblings, 1 reply; 10+ messages in thread

From: Michael Paquier @ 2021-03-01 04:31 UTC (permalink / raw)
  To: David Steele <david@pgmasters.net>; +Cc: Антон Курочкин <antkurochkin@gmail.com>; pgsql-bugs@lists.postgresql.org

On Thu, Feb 25, 2021 at 12:53:44PM -0500, David Steele wrote:
> For context, when a user performs selective restore all the relations for
> databases *not* selected are restored as sparse zero files with the correct
> size.  WAL replay will update those relations but they are not expected to
> be consistent, and in fact we make it impossible to logon to those databases
> so the only option is to drop them.

What is a selective restore?  Is that a pgbackrest restore mode from a
full physical backup where only a portion of the database folders are
physically put back into the data folder restored before doing WAL
replay up to the consistent point of the full physical backup?  Then,
you emulate the absence of the files from the other database folders
using files full of zeros but with the same size as the original
backup to trick WAL replay?
--
Michael

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: BUG #16894: PANIC: WAL contains references to invalid pages
  2021-02-24 09:42 BUG #16894: PANIC:  WAL contains references to invalid pages PG Bug reporting form <noreply@postgresql.org>
  2021-02-24 12:22 ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Антон Курочкин <antkurochkin@gmail.com>
  2021-02-25 17:53   ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
  2021-03-01 04:31     ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Michael Paquier <michael@paquier.xyz>
@ 2021-03-02 12:46       ` David Steele <david@pgmasters.net>
  2021-03-03 07:03         ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 10+ messages in thread

From: David Steele @ 2021-03-02 12:46 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Антон Курочкин <antkurochkin@gmail.com>; pgsql-bugs@lists.postgresql.org

Hi Michael,

On 2/28/21 11:31 PM, Michael Paquier wrote:
> On Thu, Feb 25, 2021 at 12:53:44PM -0500, David Steele wrote:
>> For context, when a user performs selective restore all the relations for
>> databases *not* selected are restored as sparse zero files with the correct
>> size.  WAL replay will update those relations but they are not expected to
>> be consistent, and in fact we make it impossible to logon to those databases
>> so the only option is to drop them.
> 
> What is a selective restore?  Is that a pgbackrest restore mode from a
> full physical backup where only a portion of the database folders are
> physically put back into the data folder restored before doing WAL
> replay up to the consistent point of the full physical backup?  Then,
> you emulate the absence of the files from the other database folders
> using files full of zeros but with the same size as the original
> backup to trick WAL replay?

That summarizes the process very well.

My concern here is that we appear to have a partial page write as the 
first write to a page after the backup start checkpoint. At least, 
that's what this error seems to be indicating from my reading of the code.

The question is -- are there valid circumstances under which this might 
be happening or is it a bug?

I have not submitted a bug before this because I am not able to 
reproduce it in a test environment, but perhaps somebody can weigh in on 
whether this behavior seems normal.

Regards,
-- 
-David
david@pgmasters.net





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: BUG #16894: PANIC: WAL contains references to invalid pages
  2021-02-24 09:42 BUG #16894: PANIC:  WAL contains references to invalid pages PG Bug reporting form <noreply@postgresql.org>
  2021-02-24 12:22 ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Антон Курочкин <antkurochkin@gmail.com>
  2021-02-25 17:53   ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
  2021-03-01 04:31     ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Michael Paquier <michael@paquier.xyz>
  2021-03-02 12:46       ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
@ 2021-03-03 07:03         ` Michael Paquier <michael@paquier.xyz>
  2021-03-03 11:49           ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
  0 siblings, 1 reply; 10+ messages in thread

From: Michael Paquier @ 2021-03-03 07:03 UTC (permalink / raw)
  To: David Steele <david@pgmasters.net>; +Cc: Антон Курочкин <antkurochkin@gmail.com>; pgsql-bugs@lists.postgresql.org

On Tue, Mar 02, 2021 at 07:46:21AM -0500, David Steele wrote:
> That summarizes the process very well.

Okay.

> My concern here is that we appear to have a partial page write as the first
> write to a page after the backup start checkpoint. At least, that's what
> this error seems to be indicating from my reading of the code.
> 
> The question is -- are there valid circumstances under which this might be
> happening or is it a bug?

Err, what you are doing here is qualified as unorthodox, but isn't
"unsupported" a better term?  That's basically tricking WAL replay
into doing something it would never have to do for physical backups.
--
Michael

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: BUG #16894: PANIC: WAL contains references to invalid pages
  2021-02-24 09:42 BUG #16894: PANIC:  WAL contains references to invalid pages PG Bug reporting form <noreply@postgresql.org>
  2021-02-24 12:22 ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Антон Курочкин <antkurochkin@gmail.com>
  2021-02-25 17:53   ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
  2021-03-01 04:31     ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Michael Paquier <michael@paquier.xyz>
  2021-03-02 12:46       ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
  2021-03-03 07:03         ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Michael Paquier <michael@paquier.xyz>
@ 2021-03-03 11:49           ` David Steele <david@pgmasters.net>
  2021-03-05 04:32             ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 10+ messages in thread

From: David Steele @ 2021-03-03 11:49 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Антон Курочкин <antkurochkin@gmail.com>; pgsql-bugs@lists.postgresql.org

On 3/3/21 2:03 AM, Michael Paquier wrote:
> On Tue, Mar 02, 2021 at 07:46:21AM -0500, David Steele wrote:
> 
>> My concern here is that we appear to have a partial page write as the first
>> write to a page after the backup start checkpoint. At least, that's what
>> this error seems to be indicating from my reading of the code.
>>
>> The question is -- are there valid circumstances under which this might be
>> happening or is it a bug?
> 
> Err, what you are doing here is qualified as unorthodox, but isn't
> "unsupported" a better term?  That's basically tricking WAL replay
> into doing something it would never have to do for physical backups.

OK, but shouldn't we have a full page write for this page after the 
backup starts, rather than the partial we seem to be seeing here? Is 
there any condition where the full page write would be skipped 
legitimately, or does it point to a problem?

If Postgres is running correctly there is certainly no expectation for 
support of this unusual use case, but I do think that this possibly 
points to an issue in Postgres, which under normal circumstances would 
be very hard to detect.

Put another way, what if this page was torn while being copied during 
the backup? Would this WAL record still be doing the right thing in that 
case?

Regards,
-- 
-David
david@pgmasters.net





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: BUG #16894: PANIC: WAL contains references to invalid pages
  2021-02-24 09:42 BUG #16894: PANIC:  WAL contains references to invalid pages PG Bug reporting form <noreply@postgresql.org>
  2021-02-24 12:22 ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Антон Курочкин <antkurochkin@gmail.com>
  2021-02-25 17:53   ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
  2021-03-01 04:31     ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Michael Paquier <michael@paquier.xyz>
  2021-03-02 12:46       ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
  2021-03-03 07:03         ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Michael Paquier <michael@paquier.xyz>
  2021-03-03 11:49           ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
@ 2021-03-05 04:32             ` Michael Paquier <michael@paquier.xyz>
  2021-03-05 12:55               ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
  0 siblings, 1 reply; 10+ messages in thread

From: Michael Paquier @ 2021-03-05 04:32 UTC (permalink / raw)
  To: David Steele <david@pgmasters.net>; +Cc: Антон Курочкин <antkurochkin@gmail.com>; pgsql-bugs@lists.postgresql.org

On Wed, Mar 03, 2021 at 06:49:02AM -0500, David Steele wrote:
> OK, but shouldn't we have a full page write for this page after the backup
> starts, rather than the partial we seem to be seeing here? Is there any
> condition where the full page write would be skipped legitimately, or does
> it point to a problem?

That's how things work.  If they don't work this way for physical
backups, we may have a problem.  At replay, the full page will be
replayed if BKPIMAGE_APPLY is correctly set, as per
XLogReadBufferForRedoExtended().  And XLogRecordAssemble()
does the decision when building the record (just grep for
needs_backup).

> If Postgres is running correctly there is certainly no expectation for
> support of this unusual use case, but I do think that this possibly points
> to an issue in Postgres, which under normal circumstances would be very hard
> to detect.

Well, the report tells that this is an issue that happens on those
fake files full of zeros, but are you sure that you have the sizing
right?  I still don't see any evidence of anything broken based on the
information gathered for full backups, FWIW.
--
Michael

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: BUG #16894: PANIC: WAL contains references to invalid pages
  2021-02-24 09:42 BUG #16894: PANIC:  WAL contains references to invalid pages PG Bug reporting form <noreply@postgresql.org>
  2021-02-24 12:22 ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Антон Курочкин <antkurochkin@gmail.com>
  2021-02-25 17:53   ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
  2021-03-01 04:31     ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Michael Paquier <michael@paquier.xyz>
  2021-03-02 12:46       ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
  2021-03-03 07:03         ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Michael Paquier <michael@paquier.xyz>
  2021-03-03 11:49           ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
  2021-03-05 04:32             ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Michael Paquier <michael@paquier.xyz>
@ 2021-03-05 12:55               ` David Steele <david@pgmasters.net>
  2021-03-06 14:37                 ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
  0 siblings, 1 reply; 10+ messages in thread

From: David Steele @ 2021-03-05 12:55 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Антон Курочкин <antkurochkin@gmail.com>; pgsql-bugs@lists.postgresql.org

On 3/4/21 11:32 PM, Michael Paquier wrote:
> On Wed, Mar 03, 2021 at 06:49:02AM -0500, David Steele wrote:
>> OK, but shouldn't we have a full page write for this page after the backup
>> starts, rather than the partial we seem to be seeing here? Is there any
>> condition where the full page write would be skipped legitimately, or does
>> it point to a problem?
> 
> That's how things work.  If they don't work this way for physical
> backups, we may have a problem.  At replay, the full page will be
> replayed if BKPIMAGE_APPLY is correctly set, as per
> XLogReadBufferForRedoExtended().  And XLogRecordAssemble()
> does the decision when building the record (just grep for
> needs_backup).

That's exactly the problem. This WAL record appears to be the only 
reference to the page the submitter could find in their WAL. Anton, 
could you confirm that?

If we can confirm that there is no FPI for this page after the initial 
backup checkpoint, wouldn't that point to an issue?

>> If Postgres is running correctly there is certainly no expectation for
>> support of this unusual use case, but I do think that this possibly points
>> to an issue in Postgres, which under normal circumstances would be very hard
>> to detect.
> 
> Well, the report tells that this is an issue that happens on those
> fake files full of zeros, but are you sure that you have the sizing
> right?  I still don't see any evidence of anything broken based on the
> information gathered for full backups, FWIW.

This is the size we validate for normal restores so pretty sure it is 
accurate. Actually, postgres doesn't seem to care if the file is large 
enough as long as it is present. The reason we precreate the files at 
the correct size is because when postgres extends the file it is not 
sparse and uses more space.

Regards,
-- 
-David
david@pgmasters.net





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: BUG #16894: PANIC: WAL contains references to invalid pages
  2021-02-24 09:42 BUG #16894: PANIC:  WAL contains references to invalid pages PG Bug reporting form <noreply@postgresql.org>
  2021-02-24 12:22 ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Антон Курочкин <antkurochkin@gmail.com>
  2021-02-25 17:53   ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
  2021-03-01 04:31     ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Michael Paquier <michael@paquier.xyz>
  2021-03-02 12:46       ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
  2021-03-03 07:03         ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Michael Paquier <michael@paquier.xyz>
  2021-03-03 11:49           ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
  2021-03-05 04:32             ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Michael Paquier <michael@paquier.xyz>
  2021-03-05 12:55               ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
@ 2021-03-06 14:37                 ` David Steele <david@pgmasters.net>
  0 siblings, 0 replies; 10+ messages in thread

From: David Steele @ 2021-03-06 14:37 UTC (permalink / raw)
  To: Владимир Владимир <skilyazhnev@mail.ru>; michael@paquier.xyz; +Cc: pgsql-bugs@lists.postgresql.org; antkurochkin@gmail.com

On 3/6/21 2:32 AM, Владимир Владимир wrote:
> 
> I have reproduced the situation with WAL files and zeroed pgbackrest files.
> the same problem started on the first lsn in wal.

I spent some time looking through the code this morning. As I read it, 
WAL replay allows uninitialized (and missing) pages up to the point 
where consistency is achieved and after that flags uninitialized (or 
missing) pages as errors. It's pretty hard to argue with this logic and 
it addresses my concern about torn pages in the backups, i.e. 
torn/zero/missing pages will be repaired without complaint up to 
consistency.

So I believe that means Postgres is working as expected and there is no bug.

For pgBackRest selective restore there are two solutions:

1) It appears recovery_target = 'immediate' works fine. This was 
verified in Anton's case. Vladimir, perhaps you could try that?

2) PG13 introduced the ignore_invalid_pages GUC [1] which should allow 
replay to work past consistency. Note that the onus would be on the user 
to ensure that there were no invalid pages in the database(s) they are 
restoring.

I think we should continue the discussion in [2] since there doesn't 
seem to be anything else to discuss in this thread.

Regards,
-- 
-David
david@pgmasters.net

[1] 
https://www.postgresql.org/message-id/E1iu6D8-0000tK-Cm%40gemulon.postgresql.org
[2] https://github.com/pgbackrest/pgbackrest/issues/796





^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2021-03-06 14:37 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-02-24 09:42 BUG #16894: PANIC:  WAL contains references to invalid pages PG Bug reporting form <noreply@postgresql.org>
2021-02-24 12:22 ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Антон Курочкин <antkurochkin@gmail.com>
2021-02-25 17:53   ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
2021-03-01 04:31     ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Michael Paquier <michael@paquier.xyz>
2021-03-02 12:46       ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
2021-03-03 07:03         ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Michael Paquier <michael@paquier.xyz>
2021-03-03 11:49           ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
2021-03-05 04:32             ` Re: BUG #16894: PANIC: WAL contains references to invalid pages Michael Paquier <michael@paquier.xyz>
2021-03-05 12:55               ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>
2021-03-06 14:37                 ` Re: BUG #16894: PANIC: WAL contains references to invalid pages David Steele <david@pgmasters.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox