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 1hduUP-0005L3-5W for pgsql-admin@arkaria.postgresql.org; Thu, 20 Jun 2019 10:40:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hduUN-0004Gl-1H for pgsql-admin@arkaria.postgresql.org; Thu, 20 Jun 2019 10:40:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hduUM-0004Ge-OD for pgsql-admin@lists.postgresql.org; Thu, 20 Jun 2019 10:40:18 +0000 Received: from hermes.gatewaynet.com ([193.92.121.98]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hduUJ-0003ID-HW for pgsql-admin@lists.postgresql.org; Thu, 20 Jun 2019 10:40:18 +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 x5KAe821015475 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT) for ; Thu, 20 Jun 2019 13:40:13 +0300 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id x5KAe8Ve062774 for ; Thu, 20 Jun 2019 13:40:08 +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: <20190620121251.418294c0@firost> From: Achilleas Mantzios Message-ID: <62943f11-43f6-fd4b-6311-92b7fce90670@matrix.gatewaynet.com> Date: Thu, 20 Jun 2019 13:40:08 +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: <20190620121251.418294c0@firost> Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Content-Language: en-US X-Virus-Scanned: clamav-milter 0.100.3 at hermes X-Virus-Status: Clean X-Spam-Status: No, score=-35.9 required=5.0 tests=ALL_TRUSTED,DYN_HAM_COORD, DYN_HAM_IT,DYN_HAM_IT_PARTNERS,DYN_HAM_NAUTICAL,DYN_SPAM_SUBJ,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 On 20/6/19 1:12 μ.μ., Jehan-Guillaume (ioguix) de Rorthais wrote: > On Thu, 20 Jun 2019 08:37:21 +0300 > Achilleas Mantzios wrote: > >> 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. > No, this could be more than 16MB. Archiving might be lagging as well depending > on various circumstances and activity. In this case we have more than one problem. In such cases pg_wal will be quickly stuffed with unarchived files, this indicates another (usually network or some sudden unforeseen spike in activity) problem. So yes, in such cases where we have a network or performance problem AND at the same time disaster in the cluster occurs, then it would be much more than 16MB, but then again we are having two problems now to solve (DB loss + infrastructure). > >> 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 . > This will still allow data loss. I said minimize. > > > If you don't want a secondary in sync, you might want to have a look at > pg_receivewal. > > ++ > > -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt