Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qTMnV-009LJM-U1 for pgsql-general@arkaria.postgresql.org; Tue, 08 Aug 2023 13:30:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qTMnU-00GPcl-1r for pgsql-general@arkaria.postgresql.org; Tue, 08 Aug 2023 13:30:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qTMnT-00GPcc-Lg for pgsql-general@lists.postgresql.org; Tue, 08 Aug 2023 13:30:52 +0000 Received: from mail-yb1-xb2c.google.com ([2607:f8b0:4864:20::b2c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qTMnR-001I6g-0b for pgsql-general@lists.postgresql.org; Tue, 08 Aug 2023 13:30:51 +0000 Received: by mail-yb1-xb2c.google.com with SMTP id 3f1490d57ef6-d299ed34bacso9531827276.1 for ; Tue, 08 Aug 2023 06:30:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1691501447; x=1692106247; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=+X5njdTe5j6IS9rWzvoO3Z6ETnFQwM4IBKjSilWUNcI=; b=LhWAjafyX/LvlrZCiA4gFiWlmWU7ZGo4ukiLLGcuwuVysDgUQT02Lc/x+rRUcmiQfn 7Z/uTWaEkZnbTyDDWrwpcapWYF98uV4IbauV8VSmzqWVtSoauF12y41y2yzBWNzRoX+W EZ+0o8xbxwha5JqDqeuZYmBlsPUM4XFeKdYkKRU1tVs34zeMssNmtjSQvW+7PqzJOKtb wIoO+HW+6Vp+EFBwLqtjyaSdp/ZHsvPFZ/GgZlZHrUzVtKN2IzvtrC0novfFDiTKkdmn XWMO+hzzdYPwLle9Bymt7lHzW5Rg+rWS8YKR3Mrl6RWY1inS7KEPrm4k6Jbl+tAZy2z0 FBhg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1691501447; x=1692106247; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=+X5njdTe5j6IS9rWzvoO3Z6ETnFQwM4IBKjSilWUNcI=; b=Ob3I7BwiQoYjBz7RJUaTu4YijapG7yzZsDZZbJghx8dayXwntdRnGH5hj0Ajw34DaF 8fR/a5bjyfnURlYMeizJelOZkgN/FpgiWhTLayXs4/ePpES9crWHoWMk2kZ3QKrkLNev BOnhDtiY0vFkmmp/F3v2mVSu+hmTZPLa0svBnLCHf8j6mIA0Pt5JYS+pQAKOEe1amRrE 7BAjUejW/M/8PFBcka5wMHhtMVHzbmb/32r2IXFC5gtS88vYzDhxbEFTE/iv03M7F1Au pHKy/DCKVov8dUa4AzIasurjoNWxZcmORKyDjkHiajVMW1ky0qPqapuyKoa0wtd+y0iu yMyQ== X-Gm-Message-State: AOJu0YwvFYH0kdxiyLyBXPMGbbkk771h6XA6tsZim4XARZq978wYflYn moouNw5BQVcnHtPXgFeyZzEtB5TsbHM= X-Google-Smtp-Source: AGHT+IFDMPtjKryGbcMiv/BqyyOIMCQaelBX2jFimc5fS9WioamtpNFciZpBB0KXsh4MgNwmB7rynA== X-Received: by 2002:a25:dcc1:0:b0:d16:1ef7:9b71 with SMTP id y184-20020a25dcc1000000b00d161ef79b71mr11617617ybe.10.1691501446932; Tue, 08 Aug 2023 06:30:46 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:e114:e5b8:c582:3847? ([2600:1700:bd2c:b810:e114:e5b8:c582:3847]) by smtp.googlemail.com with ESMTPSA id g188-20020a2520c5000000b00d32135938c1sm2794187ybg.26.2023.08.08.06.30.46 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 08 Aug 2023 06:30:46 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------9sgsx0Wib7s3FJdGBxA0rsEv" Message-ID: Date: Tue, 8 Aug 2023 08:30:45 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: PostgreSQL 14.8 - server fails to start even though all transaction logs with base backup are restored Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------9sgsx0Wib7s3FJdGBxA0rsEv Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit "26.3.3.2. Making An Exclusive Low-Level Backup Note The exclusive backup method is deprecated and should be avoided. Prior toPostgreSQL9.6, this was the only low-level method available, but it is now recommended that all users upgrade their scripts to use non-exclusive backups." Use PgBackRest, instead. On 8/8/23 02:39, Meera Nair wrote: > > Hi Team, > > We are following > https://www.postgresql.org/docs/14/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP > > Making An Exclusive Low-Level Backup. > > After restoring, we see the server fails to start with below error: > > PostgreSQL server log: > > 2023-08-04 16:47:47.227 IST [40582] LOG:  starting PostgreSQL 14.8 on > x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat > 4.8.5-44), 64-bit > > 2023-08-04 16:47:47.228 IST [40582] LOG:  listening on IPv4 address > "0.0.0.0", port 5414 > > 2023-08-04 16:47:47.228 IST [40582] LOG:  listening on IPv6 address "::", > port 5414 > > 2023-08-04 16:47:47.231 IST [40582] LOG:  listening on Unix socket > "/var/run/postgresql/.s.PGSQL.5414" > > 2023-08-04 16:47:47.236 IST [40582] LOG:  listening on Unix socket > "/tmp/.s.PGSQL.5414" > > 2023-08-04 16:47:47.243 IST [40584] LOG:  database system was interrupted; > last known up at 2023-08-04 14:48:29 IST > > 2023-08-04 16:47:49.254 IST [40584] LOG:  starting archive recovery > > cp: cannot stat ‘/var/lib/pgsql/14/wal/00000004.history’: No such file or > directory > > 2023-08-04 16:47:49.275 IST [40584] LOG:  restored log file > "000000040000000200000041" from archive > > 2023-08-04 16:47:49.290 IST [40584] LOG:  redo starts at 2/41000028 > > 2023-08-04 16:47:49.310 IST [40584] LOG:  restored log file > "000000040000000200000042" from archive > > 2023-08-04 16:47:49.344 IST [40584] LOG:  restored log file > "000000040000000200000043" from archive > > 2023-08-04 16:47:49.368 IST [40584] LOG:  restored log file > "000000040000000200000044" from archive > > 2023-08-04 16:47:49.395 IST [40584] LOG:  restored log file > "000000040000000200000045" from archive > > 2023-08-04 16:47:49.423 IST [40584] LOG:  restored log file > "000000040000000200000046" from archive > > 2023-08-04 16:47:49.460 IST [40584] LOG:  restored log file > "000000040000000200000047" from archive > > 2023-08-04 16:47:50.649 IST [40584] LOG:  restored log file > "000000040000000200000048" from archive > > 2023-08-04 16:47:51.219 IST [40584] LOG:  restored log file > "000000040000000200000049" from archive > > cp: cannot stat ‘/var/lib/pgsql/14/wal/00000004000000020000004A’: No such > file or directory > > 2023-08-04 16:47:51.603 IST [40584] LOG:  redo done at 2/49013500 system > usage: CPU: user: 0.00 s, system: 0.19 s, elapsed: 2.31 s > > 2023-08-04 16:47:51.603 IST [40584] LOG:  last completed transaction was > at log time 2023-08-04 14:58:51.021414+05:30 > > 2023-08-04 16:47:51.628 IST [40584] LOG:  restored log file > "000000040000000200000049" from archive > > 2023-08-04 16:47:51.973 IST [40584] FATAL:  WAL ends before end of online > backup > > 2023-08-04 16:47:51.973 IST [40584] HINT:  Online backup started with > pg_start_backup() must be ended with pg_stop_backup(), and all WAL up to > that point must be available at recovery. > > 2023-08-04 16:47:51.975 IST [40582] LOG:  startup process (PID 40584) > exited with exit code 1 > > 2023-08-04 16:47:51.975 IST [40582] LOG:  terminating any other active > server processes > > 2023-08-04 16:47:51.975 IST [40582] LOG:  shutting down due to startup > process failure > > 2023-08-04 16:47:51.977 IST [40582] LOG:  database system is shut down > > Backup_label from data directory: > > [root@gkstandby2 data]# cat backup_label > > START WAL LOCATION: 2/41000028 (file 000000040000000200000041) > > CHECKPOINT LOCATION: 2/41000060 > > BACKUP METHOD: pg_start_backup > > BACKUP FROM: primary > > START TIME: 2023-08-04 14:48:29 IST > > LABEL: pgida_backup_5414_108625_1691140709 > > START TIMELINE: 4 > > [root@gkstandby2 data]# > > Archive log directory: > > [root@gkstandby2 wal]# ls > > 00000002000000020000003B 00000002000000020000003E.00000028.backup > 000000040000000200000040.00000028.backup 000000040000000200000044 > 000000040000000200000048 > > 00000002000000020000003C 00000002.history 000000040000000200000041 > 000000040000000200000045 000000040000000200000049 > > 00000002000000020000003C.00000028.backup 0000000300000005000000E4 > 000000040000000200000042 000000040000000200000045.00005AE0.backup > > 00000002000000020000003D 00000004000000020000003F 000000040000000200000043 > 000000040000000200000046 > > 00000002000000020000003E 000000040000000200000040 > 000000040000000200000043.00000028.backup 000000040000000200000047 > > [root@gkstandby2 wal]# /usr/pgsql-14/bin/pg_waldump > 000000040000000200000046 | grep BACKUP > > rmgr: XLOG        len (rec/tot):     34/    34, tx:          0, lsn: > 2/46000110, prev 2/46000098, desc: BACKUP_END 2/45005AE0 > > [root@gkstandby2 wal]# > > We are restoring all the transaction logs required for base backup to be > consistent. > > BACKUP_END is present in the 000000040000000200000046 segment. Why did > recovery fail to find this? > > Regards, > > Meera > -- Born in Arizona, moved to Babylonia. --------------9sgsx0Wib7s3FJdGBxA0rsEv Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
"26.3.3.2. Making An Exclusive Low-Level Backup

Note

The exclusive backup method is deprecated and should be avoided. Prior to PostgreSQL 9.6, this was the only low-level method available, but it is now recommended that all users upgrade their scripts to use non-exclusive backups."


Use PgBackRest, instead.


On 8/8/23 02:39, Meera Nair wrote:

Hi Team,

 

We are following https://www.postgresql.org/docs/14/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP

Making An Exclusive Low-Level Backup.

 

After restoring, we see the server fails to start with below error:

 

PostgreSQL server log:

2023-08-04 16:47:47.227 IST [40582] LOG:  starting PostgreSQL 14.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit

2023-08-04 16:47:47.228 IST [40582] LOG:  listening on IPv4 address "0.0.0.0", port 5414

2023-08-04 16:47:47.228 IST [40582] LOG:  listening on IPv6 address "::", port 5414

2023-08-04 16:47:47.231 IST [40582] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5414"

2023-08-04 16:47:47.236 IST [40582] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5414"

2023-08-04 16:47:47.243 IST [40584] LOG:  database system was interrupted; last known up at 2023-08-04 14:48:29 IST

2023-08-04 16:47:49.254 IST [40584] LOG:  starting archive recovery

cp: cannot stat ‘/var/lib/pgsql/14/wal/00000004.history’: No such file or directory

2023-08-04 16:47:49.275 IST [40584] LOG:  restored log file "000000040000000200000041" from archive

2023-08-04 16:47:49.290 IST [40584] LOG:  redo starts at 2/41000028

2023-08-04 16:47:49.310 IST [40584] LOG:  restored log file "000000040000000200000042" from archive

2023-08-04 16:47:49.344 IST [40584] LOG:  restored log file "000000040000000200000043" from archive

2023-08-04 16:47:49.368 IST [40584] LOG:  restored log file "000000040000000200000044" from archive

2023-08-04 16:47:49.395 IST [40584] LOG:  restored log file "000000040000000200000045" from archive

2023-08-04 16:47:49.423 IST [40584] LOG:  restored log file "000000040000000200000046" from archive

2023-08-04 16:47:49.460 IST [40584] LOG:  restored log file "000000040000000200000047" from archive

2023-08-04 16:47:50.649 IST [40584] LOG:  restored log file "000000040000000200000048" from archive

2023-08-04 16:47:51.219 IST [40584] LOG:  restored log file "000000040000000200000049" from archive

cp: cannot stat ‘/var/lib/pgsql/14/wal/00000004000000020000004A’: No such file or directory

2023-08-04 16:47:51.603 IST [40584] LOG:  redo done at 2/49013500 system usage: CPU: user: 0.00 s, system: 0.19 s, elapsed: 2.31 s

2023-08-04 16:47:51.603 IST [40584] LOG:  last completed transaction was at log time 2023-08-04 14:58:51.021414+05:30

2023-08-04 16:47:51.628 IST [40584] LOG:  restored log file "000000040000000200000049" from archive

2023-08-04 16:47:51.973 IST [40584] FATAL:  WAL ends before end of online backup

2023-08-04 16:47:51.973 IST [40584] HINT:  Online backup started with pg_start_backup() must be ended with pg_stop_backup(), and all WAL up to that point must be available at recovery.

2023-08-04 16:47:51.975 IST [40582] LOG:  startup process (PID 40584) exited with exit code 1

2023-08-04 16:47:51.975 IST [40582] LOG:  terminating any other active server processes

2023-08-04 16:47:51.975 IST [40582] LOG:  shutting down due to startup process failure

2023-08-04 16:47:51.977 IST [40582] LOG:  database system is shut down

 

 

Backup_label from data directory:

 

[root@gkstandby2 data]# cat backup_label

START WAL LOCATION: 2/41000028 (file 000000040000000200000041)

CHECKPOINT LOCATION: 2/41000060

BACKUP METHOD: pg_start_backup

BACKUP FROM: primary

START TIME: 2023-08-04 14:48:29 IST

LABEL: pgida_backup_5414_108625_1691140709

START TIMELINE: 4

[root@gkstandby2 data]#

 

 

Archive log directory:

 

[root@gkstandby2 wal]# ls

00000002000000020000003B                  00000002000000020000003E.00000028.backup  000000040000000200000040.00000028.backup  000000040000000200000044                  000000040000000200000048

00000002000000020000003C                  00000002.history                          000000040000000200000041                  000000040000000200000045                  000000040000000200000049

00000002000000020000003C.00000028.backup  0000000300000005000000E4                  000000040000000200000042                  000000040000000200000045.00005AE0.backup 

00000002000000020000003D                  00000004000000020000003F                  000000040000000200000043                  000000040000000200000046

00000002000000020000003E                  000000040000000200000040                  000000040000000200000043.00000028.backup  000000040000000200000047

 

 

[root@gkstandby2 wal]# /usr/pgsql-14/bin/pg_waldump 000000040000000200000046 | grep BACKUP

rmgr: XLOG        len (rec/tot):     34/    34, tx:          0, lsn: 2/46000110, prev 2/46000098, desc: BACKUP_END 2/45005AE0

[root@gkstandby2 wal]#

 

 

We are restoring all the transaction logs required for base backup to be consistent.

BACKUP_END is present in the 000000040000000200000046 segment. Why did recovery fail to find this?

 

 

Regards,

Meera

 


--
Born in Arizona, moved to Babylonia.
--------------9sgsx0Wib7s3FJdGBxA0rsEv--