Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i33Yp-0007CD-CK for pgsql-admin@arkaria.postgresql.org; Wed, 28 Aug 2019 19:24:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1i33Yo-0005oT-2o for pgsql-admin@arkaria.postgresql.org; Wed, 28 Aug 2019 19:24:50 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i33Yn-0005oM-ND for pgsql-admin@lists.postgresql.org; Wed, 28 Aug 2019 19:24:49 +0000 Received: from impout008aa.msg.chrl.nc.charter.net ([47.43.20.32] helo=impout008.msg.chrl.nc.charter.net) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1i33Yl-0003wl-9B for pgsql-admin@lists.postgresql.org; Wed, 28 Aug 2019 19:24:48 +0000 Received: from cerberus.crazybean.net ([24.151.10.150]) by cmsmtp with ESMTP id 33YjiKGWZMMJt33YjibdLQ; Wed, 28 Aug 2019 19:24:46 +0000 Authentication-Results: crazybean.net; none X-Authority-Analysis: v=2.3 cv=QtjqIm6d c=1 sm=1 tr=0 a=D2HTw/M6/8MmcHighGO7rg==:117 a=D2HTw/M6/8MmcHighGO7rg==:17 a=jpOVt7BSZ2e4Z31A5e1TngXxSK0=:19 a=xqWC_Br6kY4A:10 a=FmdZ9Uzk2mMA:10 a=pGLkceISAAAA:8 a=epTmVMiNAAAA:8 a=3zSae08lJmafUfs0ISwA:9 a=SnVwWKAxRNNRDRlg:21 a=i5nyA12XMJNwi-s2:21 a=QEXdDO2ut3YA:10 a=eeHAjntkAEkA:10 a=l40T8sb8AQQA:10 a=SVl2aYXhbPgDLD9xko4A:9 a=7nFiTlcZiOpunjGL:21 a=X0_LPUHOw5AlXUNM:21 a=vGrAmcNDS0RwZe8g:21 a=_W_S_7VecoQA:10 a=ndEWmUVY6Yapc0oHF_P4:22 Received: from [IPv6:2001:470:e117:4:f1b1:7ed9:a416:c22e] (2001:470:e117:4:f1b1:7ed9:a416:c22e [IPv6:2001:470:e117:4:f1b1:7ed9:a416:c22e]) by cerberus.crazybean.net (OpenSMTPD) with ESMTPSA id 2205af6a (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO); Wed, 28 Aug 2019 15:24:45 -0400 (EDT) From: Rui DeSousa Message-Id: <61A32609-4552-4F4D-9B88-EF15C06010C1@crazybean.net> Content-Type: multipart/alternative; boundary="Apple-Mail=_A1AEFC99-C689-465A-BEAF-29CE259E21F6" Mime-Version: 1.0 (Mac OS X Mail 12.4 \(3445.104.11\)) Subject: Re: Backup From HotStandby Date: Wed, 28 Aug 2019 15:24:44 -0400 In-Reply-To: Cc: Jeff Janes , pgsql-admin@lists.postgresql.org To: Licio Matos References: X-Mailer: Apple Mail (2.3445.104.11) X-CMAE-Envelope: MS4wfNnWDlU9AB3jArq/2+a/3xj/vECxp/I7/4teqLYpkpjvcKmHfOkoY6GQam9hIeqSrWbfQor4TDZDG0nBb9FoubkHobZ4FjhK+Kje16CI7CSRIvlF064i sCitBxeto9NEdWQjOP6y8WdSKcAK7J0869xxlIWIyHOWWxQwZ44eWUHy+Q779CQnOwZmTlTlvo9bcYYcvStT5NK0imNIQ1i1NoNmAEiz2ZpIAIW7dKqLkEhz FD5GtvDpOPixl/EIMoUbCkTquQuVfO/fTo8LXr2vCvk= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_A1AEFC99-C689-465A-BEAF-29CE259E21F6 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Aug 28, 2019, at 2:40 PM, Licio Matos = wrote: >=20 > Tks Jeff, >=20 > But in this case i am not using pg_basebackup, but i make = pg_start_backup() and pg_stop_backup() in the script and make a = snapshot. > So, i believe this work too, right? >=20 > Tks again. >=20 > Licio Matos >=20 > Em qua, 28 de ago de 2019 =C3=A0s 15:24, Jeff Janes = > escreveu: > On Wed, Aug 28, 2019 at 1:42 PM Licio Matos > wrote: > Hi Guys, >=20 > Is it possible to make a backup snapshot from a hotstandby (slave) = that is receiving WAL stream from a master on postgresql 9.6? >=20 > Yes, but with some restrictions. See the docs for your version = (https://www.postgresql.org/docs/9.6/app-pgbasebackup.html = ) starting = with "pg_basebackup can make a base backup from not only the master but = also the standby." >=20 > Cheers, >=20 > Jeff Licio, Yes, you can make a backup using a snapshot given that you can assure it = is an atomic operation across the entire PostgreSQL datafiles. What = snapshot technology are you using and how is your datafiles layed out? If Postgres resides on a single volume and a snapshot is taken of that = volume then the result would be a crash recovery backup. There is no = need to run pg_start_backup() in this case nor can it be run on a = replica. I=E2=80=99ve been doing this type of backups for a better part = of decade with great success. The benefit is zero impact on the primary = database and multiple backups can be taken throught the day to reduce = recovery times in a large and high volume database.=20 If you can=E2=80=99t guarantee an atomic operations across all the = datafiles then you need to use a different method. i.e. shutdown the = replica, take the snapshot backups, and start the replica again. --Apple-Mail=_A1AEFC99-C689-465A-BEAF-29CE259E21F6 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On Aug 28, 2019, at 2:40 PM, Licio Matos <licio.matos@gmail.com> wrote:

Tks Jeff,

But in this case i am not using pg_basebackup, but i make = pg_start_backup() and pg_stop_backup() in the script and make a = snapshot.
So, i believe this work = too, right?

Tks = again.

Licio = Matos

Em qua, 28 = de ago de 2019 =C3=A0s 15:24, Jeff Janes <jeff.janes@gmail.com> escreveu:
On Wed, Aug 28, 2019 at 1:42 PM Licio Matos = <licio.matos@gmail.com> wrote:
Hi = Guys,

Is it possible = to make a backup snapshot from a hotstandby (slave) that is receiving = WAL stream from a master on postgresql 9.6?

 Yes, but with some = restrictions.  See the docs for your version (https://www.postgresql.org/docs/9.6/app-pgbasebackup.html) = starting with "pg_basebackup can make a base backup from not only the = master but also the standby."

Cheers,

Jeff

Licio,

Yes, you can make a = backup using a snapshot given that you can assure it is an atomic = operation across the entire PostgreSQL datafiles.  What snapshot = technology are you using and how is your datafiles layed out?

If Postgres resides on a = single volume and a snapshot is taken of that volume then the result = would be a crash recovery backup.  There is no need to run = pg_start_backup() in this case nor can it be run on a replica. =  I=E2=80=99ve been doing this type of backups for a better part of = decade with great success.  The benefit is zero impact on the = primary database and multiple backups can be taken throught the day to = reduce recovery times in a large and high volume = database. 

If you can=E2=80=99t guarantee an atomic operations = across all the datafiles then you need to use a different method. =  i.e. shutdown the replica, take the snapshot backups, and start = the replica again.

= --Apple-Mail=_A1AEFC99-C689-465A-BEAF-29CE259E21F6--