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 1tdywa-00B9y5-M8 for pgsql-general@arkaria.postgresql.org; Fri, 31 Jan 2025 21:52:57 +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 1tdywZ-002kFw-DV for pgsql-general@arkaria.postgresql.org; Fri, 31 Jan 2025 21:52:55 +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 1tdywY-002kFk-QP for pgsql-general@lists.postgresql.org; Fri, 31 Jan 2025 21:52:54 +0000 Received: from mail-ej1-x62b.google.com ([2a00:1450:4864:20::62b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tdywW-002WmL-0Q for pgsql-general@lists.postgresql.org; Fri, 31 Jan 2025 21:52:53 +0000 Received: by mail-ej1-x62b.google.com with SMTP id a640c23a62f3a-aaee2c5ee6eso443108266b.1 for ; Fri, 31 Jan 2025 13:52:52 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1738360371; x=1738965171; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=tiqpRCz2QsQR3nIwCYgN9E21UzxY/CbZQswOIp3DJ9s=; b=jHIZ1GjTQd29xWRnd5l8h6BduBW58LEzzK1KGO1/VVKm6iN8rNPwqoLBec0oMDiBJ4 6JC3YY3CbGIKKHCx9MnomcgwPcxmbepKKLpOd8UF4TchvcE5WdrRStPrqAOP3mGkLRDa Yjs5a7BsItGZiTW1Nwiqs4CalFyyzK98iMQHvlSsWBjpP4jIT8btOV/MeLskMXTRhTFv 0DYqaVs5r26dT4CN98euUJ/xfp8IYGU2jbL0nugE1/mDe0KeHnwmIortOB9pdfKbmb7v S0P84p2nDO37oXx2PeRQMac8Zvvez1PV6YafjeK01ux8g9/zE54YNgdLfWXm6aUcKoMS qbfw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1738360371; x=1738965171; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=tiqpRCz2QsQR3nIwCYgN9E21UzxY/CbZQswOIp3DJ9s=; b=DgGaVeuLUavfNmzlLSvE5MfRxae/tCHXG2l8uj8+X2IHyJXeUWWe/3LO3pxjYX8+fy 7hEKmfVTbC1zf9l7dUD6zBPWDXktAtJHaEYPFoHl0e/1oh8uOuV9mT3Yu+nv+w5sef8K hTZgC61EVAfT9eZbWV85DGWJJjMXAushZYmhjTGEDmELYm1EXoBKJ7ORvGcRFFJ40FGr 4ykLQnjwWXXrmjwMbNyqzFlvpyZBLRo7J3rlQCWb5/dSpUyvPNvRYlFLKEjjn18q6mgh YDdLr6Gde+bByAkSoVeia8tSRyQV+yYaJ9gQbG6N7O5CMH0g5D2AStToQo5GyKmbjgIp Hz3w== X-Gm-Message-State: AOJu0Ywv4CYl8v7ITdVxp/SfJXJWoqVNLXg8gMd5ca88nNK4bpybD8e1 MXg3seU+JEfKFL5KbTGSkuLURrZcRRloMnbgvA8mkIt1bC7oYUL+kxDXOyThhA2cYbES+RS83kq QofwWkKJCuWMdfmz3dyQQM2n8MxOSBkJy X-Gm-Gg: ASbGnctnDBxEfwNr6K7AWaHHLmhMFzI5BwHJWaRtvBHft3Eqjps/vLotAQHGsHoHDKo cTQseT6erUZNkgoIVLh588aJRlyU/WpQTj6CKgfpCMXOSD7ti+8DdM0nOlfvJcQXb39+uXwonAG yYrt2yZFLikCEsTRpQA/vo3Gq1VL9r1Mc= X-Google-Smtp-Source: AGHT+IEmUWcewpj13Q3z7zfjOIfuuiishYcFiBWraRQVV3ZQuMUv65KwpODMV+bgehulfN53vsQPkSUQ6Hw5c0YKxR4= X-Received: by 2002:a17:906:c114:b0:ab3:ed0:8d7 with SMTP id a640c23a62f3a-ab6cfce7369mr1238359566b.23.1738360370650; Fri, 31 Jan 2025 13:52:50 -0800 (PST) MIME-Version: 1.0 References: <70c20a65-4624-4509-ac6a-ef7f0119ea28@aklaver.com> In-Reply-To: From: Koen De Groote Date: Fri, 31 Jan 2025 22:52:39 +0100 X-Gm-Features: AWEUYZmtPqgQ3SpK1L9kNGHFVzkX_K9sxT8psxjVDlXVJNYYEDL-6yyX_7h0ysE Message-ID: Subject: Re: Postgres restore sometimes restores to a point 2 days in the past To: Adrian Klaver Cc: PostgreSQL General Content-Type: multipart/alternative; boundary="000000000000626703062d07901e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000626703062d07901e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable > I should have asked earlier what is the archive command The example from the documentation, but with GZIP. So from the documentation: https://www.postgresql.org/docs/16/continuous-archiving.html#BACKUP-ARCHIVI= NG-WAL Which becomes this: archive_command =3D 'test ! -f /mnt/server/archivedir/%f.gz && gzip -c %p /mnt/server/archivedir/%f.gz' > Are you setting standby.signal or recovery.signal or both Sorry, I keep confusing them. I checked my deployment code, it's recovery.signal and only that. Regards, Koen De Groote On Fri, Jan 31, 2025 at 10:26=E2=80=AFPM Adrian Klaver wrote: > On 1/31/25 12:10, Koen De Groote wrote: > > > What is the complete pg_basebackup command? > > > 2/ All my WAL files are archived and uploaded to the cloud. So, I can > > just have them downloaded. > > I should have asked earlier what is the archive command? > > Are > > > What is determining that a particular WAL file should be asked for? > > > > The postgres server itself does this. Here's the documentation: > > > https://www.postgresql.org/docs/16/runtime-config-wal.html#GUC-RESTORE-CO= MMAND > < > https://www.postgresql.org/docs/16/runtime-config-wal.html#GUC-RESTORE-CO= MMAND > > > > > > And here: https://www.postgresql.org/docs/current/warm-standby.html > > > > > > In practice, Postgres will see the "standby.signal" file and start > > In your OP you say: > > "It downloads the basebackup, unpacks it, sets a recovery.signal, ..." > > Are you setting standby.signal or recovery.signal or both? > > > > asking for WAL files. It will read the database it has and determine > > what the next WAL filename should be. And then it asks for it. And it > > will keep asking for these hexadecimal filenames, 1 at a time, for as > > long as the command or set of commands provided to "restore_command" > > returns exit code 0. If the process receives any other exit code, it > > stops recovery, switches timeline, and considers the database to be up > > and running at the state its in. > > > > It's constantly asking "I want this file now" and the script I have as > > the restore command will attempt to download it from the cloud. Then it > > will attempt to unzip it and move it into place. If any of these steps > > fails, I return exit code 1. > > > > > Regards, > > Koen De Groote > > > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > --000000000000626703062d07901e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
>=20 I should have asked earlier what is the archive command

The example from the documentation, but with GZIP= . So from the documentation: https://www.postgresql.org/= docs/16/continuous-archiving.html#BACKUP-ARCHIVING-WAL

Which becomes this: archive_command =3D 'test ! -f /mnt/server= /archivedir/%f.gz && gzip -c %p /mnt/server/archivedir/%f.gz'


>=20 Are you setting standby.signal or recovery.signal or both

Sorry, I keep confusing them. I checked my deploy= ment code, it's recovery.signal and only that.

Regards,
Koen De Groote


On Fri, Jan 31, 2025 at 10:26=E2=80=AFPM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
=
On 1/31/25 12:10, K= oen De Groote wrote:
>=C2=A0 > What is the complete pg_basebackup command?

> 2/ All my WAL files are archived and uploaded to the cloud. So, I can =
> just have them downloaded.

I should have asked earlier what is the archive command?

Are
>=C2=A0 > What is determining that a particular WAL file should be as= ked for?
>
> The postgres server itself does this. Here's the documentation: > https://www.postg= resql.org/docs/16/runtime-config-wal.html#GUC-RESTORE-COMMAND <https://www.postgresql.org/= docs/16/runtime-config-wal.html#GUC-RESTORE-COMMAND>
>
> And here: https://www.postgresql.org/d= ocs/current/warm-standby.html
> <https://www.postgresql.org/docs/cu= rrent/warm-standby.html>
>
> In practice, Postgres will see the "standby.signal" file and= start

In your OP you say:

"It downloads the basebackup, unpacks it, sets a recovery.signal, ...&= quot;

Are you setting standby.signal or recovery.signal or both?


> asking for WAL files. It will read the database it has and determine <= br> > what the next WAL filename should be. And then it asks for it. And it =
> will keep asking for these hexadecimal filenames, 1 at a time, for as =
> long as the command or set of commands provided to "restore_comma= nd"
> returns exit code 0. If the process receives any other exit code, it <= br> > stops recovery, switches timeline, and considers the database to be up=
> and running at the state its in.
>
> It's constantly asking "I want this file now" and the sc= ript I have as
> the restore command will attempt to download it from the cloud. Then i= t
> will attempt to unzip it and move it into place. If any of these steps=
> fails, I return exit code 1.
>

> Regards,
> Koen De Groote
>
>


--
Adrian Klaver
adrian.klave= r@aklaver.com

--000000000000626703062d07901e--