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 1rJKDx-00CjDM-Vd for pgsql-general@arkaria.postgresql.org; Fri, 29 Dec 2023 21:16:58 +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 1rJKDu-00CJgE-9e for pgsql-general@arkaria.postgresql.org; Fri, 29 Dec 2023 21:16:54 +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 1rJKDt-00CJg2-UI for pgsql-general@lists.postgresql.org; Fri, 29 Dec 2023 21:16:53 +0000 Received: from mail-ed1-x533.google.com ([2a00:1450:4864:20::533]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rJKDo-00EWuM-KG for pgsql-general@postgresql.org; Fri, 29 Dec 2023 21:16:51 +0000 Received: by mail-ed1-x533.google.com with SMTP id 4fb4d7f45d1cf-55569b59f81so2774680a12.1 for ; Fri, 29 Dec 2023 13:16:47 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1703884606; x=1704489406; darn=postgresql.org; h=content-transfer-encoding:in-reply-to:from:references:cc:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=FTp5Sk/8YsibqDRvFR0mNj+CW0JwbuU0ajVqI361eqY=; b=UyIEXgig6VVg/KlfzbvgpSif81fh+vOKB26I/I/JGxNSNAnf2cSY+3F0XmrgBUcoJu Q41RX5y3807JALknWxDVvMfjGj1dP6trUr+eGNi3jDE7aQ5A6EQyMDTa6nbXapB1qlwf xcficH8nJy2FBmCCBdpKougqjjqX8evAisJrIHfHjBchQsi+4ZRGF1YEQq1icW+v+MJy 1ZvcEKcFaoZwZIyOgsZT9OvfBWMJikXqdorHjn7d9VxOfghEtd5+Z8Fe/Q3F4ViioIB0 fK35RcDl655QAO+9sBEejFdJerp23l4Fjkd7aZZStbTtlkcefG8V+kXSFQqDDFiXseC1 tbkA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1703884606; x=1704489406; h=content-transfer-encoding:in-reply-to:from:references:cc: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=FTp5Sk/8YsibqDRvFR0mNj+CW0JwbuU0ajVqI361eqY=; b=RpajWb4CXLs3ZMUOWaovbATxzY1WWrrpbAwARPcOOVhoCMDkco3Okos/clgH/4ndAw 40FNYjbxWzIdm1CW4X7IUtZrfJT8NFflMFtPFqn/rkXABMXixGuX8U/NaY1Y1/lBNMUu ++kz51RORwfWnYyj7i+GYmMRbce6SU6pUmLCHfPOVZuwQfylTImujiLCCgrFLJcuSDlY Asyn2p7aFgUymlSDkG5c8TgAtYqp0ePUgPqLuZ2GPtWN6xjTJLBn3EBSjzuruZmroQEw azkjE+zT1FTM0A6eqwwN0y5h2XDGASBIrFBjn9Wd76vAZeiSz+tUOTwRrTjZqXTVg5+O qSAg== X-Gm-Message-State: AOJu0YxXnE8tp/YHqcCmk+Y0Cj4VnbVyni1OaHenNxppEaDe6T9V+2uG iJCsQVSZN3m1EgR6Rg8dIQtpCGPxqjQW X-Google-Smtp-Source: AGHT+IGlWjgTMyQ7Sop3T0aF5XLlMn2iG5Mo+oY2JGjk2Xf5i0UiJUAz2qlV5/D+9YMBi1FTQKkgCg== X-Received: by 2002:a17:906:74c2:b0:a23:6b80:c1db with SMTP id z2-20020a17090674c200b00a236b80c1dbmr6365647ejl.95.1703884606231; Fri, 29 Dec 2023 13:16:46 -0800 (PST) Received: from [10.137.0.17] (ip-86-49-229-30.bb.vodafone.cz. [86.49.229.30]) by smtp.gmail.com with ESMTPSA id wi5-20020a170906fd4500b00a27b4e1b189sm211908ejb.209.2023.12.29.13.16.45 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 29 Dec 2023 13:16:45 -0800 (PST) Message-ID: <3b75e08a-2d53-fbb5-731a-3a8e5c71edd8@enterprisedb.com> Date: Fri, 29 Dec 2023 22:16:44 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.15.1 Subject: Re: Intermittent Issue with WAL Segment Removal in Logical Replication Content-Language: en-US To: Kaushik Iska , pgsql-general@postgresql.org Cc: Sai Krishna Srirampur , =?UTF-8?Q?Philip_Dub=c3=a9?= References: From: Tomas Vondra In-Reply-To: Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 12/27/23 16:31, Kaushik Iska wrote: > Hi all, > > I'm including additional details, as I am able to reproduce this issue a > little more reliably. > > Postgres Version: POSTGRES_14_9.R20230830.01_07 > Vendor: Google Cloud SQL > Logical Replication Protocol version 1 > I don't know much about Google Cloud SQL internals. Is it relatively close to Postgres (as e.g. RDS) or are the internals very different / modified for cloud environments? > Here are the logs of attempt succeeding right after it fails: > > 2023-12-27 01:12:40.581 UTC [59790]: [6-1] db=postgres,user=postgres > STATEMENT:  START_REPLICATION SLOT peerflow_slot_wal_testing_2 LOGICAL > 6/5AE67D79 (proto_version '1', publication_names > 'peerflow_pub_wal_testing_2') <- FAILS > 2023-12-27 01:12:41.087 UTC [59790]: [7-1] db=postgres,user=postgres > ERROR:  requested WAL segment 000000010000000600000059 has already been > removed > 2023-12-27 01:12:44.581 UTC [59794]: [3-1] db=postgres,user=postgres > STATEMENT:  START_REPLICATION SLOT peerflow_slot_wal_testing_2 LOGICAL > 6/5AE67D79 (proto_version '1', publication_names > 'peerflow_pub_wal_testing_2')  <- SUCCEEDS > 2023-12-27 01:12:44.582 UTC [59794]: [4-1] db=postgres,user=postgres > LOG:  logical decoding found consistent point at 6/5A31F050 > > Happy to include any additional details of my setup. > I personally don't see how could this fail and then succeed, unless Google does something smart with the WAL segments under the hood. Surely we try to open the same WAL segment (given the LSN is the same), so how could it not exist and then exist? As Ron already suggested, it might be useful to see information for the replication slot peerflow_slot_wal_testing_2 (especially the restart_lsn value). Also, maybe show the contents of pg_wal (especially for the segment referenced in the error message). Can you reproduce this outside Google cloud environment? regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company