Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pD72C-0005hv-Jg for pgsql-general@arkaria.postgresql.org; Wed, 04 Jan 2023 16:54:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pD72B-0007Ge-1B for pgsql-general@arkaria.postgresql.org; Wed, 04 Jan 2023 16:54:35 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pD72A-0007GV-FD for pgsql-general@lists.postgresql.org; Wed, 04 Jan 2023 16:54:34 +0000 Received: from mx4.stadt-zuerich.ch ([194.56.33.13]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pD726-0001vt-Mr for pgsql-general@lists.postgresql.org; Wed, 04 Jan 2023 16:54:33 +0000 IronPort-SDR: mEQ9mOqONnN3N8woqONHLhY/dIEKvZUsTOSRFVIYVEpAQjQKKxdJWcoLiipsdbA+lM2AnIWtdz N8sH/KLL2LW+v5r7sieJMw02O6OH9CQoLrOKhGt57SH0oGbphRzAF2fin2p59WkYPaHz8DBPsQ A1OWYsawQqqAER8/RGRW5aMAg3vn8gpotbO+AaaOLM2mpYj1T142YZccz5MAr1EXZZwpTVVv9n 7bwwjszCczizApnJzYh58ihoc2C/lQDVUd5Arz43s02oPxtD35j2GA9HQa2kWXN9P8FiGHgA3n 7OA= X-IronPort-AV: E=Sophos;i="5.96,300,1665439200"; d="scan'208,217";a="35000302" Received: from unknown (HELO SZHM27372.global.szh.loc) ([10.7.50.60]) by mx4.prm.szh.zone with ESMTP/TLS/AES256-GCM-SHA384; 04 Jan 2023 17:54:27 +0100 Received: from SZHM27370.global.szh.loc (10.7.50.50) by SZHM27372.global.szh.loc (10.7.50.61) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.2375.18; Wed, 4 Jan 2023 17:54:27 +0100 Received: from SZHM27370.global.szh.loc ([fe80::25ce:879f:5795:3325]) by SZHM27370.global.szh.loc ([fe80::25ce:879f:5795:3325%19]) with mapi id 15.01.2375.018; Wed, 4 Jan 2023 17:54:27 +0100 From: "Zwettler Markus (OIZ)" To: "pgsql-general@lists.postgresql.org" Subject: curious postgres (crash) recovery behavior Thread-Topic: curious postgres (crash) recovery behavior Thread-Index: AdkgV0Wvwjpufe90So+0u/stSXaJJA== Date: Wed, 4 Jan 2023 16:54:26 +0000 Message-ID: <3bf6e04d6433401e9249517419f76ff6@zuerich.ch> Accept-Language: de-CH, en-US Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.7.48.249] Content-Type: multipart/alternative; boundary="_000_3bf6e04d6433401e9249517419f76ff6zuerichch_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_3bf6e04d6433401e9249517419f76ff6zuerichch_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable We are using Crunchy PGO which uses "pgbackrest". Over Christmas we had a disk full error on the "pgbackrest" repo followed b= y a disk full error on the PostgreSQL instance pod. Unfortunately, a colleague then deleted the current "pg_wal" directory on t= he instance pod. So we had to do a point-in-time recovery to overcome this situation. we started a PITR to 2022-12-23 01:34 based on the following "pgbackrest" b= ackup: sh-4.4$ pgbackrest info stanza: db status: ok cipher: none db (current) wal archive min/max (13): 0000000B0000010B000000C1/0000000C00000111= 0000000A full backup: 20221222-230004F timestamp start/stop: 2022-12-22 23:00:04 / 2022-12-23 01:32:17 wal start/stop: 0000000B0000010B000000C1 / 0000000B0000010C0000= 005C database size: 46.3GB, database backup size: 46.3GB repo1: backup set size: 17.6GB, backup size: 17.6GB During the point-in-time recovery, the "pgbackrest" pod terminated incorrec= tly with an error "pg_ctl: server did not start in time". There is a known PGO bug on this (pg_ctl default timeout of 60 secs cannot = be changed). PGO started the instance pod anyway in following, which leads to a regular = PostgreSQL crash recovery ending with "last completed transaction was at lo= g time 2022-12-23 20:52:29.584555+01": ... 2023-01-04 15:26:35 CET : =3D>@ : 94-4=3D>63b58c9b.5e : 00000 LOG: startin= g PostgreSQL 13.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210= 514 (Red Hat 8.5.0-10), 64-bit 2023-01-04 15:26:35 CET : =3D>@ : 94-5=3D>63b58c9b.5e : 00000 LOG: listeni= ng on IPv4 address "0.0.0.0", port 5432 2023-01-04 15:26:35 CET : =3D>@ : 94-6=3D>63b58c9b.5e : 00000 LOG: listeni= ng on IPv6 address "::", port 5432 2023-01-04 15:26:35 CET : =3D>@ : 94-7=3D>63b58c9b.5e : 00000 LOG: listeni= ng on Unix socket "/tmp/postgres/.s.PGSQL.5432" 2023-01-04 15:26:35 CET : =3D>@ : 108-1=3D>63b58c9b.6c : 00000 LOG: databa= se system was interrupted while in recovery at log time 2022-12-23 01:06:58= CET 2023-01-04 15:26:35 CET : =3D>@ : 108-2=3D>63b58c9b.6c : 00000 HINT: If th= is has occurred more than once some data might be corrupted and you might n= eed to choose an earlier recovery target. 2023-01-04 15:26:35 CET : =3D>@ : 108-3=3D>63b58c9b.6c : 00000 LOG: enteri= ng standby mode 2023-01-04 15:26:35 CET : =3D>@ : 108-4=3D>63b58c9b.6c : 00000 LOG: restor= ed log file "0000000B.history" from archive 2023-01-04 15:26:35 CET : =3D>@ : 108-5=3D>63b58c9b.6c : 00000 LOG: restor= ed log file "0000000B0000010C0000000C" from archive ... 2023-01-04 15:30:06 CET : =3D>@ : 108-1198=3D>63b58c9b.6c : 00000 LOG: res= tored log file "0000000B00000110000000AD" from archive 2023-01-04 15:30:06 CET : =3D>@ : 108-1199=3D>63b58c9b.6c : 00000 LOG: rec= eived promote request 2023-01-04 15:30:06 CET : =3D>@ : 108-1200=3D>63b58c9b.6c : 00000 LOG: red= o done at 110/AD0005B8 2023-01-04 15:30:06 CET : =3D>@ : 108-1201=3D>63b58c9b.6c : 00000 LOG: las= t completed transaction was at log time 2022-12-23 20:52:29.584555+01 2023-01-04 15:30:07 CET : =3D>@ : 108-1202=3D>63b58c9b.6c : 00000 LOG: res= tored log file "0000000B00000110000000AD" from archive 2023-01-04 15:30:07 CET : =3D>@ : 108-1203=3D>63b58c9b.6c : 00000 LOG: sel= ected new timeline ID: 12 2023-01-04 15:30:07 CET : =3D>@ : 108-1204=3D>63b58c9b.6c : 00000 LOG: arc= hive recovery complete 2023-01-04 15:30:07 CET : =3D>@ : 108-1205=3D>63b58c9b.6c : 00000 LOG: res= tored log file "0000000B.history" from archive 2023-01-04 15:30:07 CET : =3D>@ : 94-9=3D>63b58c9b.5e : 00000 LOG: databas= e system is ready to accept connections ... QUESTION: =3D=3D=3D=3D=3D=3D=3D=3D=3D I wondered how the crash recovery can run to 2022-12-23 20:52:29.584555 and= simply open the database? When I got it right the full backup itself would be consistent with "wal st= op 0000000B0000010C0000005C". PostgreSQL additionally added some wal files until 0000000B00000110000000AD= (2022-12-23 20:52:29.584555) because it didn't find any more before the re= covery target of 2022-12-23 01:34. Am I right or did I miss something? --_000_3bf6e04d6433401e9249517419f76ff6zuerichch_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

We a= re using Crunchy PGO which uses "pgbackrest".

Over= Christmas we had a disk full error on the "pgbackrest" repo foll= owed by a disk full error on the PostgreSQL instance pod.

Unfo= rtunately, a colleague then deleted the current "pg_wal" director= y on the instance pod.

So w= e had to do a point-in-time recovery to overcome this situation.=

 

 

 

we s= tarted a PITR to 2022-12-23 01:34 based on the following "pgbackrest&q= uot; backup:

 

sh-4= .4$ pgbackrest info

stan= za: db

&nbs= p;   status: ok

&nbs= p;   cipher: none

 

&nbs= p;   db (current)

&nbs= p;       wal archive min/max (13): 0000000B00= 00010B000000C1/0000000C000001110000000A

 

&nbs= p;       full backup: 20221222-230004F

&nbs= p;           timestamp st= art/stop: 2022-12-22 23:00:04 / 2022-12-23 01:32:17

&nbs= p;           wal start/st= op: 0000000B0000010B000000C1 / 0000000B0000010C0000005C

&nbs= p;           database siz= e: 46.3GB, database backup size: 46.3GB

&nbs= p;           repo1: backu= p set size: 17.6GB, backup size: 17.6GB

 

 

 

Duri= ng the point-in-time recovery, the "pgbackrest" pod terminated in= correctly with an error "pg_ctl: server did not start in time".

Ther= e is a known PGO bug on this (pg_ctl default timeout of 60 secs cannot be c= hanged).

 

 

PGO = started the instance pod anyway in following, which leads to a regular Post= greSQL crash recovery ending with "last completed transaction was at l= og time 2022-12-23 20:52:29.584555+01":

...<= o:p>

2023= -01-04 15:26:35 CET : =3D>@ : 94-4=3D>63b58c9b.5e : 00000 LOG:  = starting PostgreSQL 13.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.= 0 20210514 (Red Hat 8.5.0-10), 64-bit

2023= -01-04 15:26:35 CET : =3D>@ : 94-5=3D>63b58c9b.5e : 00000 LOG:  = listening on IPv4 address "0.0.0.0", port 5432<= /p>

2023= -01-04 15:26:35 CET : =3D>@ : 94-6=3D>63b58c9b.5e : 00000 LOG:  = listening on IPv6 address "::", port 5432

2023= -01-04 15:26:35 CET : =3D>@ : 94-7=3D>63b58c9b.5e : 00000 LOG:  = listening on Unix socket "/tmp/postgres/.s.PGSQL.5432"=

2023= -01-04 15:26:35 CET : =3D>@ : 108-1=3D>63b58c9b.6c : 00000 LOG: = database system was interrupted while in recovery at log time 2022-12-23 0= 1:06:58 CET

2023= -01-04 15:26:35 CET : =3D>@ : 108-2=3D>63b58c9b.6c : 00000 HINT: = ; If this has occurred more than once some data might be corrupted and you = might need to choose an earlier recovery target.

2023-01-04 15:26:35= CET : =3D>@ : 108-3=3D>63b58c9b.6c : 00000 LOG:  entering stand= by mode

2023= -01-04 15:26:35 CET : =3D>@ : 108-4=3D>63b58c9b.6c : 00000 LOG: = restored log file "0000000B.history" from archive

2023= -01-04 15:26:35 CET : =3D>@ : 108-5=3D>63b58c9b.6c : 00000 LOG: = restored log file "0000000B0000010C0000000C" from archive

...<= o:p>

2023= -01-04 15:30:06 CET : =3D>@ : 108-1198=3D>63b58c9b.6c : 00000 LOG:&nb= sp; restored log file "0000000B00000110000000AD" from archive

2023= -01-04 15:30:06 CET : =3D>@ : 108-1199=3D>63b58c9b.6c : 00000 LOG:&nb= sp; received promote request

2023= -01-04 15:30:06 CET : =3D>@ : 108-1200=3D>63b58c9b.6c : 00000 LOG:&nb= sp; redo done at 110/AD0005B8

2023= -01-04 15:30:06 CET : =3D>@ : 108-1201=3D>63b58c9b.6c : 00000 LOG:&nb= sp; last completed transaction was at log time 2022-12-23 20:52:29.584555&#= 43;01

2023= -01-04 15:30:07 CET : =3D>@ : 108-1202=3D>63b58c9b.6c : 00000 LOG:&nb= sp; restored log file "0000000B00000110000000AD" from archive

2023= -01-04 15:30:07 CET : =3D>@ : 108-1203=3D>63b58c9b.6c : 00000 LOG:&nb= sp; selected new timeline ID: 12

2023= -01-04 15:30:07 CET : =3D>@ : 108-1204=3D>63b58c9b.6c : 00000 LOG:&nb= sp; archive recovery complete

2023= -01-04 15:30:07 CET : =3D>@ : 108-1205=3D>63b58c9b.6c : 00000 LOG:&nb= sp; restored log file "0000000B.history" from archive<= /span>

2023= -01-04 15:30:07 CET : =3D>@ : 94-9=3D>63b58c9b.5e : 00000 LOG:  = database system is ready to accept connections

...<= o:p>

 

 

 

QUES= TION:

=3D= =3D=3D=3D=3D=3D=3D=3D=3D

I wo= ndered how the crash recovery can run to 2022-12-23 20:52:29.584555 and sim= ply open the database?

 

When= I got it right the full backup itself would be consistent with "wal s= top 0000000B0000010C0000005C".

Post= greSQL additionally added some wal files until 0000000B00000110000000AD (20= 22-12-23 20:52:29.584555) because it didn't find any more before the recove= ry target of 2022-12-23 01:34.

 

Am I= right or did I miss something?

 

 

--_000_3bf6e04d6433401e9249517419f76ff6zuerichch_--