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 1qqjLk-000lzk-B4 for pgsql-general@arkaria.postgresql.org; Thu, 12 Oct 2023 00:14:48 +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 1qqjLh-001hrI-Ks for pgsql-general@arkaria.postgresql.org; Thu, 12 Oct 2023 00:14:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qqjLh-001hrA-9x for pgsql-general@lists.postgresql.org; Thu, 12 Oct 2023 00:14:45 +0000 Received: from mail-yw1-x112c.google.com ([2607:f8b0:4864:20::112c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qqjLf-0002Hw-HE for pgsql-general@lists.postgresql.org; Thu, 12 Oct 2023 00:14:44 +0000 Received: by mail-yw1-x112c.google.com with SMTP id 00721157ae682-5a7c93507d5so4901887b3.2 for ; Wed, 11 Oct 2023 17:14:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1697069682; x=1697674482; darn=lists.postgresql.org; h=in-reply-to:references:to:from:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=41OUgqe1AKr87OCMt+WlYO8HLUAgcLiwQg04srrKdZ4=; b=GMeinq+mnt52ZTBfKSluYC5Vn3bHb93McCENn8UiWdFf7UdIWOlN6dw9NbAD6zCicH w8WeSmUPMlDHTBqOkidfM5k5rkMs+pI4Ex+Z83/OAMbymtkxa+EGM+6OPVMQgR50Yi7C Xbusje9iFhNJPeeyHfyIAQIdV8rk8plrnpjEiftOv1Nvb03KiiCYGRs2d4PUbT1aCCSU 1RKkndge5FhsRGq39kno0lRRVGMKgPxbLpyFZhr6+fZNptxdrFEjAORt5z6K6WIKNzc+ KFGucHy1QP6diB44jxV9KhFYxazUYYF2jb5g9wa8VJp7UnUBX6qvgn6x76xrpquA5Dji Uu5Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1697069682; x=1697674482; h=in-reply-to:references:to:from:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=41OUgqe1AKr87OCMt+WlYO8HLUAgcLiwQg04srrKdZ4=; b=ZoWOAQ69N0khuVlXylaYMq2KP6DVKeS4iheq6TvXyTuigWg1dQEO8haoTi251mqQrW ZMjVs2mqoTbgII0F2SAwDfosIpEK2Umdn3oThwyMPI2XIMJlbPJCY6I4+H0TzQ27tcDg y4TZL7A6T8qLwN75+EBoWbHX+PKn+XIBY4TxHmy+wcDIKHTPawdiRFpWM0dEvCJJKoch 1ZHlOMYVn22bDvN2s6fiuuqH2e9i4MCGELwi8m7Jw2GEzXu+ksyfJfjGCvA/5Wd5rtJh z6NdvFgmh1PiD+BPNbAIkNQ9BFUWGVhk3RF8oCMSUUaFfumNy0bb3BaMn7QKtVTB7NT0 +m4g== X-Gm-Message-State: AOJu0Yx3yYC+fJBP4uTrxUZkCPBrfCqSPDmb0ThJRiyWtMh6Uy/4Gyt1 QM1iAPlNdAcV9HpR4bLG/z1S91Ub4AM= X-Google-Smtp-Source: AGHT+IF9SLCIPkDA3vCJ70x3rIEQ5SrrFdu2k7RMLW/E8ttxz1H5Sk+gZa3eEXGq/4cnqBT7deWCow== X-Received: by 2002:a0d:cad3:0:b0:5a7:bc0e:193e with SMTP id m202-20020a0dcad3000000b005a7bc0e193emr7916358ywd.18.1697069682375; Wed, 11 Oct 2023 17:14:42 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:9c07:69c3:3e12:3c8? ([2600:1700:bd2c:b810:9c07:69c3:3e12:3c8]) by smtp.googlemail.com with ESMTPSA id z124-20020a0dd782000000b0058ddb62f99bsm5616952ywd.38.2023.10.11.17.14.41 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 11 Oct 2023 17:14:42 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------0pf0kwDQh1Urhxl0QvqsqRpx" Message-ID: <199ffa21-3fa9-83e6-e62d-40eaee6fbfd8@gmail.com> Date: Wed, 11 Oct 2023 19:14:41 -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: A question about possible recovery inconsistency Content-Language: en-US From: Ron To: pgsql-general@lists.postgresql.org References: <386a5cbfb38a6cd0c5fd5ed7f0d08154c2cdb49b.camel@cybertec.at> 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. --------------0pf0kwDQh1Urhxl0QvqsqRpx Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 10/11/23 12:07, Ron wrote: > On 10/11/23 09:52, Eugen Konkov wrote: >>> But why do you want to do that, if all that you have to do is specify >> "recovery_target = 'immediate'" to recover to the end of the backup? >> >> Because automation scripts do not know if transactions are available >> after some point in time or not. But automation scripts know that >> backup was completed successfully at that point. >> For example: >> We want to provide time to recover the database. >> 1. Base backup restored, wal files are applied successfully if there >> is a transaction. > > Doesn't "pg_basebackup --wal-method=stream" already do that? > > Since the commands below automagically starts physical streaming, there > must be a variation where all the wal data is applied but /doesn't/ start > replication. > > pg_basebackup --dbname=service=basebackup -D $PGDATA --progress > --checkpoint=fast -v \ >         --write-recovery-conf --wal-method=stream --create-slot > --slot=pgstandby1 --compress=server-zstd > pg_ctl start -w > > Maybe just remove the two "slot" options: > pg_basebackup --dbname=service=basebackup -D $PGDATA --progress > --checkpoint=fast -v \ >         --write-recovery-conf --wal-method=stream --compress=server-zstd > After that pg_basebackup, do this to get R/W copy of the database: pg_ctl start pg_ctl promote -- Born in Arizona, moved to Babylonia. --------------0pf0kwDQh1Urhxl0QvqsqRpx Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 10/11/23 12:07, Ron wrote:
On 10/11/23 09:52, Eugen Konkov wrote:
But why do you want to do that, if all that you have to do is specify
"recovery_target = 'immediate'" to recover to the end of the backup?

Because automation scripts do not know if transactions are available
after some point in time or not. But automation scripts know that
backup was completed successfully at that point.
For example:
We want to provide time to recover the database.
1. Base backup restored, wal files are applied successfully if there
is a transaction.

Doesn't "pg_basebackup --wal-method=stream" already do that?

Since the commands below automagically starts physical streaming, there must be a variation where all the wal data is applied but doesn't start replication.

pg_basebackup --dbname=service=basebackup -D $PGDATA --progress --checkpoint=fast -v \
        --write-recovery-conf --wal-method=stream --create-slot --slot=pgstandby1 --compress=server-zstd
pg_ctl start -w

Maybe just remove the two "slot" options:
pg_basebackup --dbname=service=basebackup -D $PGDATA --progress --checkpoint=fast -v \
        --write-recovery-conf --wal-method=stream --compress=server-zstd


After that pg_basebackup, do this to get R/W copy of the database:
pg_ctl start
pg_ctl promote

--
Born in Arizona, moved to Babylonia.
--------------0pf0kwDQh1Urhxl0QvqsqRpx--