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 1qqcgP-000Gwq-Tz for pgsql-general@arkaria.postgresql.org; Wed, 11 Oct 2023 17:07:41 +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 1qqcgM-0008Au-Uz for pgsql-general@arkaria.postgresql.org; Wed, 11 Oct 2023 17:07:39 +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 1qqcgM-0008Al-Jw for pgsql-general@lists.postgresql.org; Wed, 11 Oct 2023 17:07:39 +0000 Received: from mail-yw1-x112d.google.com ([2607:f8b0:4864:20::112d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qqcgI-0001qt-0y for pgsql-general@lists.postgresql.org; Wed, 11 Oct 2023 17:07:38 +0000 Received: by mail-yw1-x112d.google.com with SMTP id 00721157ae682-5a7af45084eso1087177b3.0 for ; Wed, 11 Oct 2023 10:07:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1697044053; x=1697648853; darn=lists.postgresql.org; 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=JwhsFBqzXnDHZ6oP465dqrsO7hDdd/Ib5Nh3oMqVer4=; b=T4m8bwXmyKaQVY8idOOH8inQgD2kqTw0XV+jCJNqrwGdwaubk+wDYjYjFsO5+eSY2e BWC0rWSEqk0vRnaxzAkkiLyMrdwFC3sLFoxQs/HINJdIJVRJzd7zs6tD+UWZ0ETuSPC4 9bv+9pUCpO92tgqe12sAumhaC6ehXrhAwP9Y5oo7MO+SbZ6y+3xBghphjfhVCohrVu2X rLsxyOvpq5ubZhcPQwfnbr0W9392Ae95e7KNSzmFD5L30nVilk7X70OV+e23nNPt705t +6QzfbPR5lB/YYGbFNC78Lby9HoplQycp6V3BbWl4gNkmGU7jVGOdZAyt8pLgyTPysqc VvQw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1697044053; x=1697648853; 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=JwhsFBqzXnDHZ6oP465dqrsO7hDdd/Ib5Nh3oMqVer4=; b=HF+48B4aCodzLwa/w6NkBsxolbk5y5vK+bO+kQT9cqx4cO8ow/qb/PH6bxBIm1+jYj Bn5rbsV62f6CzuxjHGvbRL2IArPKHTCJVV2dcfOXLvQ70DyJMMTstBsWES6b6SvTi+Tt +s7FhSHphnp8AcAgNDVVp9f2kO8y3RjhpYfvfLLRnyIaFU4tPij3fVim58s340VKY6bi lfPiAenRFNt+7/58KwXKk96hcwjj6p/POFwSvY47uNM9OmdHrMjB0hkCSGAnfTAQZ8U/ nSck8SWIG1BYUR79HQ/MUEhiJ2TyVn6/fpy+jSxRznCXvsD0pdTljbXAufLoawICoN0W KlTg== X-Gm-Message-State: AOJu0YyKVfpI/3wPg4gewUlKAsag5I7HNnlNBmGykdNQvsyg/SHWPjmh GJjlMupu0QOjLylA/yt3JsZ7gthXgNc= X-Google-Smtp-Source: AGHT+IFghxmZNfbJzWaYg8hpIIPhE0qX/WyG0OV4K+1da3tcm6FJe0rV8F3TjzNkjy5sbp0pB8eefg== X-Received: by 2002:a81:7309:0:b0:59b:fcca:afc6 with SMTP id o9-20020a817309000000b0059bfccaafc6mr21177210ywc.50.1697044052939; Wed, 11 Oct 2023 10:07:32 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:fd24:e51f:1d6f:f68d? ([2600:1700:bd2c:b810:fd24:e51f:1d6f:f68d]) by smtp.googlemail.com with ESMTPSA id o206-20020a8173d7000000b00583e52232f1sm5332287ywc.112.2023.10.11.10.07.32 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 11 Oct 2023 10:07:32 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------Aqq0o7nrfBbFU0zY0CCWd28p" Message-ID: Date: Wed, 11 Oct 2023 12:07:32 -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 To: pgsql-general@lists.postgresql.org References: <386a5cbfb38a6cd0c5fd5ed7f0d08154c2cdb49b.camel@cybertec.at> 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. --------------Aqq0o7nrfBbFU0zY0CCWd28p Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 -- Born in Arizona, moved to Babylonia. --------------Aqq0o7nrfBbFU0zY0CCWd28p Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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

--
Born in Arizona, moved to Babylonia.
--------------Aqq0o7nrfBbFU0zY0CCWd28p--