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 1t96Q0-007mLv-HU for pgsql-admin@arkaria.postgresql.org; Thu, 07 Nov 2024 17:35:39 +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 1t96Oz-000DhM-Ve for pgsql-admin@arkaria.postgresql.org; Thu, 07 Nov 2024 17:34:38 +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 1t96Oz-000DhC-Js for pgsql-admin@lists.postgresql.org; Thu, 07 Nov 2024 17:34:38 +0000 Received: from mail-oo1-xc33.google.com ([2607:f8b0:4864:20::c33]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t96Ox-000fQF-K0 for pgsql-admin@postgresql.org; Thu, 07 Nov 2024 17:34:36 +0000 Received: by mail-oo1-xc33.google.com with SMTP id 006d021491bc7-5ebc1af8f10so574861eaf.2 for ; Thu, 07 Nov 2024 09:34:35 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1731000875; x=1731605675; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=9cbKuMlykZDsqNpYg5/DYE0BjUNqRR4dezlv74lIWk4=; b=U6QNp6JOkVrSUid5NKyhJ3QG24bsEGKhpngRsdLF928msqrlNa7L3wP1wU9Ti4rcgv PQJcQzKd5Yk96VqQ+bvz8qE9QM3Bgf9tej6+DvG4T7f/wy9bK8JCA/JSsozdyaLT8ZwR bglT5fvi08HGtuI9+LWK744oodF7M3M3BpyMed9tz8RXDBkWGYsB98vndwXFnor217XK Roii6BWcKz3DNPgX6T6gfqsC+n94zGEZEpyZdy2Dl4qZf8Rgsw7XVvDcI4w+DCfWXYwK HbV/nC+dbzaTYBk5HMAOSlJQbzW6oGB4Fkyr1m57RMqaPNtUIxSFSR1uWsxpNHRTBosb ekew== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1731000875; x=1731605675; h=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=9cbKuMlykZDsqNpYg5/DYE0BjUNqRR4dezlv74lIWk4=; b=iRV1kYXr7/DSyUAXungfXxrAD2bWXAvwUVS6jDZd9HuBUwt2K3CJgj4MzOqtbmUnTz KJDoCCte4YwRdAjNvFFjTaNTAwSbWBAhhVq1BRfQMqmKc7PnrP0FpCQwk+m3SkVd7Vcb agvCGO+/hnXRr15drG++2wnobe2rbkLo1Q6J8XwhmgsMYdzd2VgxA9zjEFXaNHHN5ILz BRWmJVgjZm8JyRBAe2plVfTRGJvMR3vutufNK+ZpxKIgrsYvTkxuks0H/RqrWh7r3BLX oIVWqOChbCAf5EPQLYckYWmIvOfMdMoxNDEG/r3BFcgLW748sCtKNRY6CW8eV+IiTdUU jIdA== X-Gm-Message-State: AOJu0YxOkhohKakmVrfv1HBgOdOpMchDxCS2jfecPOZFHfE6WznT1mXv ulOOhxARihn5hTLzOzK5h8ynhp2qLtydBRInYkv9T4hhN1jLASxRDP7o8VQVxZp4njvb1G3gcLw RvZxmzW5jq/o+BAOiNMV8kze3tIKZ7w== X-Google-Smtp-Source: AGHT+IELVIAvoScW2WwWnJiCdPHwzG47UkF01v7OqSoZsRV6RM8htZ/th7iOUrgMaRKFc1MVV+ysp2637VF9s7ZUwbo= X-Received: by 2002:a05:6820:2018:b0:5eb:64ca:f6ef with SMTP id 006d021491bc7-5ee5698218cmr322431eaf.2.1731000874671; Thu, 07 Nov 2024 09:34:34 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Thu, 7 Nov 2024 12:34:22 -0500 Message-ID: Subject: Re: Running rsync backups in pg15 To: "pgsql-admin@postgresql.org" Content-Type: multipart/alternative; boundary="0000000000003daa260626560ced" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000003daa260626560ced Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Nov 7, 2024 at 11:35=E2=80=AFAM Murthy Nunna wrot= e: > Hi, > > > > In PG14 and earlier, there is no requirement to keep database connection > while rsync is in progress. However, there is a change in PG15+ that > requires rsync to be while we have the same database session open that > executes SELECT pg_backup_start('label'). This change requires a rewrite > of existing scripts we have. > > > > Currently (pg14): > > > > In bash script (run from cron) > > 1. psql Select pg_start_backup > 2. rsync > 3. psql Select pg_stop_backup > > > > In pg15 and later: > > > > In bash script (run from cron) > > > > psql > > Select pg_start_backup > > ! run-rsync-script > > Select pg_stop_backup > > > > It can be done, but it makes it ugly to check errors and so forth that > occur in the rsync script. > > > > Anybody found an elegant way of doing this? > Run pgbackrest instead of rsync, --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000003daa260626560ced Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Nov 7, 2024 at 11:35=E2=80=AFAM M= urthy Nunna <mnunna@fnal.gov> = wrote:

Hi,

=C2=A0<= /span>

In PG14 and earlier, = there is no requirement to keep database connection while rsync is in progr= ess. However, there is a change in PG15+ that requires rsync to be while we= have the same database session open that executes SELECT pg_backup_start('label'). This change = requires a rewrite of existing scripts we have.

=C2=A0

Currently (pg14):

=C2=A0

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 In bash script (run from cron)

  1. psql Select pg_start_backup
  2. rsync=
  3. psql Select pg_stop_backup

=C2=A0

In pg15 and later:

=C2=A0

In bash script (run from cron)

=C2=A0

psql

Select pg_start_backup

! run-rsync-script

Select pg_stop_backup

=C2=A0

It can be done, but it makes it ugly to check errors= and so forth that occur in the rsync script.

=C2=A0

Anybody found an elegant way of doing this?


Run pgbackre= st instead of rsync,

--
Death to <Redacted>, and butter sauce.
Don't boil me, I&#= 39;m still alive.
<Redacted> lobster!
<= /div>
--0000000000003daa260626560ced--