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 1hdpmP-0001KL-CI for pgsql-admin@arkaria.postgresql.org; Thu, 20 Jun 2019 05:38:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hdplP-0004Pm-Bh for pgsql-admin@arkaria.postgresql.org; Thu, 20 Jun 2019 05:37:35 +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 1hdplO-0004M8-Sh for pgsql-admin@lists.postgresql.org; Thu, 20 Jun 2019 05:37:35 +0000 Received: from hermes.gatewaynet.com ([193.92.121.98]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hdplL-0005k1-Ej for pgsql-admin@lists.postgresql.org; Thu, 20 Jun 2019 05:37:33 +0000 Received: from smadev.internal.net (pc131.internal.net [10.9.200.131]) by hermes.gatewaynet.com (8.15.2/8.15.2/Debian-8) with ESMTPS id x5K5bL5p014990 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT) for ; Thu, 20 Jun 2019 08:37:26 +0300 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id x5K5bLXC049819 for ; Thu, 20 Jun 2019 08:37:21 +0300 (EEST) (envelope-from achill@matrix.gatewaynet.com) Subject: Re: zero data loss recovery is possbile with pgbackrest tool? To: pgsql-admin@lists.postgresql.org References: From: Achilleas Mantzios Message-ID: Date: Thu, 20 Jun 2019 08:37:21 +0300 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:60.0) Gecko/20100101 Thunderbird/60.3.2 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------9F05EBBF9E895DF3F1235453" Content-Language: en-US X-Virus-Scanned: clamav-milter 0.100.3 at hermes X-Virus-Status: Clean X-Spam-Status: No, score=-27.6 required=5.0 tests=ALL_TRUSTED,DYN_HAM_BRANCH_1, DYN_HAM_BRANCH_2,DYN_HAM_COORD,DYN_HAM_IT,DYN_HAM_NAUTICAL, DYN_HAM_SIGNAT1,DYN_SPAM_SUBJ,HTML_FONT_FACE_BAD,HTML_MESSAGE,TW_PG autolearn=unavailable autolearn_force=no version=3.4.2 X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on hermes.gatewaynet.com List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------9F05EBBF9E895DF3F1235453 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 20/6/19 12:40 π.μ., Pavan Kumar wrote: > Hello Rui, > > yes those are committed transaction. > > I have tested two cases > > 1. complete lost. (means complete data directory, pg_wal direcoty) > 2. lost few directories (deleted few directories , except pg_wal). > > both cases I do see latest data in the current wal is lost. > > when I check the recovery process , wal archives are copying from pgbackrest repository to pg_wal directory. however pgbackrest tool is not copying current wal log at all. it is only copying > archived wal's/ > > is there any way to copy current wal log with pgbackrest tool? No tool that is based on archiving is able to do that. Incomplete wals don't get archived till they are complete. So, you have the risk of losing (maximum) 16MB worth of data. If you want to minimize the effect of this, you should set https://www.postgresql.org/docs/11/runtime-config-wal.html#GUC-ARCHIVE-TIMEOUT (|archive_timeout|) to a smaller value . In order to achieve zero data loss, in general, you have to use a tool that can support synchronous streaming replication. For an overview you may look here : https://severalnines.com/blog/current-state-open-source-backup-management-postgresql > > > On Wed, Jun 19, 2019 at 4:33 PM Rui DeSousa > wrote: > > > > > On Jun 19, 2019, at 5:23 PM, Ron > wrote: > > > > Set the checkpoint timeout to something closer to what you want. > > > > > > Checkpoint does not matter. > > You shouldn’t loose committed transactions.  Was the transaction committed? Do you have all the WALs? > > > > > > -- > *Regards, > > #!  Pavan Kumar > ----------------------------------------------*- > *Sr. Database Administrator..!* > *NEXT GENERATION PROFESSIONALS, LLC* > *Cell    #  267-799-3182 #  pavan.dba27 (Gtalk) * > *India   # 9000459083* > > *Take Risks; if you win, you will be very happy. If you lose you will be Wise * > -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt --------------9F05EBBF9E895DF3F1235453 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
On 20/6/19 12:40 π.μ., Pavan Kumar wrote:
Hello Rui,

yes those are committed transaction. 

I have tested two cases

1. complete lost. (means complete data directory, pg_wal direcoty)
2. lost few directories (deleted few directories , except pg_wal).

both cases I do see latest data in the current wal is lost.

when I check the recovery process , wal archives are copying from pgbackrest repository to pg_wal directory. however pgbackrest tool is not copying current wal log at all. it is only copying archived wal's/

is there any way to copy current wal log with pgbackrest tool?

No tool that is based on archiving is able to do that. Incomplete wals don't get archived till they are complete. So, you have the risk of losing (maximum) 16MB worth of data.
If you want to minimize the effect of this, you should set https://www.postgresql.org/docs/11/runtime-config-wal.html#GUC-ARCHIVE-TIMEOUT (archive_timeout) to a smaller value .
In order to achieve zero data loss, in general, you have to use a tool that can support synchronous streaming replication. For an overview you may look here :
https://severalnines.com/blog/current-state-open-source-backup-management-postgresql



On Wed, Jun 19, 2019 at 4:33 PM Rui DeSousa <rui@crazybean.net> wrote:


> On Jun 19, 2019, at 5:23 PM, Ron <ronljohnsonjr@gmail.com> wrote:
>
> Set the checkpoint timeout to something closer to what you want.
>
>

Checkpoint does not matter.

You shouldn’t loose committed transactions.  Was the transaction committed? Do you have all the WALs?





--
Regards,

#!  Pavan Kumar
-----------------------------------------------
Sr. Database Administrator..!
NEXT GENERATION PROFESSIONALS, LLC
Cell    #  267-799-3182 #  pavan.dba27 (Gtalk)  
India   # 9000459083

Take Risks; if you win, you will be very happy. If you lose you will be Wise  


-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
--------------9F05EBBF9E895DF3F1235453--