pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
WAL segment question
5+ messages / 4 participants
[nested] [flat]

* WAL segment question
@ 2007-08-03 23:00  Donald Fraser <postgres@kiwi-fraser.net>
  0 siblings, 3 replies; 5+ messages in thread

From: Donald Fraser @ 2007-08-03 23:00 UTC (permalink / raw)
  To: pgsql-admin

Our company has two sites, a master and a disaster recovery site.
I am trying to assess whether data has been lost during a fail-over, due to the asynchronous method of transporting WAL file data between sites (we use DRBD).

Disaster recovery node start-up log:
user= pid=9907 timestamp=[2007-08-01 17:09:51 BST] tid= LOG:  record with zero length at 0/7C62F52C
user= pid=9907 timestamp=[2007-08-01 17:09:51 BST] tid= LOG:  redo done at 0/7C62F4E8

My question is: given the above WAL segment information, can I use it to decide whether information was lost in the master database site, at the point of failure, without starting the database up?

Regards
Donald Fraser.

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: WAL segment question
@ 2007-08-04 00:21  Decibel! <decibel@decibel.org>
  parent: Donald Fraser <postgres@kiwi-fraser.net>
  2 siblings, 0 replies; 5+ messages in thread

From: Decibel! @ 2007-08-04 00:21 UTC (permalink / raw)
  To: Donald Fraser <postgres@kiwi-fraser.net>; +Cc: pgsql-admin

On Sat, Aug 04, 2007 at 12:00:52AM +0100, Donald Fraser wrote:
> Our company has two sites, a master and a disaster recovery site.
> I am trying to assess whether data has been lost during a fail-over, due to the asynchronous method of transporting WAL file data between sites (we use DRBD).
> 
> Disaster recovery node start-up log:
> user= pid=9907 timestamp=[2007-08-01 17:09:51 BST] tid= LOG:  record with zero length at 0/7C62F52C
> user= pid=9907 timestamp=[2007-08-01 17:09:51 BST] tid= LOG:  redo done at 0/7C62F4E8
> 
> My question is: given the above WAL segment information, can I use it to decide whether information was lost in the master database site, at the point of failure, without starting the database up?

I'm not sure, but you could try md5-ing the appropriate WAL file on the
master and slave; if the files are identical then you shouldn't have
lost any data... if they are then you probably have.
-- 
Decibel!, aka Jim Nasby                        decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: WAL segment question
@ 2007-08-05 20:58  Simon Riggs <simon@2ndquadrant.com>
  parent: Donald Fraser <postgres@kiwi-fraser.net>
  2 siblings, 0 replies; 5+ messages in thread

From: Simon Riggs @ 2007-08-05 20:58 UTC (permalink / raw)
  To: Donald Fraser <postgres@kiwi-fraser.net>; +Cc: pgsql-admin

On Sat, 2007-08-04 at 00:00 +0100, Donald Fraser wrote:
> Our company has two sites, a master and a disaster recovery site.
> I am trying to assess whether data has been lost during a fail-over,
> due to the asynchronous method of transporting WAL file data between
> sites (we use DRBD).
>  
> Disaster recovery node start-up log:
> user= pid=9907 timestamp=[2007-08-01 17:09:51 BST] tid= LOG:  record
> with zero length at 0/7C62F52C
> user= pid=9907 timestamp=[2007-08-01 17:09:51 BST] tid= LOG:  redo
> done at 0/7C62F4E8
>  
> My question is: given the above WAL segment information, can I use it
> to decide whether information was lost in the master database site, at
> the point of failure, without starting the database up?

Currently there is no built-in means of finding out the time of the last
record transferred, if the primary stays down. You need to use xlogdump
to locate the time of the last commit/abort record.

-- 
  Simon Riggs
  EnterpriseDB  http://www.enterprisedb.com




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: WAL segment question
@ 2007-08-06 22:54  Chander Ganesan <chander@otg-nc.com>
  parent: Donald Fraser <postgres@kiwi-fraser.net>
  2 siblings, 1 reply; 5+ messages in thread

From: Chander Ganesan @ 2007-08-06 22:54 UTC (permalink / raw)
  To: Donald Fraser <postgres@kiwi-fraser.net>; +Cc: pgsql-admin

Donald Fraser wrote:
> Our company has two sites, a master and a disaster recovery site.
> I am trying to assess whether data has been lost during a fail-over, 
> due to the asynchronous method of transporting WAL file data between 
> sites (we use DRBD).
>  
> Disaster recovery node start-up log:
> user= pid=9907 timestamp=[2007-08-01 17:09:51 BST] tid= LOG:  record 
> with zero length at 0/7C62F52C
> user= pid=9907 timestamp=[2007-08-01 17:09:51 BST] tid= LOG:  redo 
> done at 0/7C62F4E8
>  
> My question is: given the above WAL segment information, can I use it 
> to decide whether information was lost in the master database site, at 
> the point of failure, without starting the database up?
>  
> Regards
> Donald Fraser.
DRBD is a synchronous method of transferring data - it's not 
asynchronous.  Assuming you are using Protocol C you should find that 
both are always in sync.  If you are using protocol B then things will 
be in sync so long as the remote system didn't crash, and if you are 
using Protocol A then things should be in sync as long as the local 
system didn't crash.

-- 
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC  27560
919-463-0999/866-229-3386
http://www.otg-nc.com

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: WAL segment question
@ 2007-08-06 23:59  Donald Fraser <postgres@kiwi-fraser.net>
  parent: Chander Ganesan <chander@otg-nc.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Donald Fraser @ 2007-08-06 23:59 UTC (permalink / raw)
  To: pgsql-admin


----- Original Message ----- 
From: Chander Ganesan
> DRBD is a synchronous method of transferring data - it's not asynchronous.
Assuming you are using Protocol C you should find that both are always in
sync.  If you are using protocol B then things will be in sync so long as
the remote system didn't crash, and if you are using Protocol A then things
should be in sync as long as the local system didn't crash.

Apologies if you thought I was insinuating DRBD is categorically
asynchronous.
But you assumed wrong, we use protocol A, because we don't have a fast WAN
link < 10Mbps.
According to the DRBD documentation:
Protocol A: write IO is reported as completed, if it has reached local disk
and local tcp send buffer.

Which I interpret as: A for Asynchronous, which means I can complete writing
data and start writing the next set of data before the first set of data has
been synchronised at the remote site.

This is the cost of our system - we are willing to loose data for improved
performance of our master server. We've had one failure in 7 years that I've
been at the company... Many thanks to Linux and more importantly PostgreSQL!

One day when our local telecomms company stop the monopoly on leased data
lines, we may upgrade our DR link to a 100Mbps or even 1Gbps link, in which
case, protocol B or C will be a better solution. Unfortunately I think
there's a better chance that the cost of postage will go down first.






^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2007-08-06 23:59 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-03 23:00 WAL segment question Donald Fraser <postgres@kiwi-fraser.net>
2007-08-04 00:21 ` Decibel! <decibel@decibel.org>
2007-08-05 20:58 ` Simon Riggs <simon@2ndquadrant.com>
2007-08-06 22:54 ` Chander Ganesan <chander@otg-nc.com>
2007-08-06 23:59   ` Donald Fraser <postgres@kiwi-fraser.net>

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