pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedzero data loss recovery is possbile with pgbackrest tool?
8+ messages / 5 participants
[nested] [flat]
* zero data loss recovery is possbile with pgbackrest tool?
@ 2019-06-19 21:14 Pavan Kumar <pavan.dba27@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Pavan Kumar @ 2019-06-19 21:14 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hello experts,
pgbackrest tool is possible to perform zero data loss recovery?
I did couple of test case where I have end up with data loss (data exist in
current wal).
please share your experience/suggestion to perform complete recovery with
this tool
--
*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 *
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: zero data loss recovery is possbile with pgbackrest tool?
@ 2019-06-19 21:23 Ron <ronljohnsonjr@gmail.com>
parent: Pavan Kumar <pavan.dba27@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Ron @ 2019-06-19 21:23 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 6/19/19 4:14 PM, Pavan Kumar wrote:
> Hello experts,
>
> pgbackrest tool is possible to perform zero data loss recovery?
> I did couple of test case where I have end up with data loss (data exist
> in current wal).
>
> please share your experience/suggestion to perform complete recovery with
> this tool
Set the checkpoint timeout to something closer to what you want.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: zero data loss recovery is possbile with pgbackrest tool?
@ 2019-06-19 21:33 Rui DeSousa <rui@crazybean.net>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Rui DeSousa @ 2019-06-19 21:33 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> 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?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: zero data loss recovery is possbile with pgbackrest tool?
@ 2019-06-19 21:40 Pavan Kumar <pavan.dba27@gmail.com>
parent: Rui DeSousa <rui@crazybean.net>
0 siblings, 2 replies; 8+ messages in thread
From: Pavan Kumar @ 2019-06-19 21:40 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: Ron <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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?
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 *
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: zero data loss recovery is possbile with pgbackrest tool?
@ 2019-06-20 04:31 Rui DeSousa <rui@crazybean.net>
parent: Pavan Kumar <pavan.dba27@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Rui DeSousa @ 2019-06-20 04:31 UTC (permalink / raw)
To: Pavan Kumar <pavan.dba27@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Jun 19, 2019, at 5:40 PM, Pavan Kumar <pavan.dba27@gmail.com> wrote:
>
> 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?
>
If the WAL is not archived and if losing the primary without having access to the current WAL then data loss would result. A way to avoid the scenario is with an HA solution that involves synchronous replication thus any committed transaction would exist on multiple nodes.
I’m not familiar enough with pgbackrest to offer much help there; but it seems like a backup issue as you are losing files and directories with a given backup set.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: zero data loss recovery is possbile with pgbackrest tool?
@ 2019-06-20 05:37 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Pavan Kumar <pavan.dba27@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Achilleas Mantzios @ 2019-06-20 05:37 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 <mailto:rui@crazybean.net>> wrote:
>
>
>
> > On Jun 19, 2019, at 5:23 PM, Ron <ronljohnsonjr@gmail.com <mailto: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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: zero data loss recovery is possbile with pgbackrest tool?
@ 2019-06-20 10:12 Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 8+ messages in thread
From: Jehan-Guillaume (ioguix) de Rorthais @ 2019-06-20 10:12 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin@lists.postgresql.org
On Thu, 20 Jun 2019 08:37:21 +0300
Achilleas Mantzios <achill@matrix.gatewaynet.com> 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.
> 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. No matter the value. Moreover, there no sense
setting it bellow eg. 1min. If you want something smaller, you really want
replication.
If you don't want a secondary in sync, you might want to have a look at
pg_receivewal.
++
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: zero data loss recovery is possbile with pgbackrest tool?
@ 2019-06-20 10:40 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
0 siblings, 0 replies; 8+ messages in thread
From: Achilleas Mantzios @ 2019-06-20 10:40 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 20/6/19 1:12 μ.μ., Jehan-Guillaume (ioguix) de Rorthais wrote:
> On Thu, 20 Jun 2019 08:37:21 +0300
> Achilleas Mantzios <achill@matrix.gatewaynet.com> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2019-06-20 10:40 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-06-19 21:14 zero data loss recovery is possbile with pgbackrest tool? Pavan Kumar <pavan.dba27@gmail.com>
2019-06-19 21:23 ` Ron <ronljohnsonjr@gmail.com>
2019-06-19 21:33 ` Rui DeSousa <rui@crazybean.net>
2019-06-19 21:40 ` Pavan Kumar <pavan.dba27@gmail.com>
2019-06-20 04:31 ` Rui DeSousa <rui@crazybean.net>
2019-06-20 05:37 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2019-06-20 10:12 ` Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
2019-06-20 10:40 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox