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 1tAaFb-00GZ28-4B for pgsql-admin@arkaria.postgresql.org; Mon, 11 Nov 2024 19:39:02 +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 1tAaFY-00HPhQ-IJ for pgsql-admin@arkaria.postgresql.org; Mon, 11 Nov 2024 19:39:01 +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 1tAaFX-00HPhE-Uu for pgsql-admin@lists.postgresql.org; Mon, 11 Nov 2024 19:39:00 +0000 Received: from mout.kundenserver.de ([212.227.126.131]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tAaFV-001MUn-2S for pgsql-admin@postgresql.org; Mon, 11 Nov 2024 19:38:59 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=msym.fr; s=s1-ionos; t=1731353931; x=1731958731; i=msalais@msym.fr; bh=AOZrBuSTGzjiRNGWSFSddTQFXpSJVjVukPljO8zsfFw=; h=X-UI-Sender-Class:From:To:References:In-Reply-To:Subject:Date: Message-ID:MIME-Version:Content-Type:cc:content-transfer-encoding: content-type:date:from:message-id:mime-version:reply-to:subject: to; b=sFEluGCLBFnn5LfrlfW9MBUICiWYaMr0uGtgvwVi43L45lWkoRUnHFHFNBY+3yYn PIHabWFplOmFNJnMnXBC6NipmBRugFoazUVkW3xrFukpqwOm+hQ1EMgZ4I6AtRKKU uzsiWLs0zYAcD2f3MqBUpU94DxlKEReoDr7pDjlPUU7WvGQZwjP4ffKby+zbiikdt 68wSLsnFJu5JUyeX4ga2GWvVQDaskDNAnGhbpmuHCsmiVghvji4xMyCEclqCFnn00 HLIKve6z+hcw1t1nGFwh33rbdeUqY3n9sMfnGxLEVN+vS7gy57IB3n3iqCMe4iY4B KQbIYXtxgPT6PVWTXw== X-UI-Sender-Class: 55c96926-9e95-11ee-ae09-1f7a4046a0f6 Received: from msim1 ([78.197.128.121]) by mrelayeu.kundenserver.de (mreue012 [212.227.15.168]) with ESMTPSA (Nemesis) id 1MPK73-1tRG1W1aJx-00Xqk8; Mon, 11 Nov 2024 20:38:51 +0100 From: To: "'Murthy Nunna'" , References: In-Reply-To: Subject: RE: Running rsync backups in pg15 Date: Mon, 11 Nov 2024 20:38:49 +0100 Message-ID: <026f01db3471$551dd990$ff598cb0$@msym.fr> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0270_01DB3479.B6E2B6C0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQJ5zisdvcgrzKCDqSyyVqh/HAgxk7F1Q/0A Content-Language: fr X-Provags-ID: V03:K1:0BkZiXiQ+vFe/+VEyzHmebWXQZN/TsVEksLAnkBh+Mm6kKzXTiG xS9zw+UoY2aI/LqJ53ZXBYhvZq/ElPrMzNje/zIrD303Kf3FCVHTY18D5S4kDyenMvLR3Dl VnF5Ur8R6j/cxAupFDOpMmWI7BufHKJwuY0+rSGry3tLyZcPJwbL0j8VMzc9AyS4PkDnELP IStFxzouRs7pbK8+e0KBA== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:CkVvK3i1X3A=;YPSthKwxesKWa7yI9rV+lx4tcZc HEwbgD9sW1ZdBzzK8P/hyoech446x2qM3GRRsPcvUBUyr+M3O1u37Ct4h3gnUo/qIvv70cH8a M+wBysgw2gjhZy9wMpl07UGY6Be0wdl3SV4sUL8o//MRqmz1Hrws85mZueVQQXGzfi3nKfhSs DvK0dSor3yJCjR71cPO6CZEYfledfCvoRBW3un4RYDNOj22aiOArgDv1Ndzdj5JRN0eGolqtR y+mHnqcD5U0q0/XkLz+JJdHTXy9WLOU8HHTd3hnLAi8WMtsHvy27WsnLhJEA3YV5pIndgwXgz 68y4zI6KIRLUghJuUP3hJQKOhmpQwpn0h7kPtMyYaCbtg6xJtQyIm4vaiPEn7y2Uo7JSOGUIG fh8F1N8Tjfsaj2aszAkvEF2EdsPyrvGL6FmSDZi1MSq3paVmVH95WPH1dtcvqseZQzjkWZ0YW 7awfCywPnNhzyzMALW4lMyEdAXhR8NX3V1JTMSPUhFs8CyNWPWr1/Q7SU/2M51MBbTPwDBnb9 CE9Aa+4JEIpEmQBKjY0961rf0ZlMN9S9O2qugz9o3U+JA+4GdqCdOav6rWUcmgIKqz/oKKBIz 0q5Cm0reFOKLSIujNG6gUL8KmznDlZAxJpPZo8+9LGlbxT67tw7KorejhAv29tylpMVk9u/ZD 2WY8Jx5o2/k3VJzd8DPdsajwbveXWCPjmtNkexrPjPNaU4Q3+YmdEyBNY87EkgsUjcvIHA+oe 0WvTk9gagr/bNvp8dtfzYJW+B56cLUjUQ== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0270_01DB3479.B6E2B6C0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, =20 First of all, have you ever thought what happens if your database = crashed while you are updating your single backup?! It is really a dangerous scenario there! =20 But any way, I suppose you are using psql to do your pg_start_backup(), pg_stop_backup(). What about this one (I didn=92t change function names): =20 Select pg_start_backup(); \! your_rsync_script=85 Select pg_stop_bacup(); =20 This could be more sophisticated if you want=85 =20 Regards =20 Michel SALAIS De : Murthy Nunna =20 Envoy=E9 : jeudi 7 novembre 2024 17:35 =C0 : pgsql-admin@postgresql.org Objet : Running rsync backups in pg15 =20 Hi, =20 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. =20 Currently (pg14): =20 In bash script (run from cron) 1. psql Select pg_start_backup 2. rsync 3. psql Select pg_stop_backup =20 In pg15 and later: =20 In bash script (run from cron) =20 psql Select pg_start_backup ! run-rsync-script Select pg_stop_backup =20 It can be done, but it makes it ugly to check errors and so forth that = occur in the rsync script. =20 Anybody found an elegant way of doing this? =20 Thank you in advance for your ideas. =20 =20 ------=_NextPart_000_0270_01DB3479.B6E2B6C0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi,

 

First of all, have you ever thought = what happens if your database crashed while you are updating your single = backup?! It is really a dangerous scenario = there!

 

But any way, I suppose you are = using psql to do your pg_start_backup(), = pg_stop_backup().

What about this one (I = didn’t change function names):

 

Select = pg_start_backup();

\! = your_rsync_script…

Select = pg_stop_bacup();

 

This could be more sophisticated if = you want…

 

Regards

 

Michel = SALAIS

De : Murthy Nunna <mnunna@fnal.gov> =
Envoy=E9 : jeudi 7 novembre 2024 = 17:35
=C0 : = pgsql-admin@postgresql.org
Objet : Running rsync backups = in pg15

 

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?

 

Thank you in advance for your = ideas.

 

 

------=_NextPart_000_0270_01DB3479.B6E2B6C0--