pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Multiple WALs for PITR ?
9+ messages / 7 participants
[nested] [flat]

* Multiple WALs for PITR ?
@ 2004-10-06 14:29 Michael Kleiser <mkl@webde-ag.de>
  2004-10-06 22:58 ` Re: Multiple WALs for PITR ? Gaetano Mendola <mendola@bigfoot.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Michael Kleiser @ 2004-10-06 14:29 UTC (permalink / raw)
  To: pgsql-admin

I wan`t to use Point in Time Recovery
I allread read http://developer.postgresql.org/docs/postgres/backup-online.html#BACKUP-ARCHIVING-WAL

But I still wonder, if it is possible for PostgreSQL 8.0 to write multiple, redundant WAL-Files
like Oracles with its redo-log-groups ?

Is threre an equivalent to Oracles "ALTER SYSTEM SWITCH LOGFILE" ?

Thanks
Michael Kleiser



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

* Re: Multiple WALs for PITR ?
  2004-10-06 14:29 Multiple WALs for PITR ? Michael Kleiser <mkl@webde-ag.de>
@ 2004-10-06 22:58 ` Gaetano Mendola <mendola@bigfoot.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Gaetano Mendola @ 2004-10-06 22:58 UTC (permalink / raw)
  To: pgsql-admin

Michael Kleiser wrote:
> I wan`t to use Point in Time Recovery
> I allread read 
> http://developer.postgresql.org/docs/postgres/backup-online.html#BACKUP-ARCHIVING-WAL 
> 
> 
> But I still wonder, if it is possible for PostgreSQL 8.0 to write 
> multiple, redundant WAL-Files
> like Oracles with its redo-log-groups ?

Could you explain what do this feature that PITR implementation don't support ?


> Is threre an equivalent to Oracles "ALTER SYSTEM SWITCH LOGFILE" ?

Given that this is Postgresql list could you kindly explain what do this command,
and also why do you think Postgresql must support it?


Regards
Gaetano Mendola



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

* Re:  Multiple WALs for PITR ?
@ 2004-10-06 21:36 simon@2ndquadrant.com
  2004-10-07 15:29 ` Re: Multiple WALs for PITR ? JEDIDIAH <jedi@nomad.mishnet>
  0 siblings, 1 reply; 9+ messages in thread

From: simon@2ndquadrant.com @ 2004-10-06 21:36 UTC (permalink / raw)
  To: mkl@webde-ag.de; +Cc: pgsql-admin


Michael Kleiser <mkl@webde-ag.de> wrote on 06.10.2004, 16:29:08:
> I wan`t to use Point in Time Recovery
> I allread read http://developer.postgresql.org/docs/postgres/backup-online.html#BACKUP-ARCHIVING-WAL
> 

Good!

> But I still wonder, if it is possible for PostgreSQL 8.0 to write multiple, redundant WAL-Files
> like Oracles with its redo-log-groups ?

Not currently.

That was something I considered, though in the end lacked both
implementation time and justification for.

If you can explain why you think that might be necessary, I'd be happy
to consider it for v8.1. 

RAID-10 seems a good solution for me...

> 
> Is threre an equivalent to Oracles "ALTER SYSTEM SWITCH LOGFILE" ?
> 

Not currently.

Again, if you can say why we might want that, it can also be added -
other than "they have it, so we want it too" :-)

This is a likely addition for 8.1, since we will most likely want to
archive xlog files every N minutes, whatever the transaction rate,
allowing people to specify a maximum period of data loss - but this
seemed like an enhancement rather than a definite feature for 8.0

> Thanks
> Michael Kleiser
> 

Glad you're interested. Your questions show you understand - good luck.

Best Regards

Simon Riggs



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

* Re: Multiple WALs for PITR ?
  2004-10-06 21:36 Re:  Multiple WALs for PITR ? simon@2ndquadrant.com
@ 2004-10-07 15:29 ` JEDIDIAH <jedi@nomad.mishnet>
  0 siblings, 0 replies; 9+ messages in thread

From: JEDIDIAH @ 2004-10-07 15:29 UTC (permalink / raw)
  To: pgsql-admin

On 2004-10-06, simon@2ndquadrant.com <simon@2ndquadrant.com> wrote:
>
> Michael Kleiser <mkl@webde-ag.de> wrote on 06.10.2004, 16:29:08:
>> I wan`t to use Point in Time Recovery
>> I allread read http://developer.postgresql.org/docs/postgres/backup-online.html#BACKUP-ARCHIVING-WAL
>> 
>
> Good!
>
>> But I still wonder, if it is possible for PostgreSQL 8.0 to write multiple, redundant WAL-Files
>> like Oracles with its redo-log-groups ?
>
> Not currently.
>
> That was something I considered, though in the end lacked both
> implementation time and justification for.

	WAL archival is more decoupled from WAL generation.

	You can use this to exploit more IO parallelism so that the same
disk isn't being written to and read from at the same time.

	Now, the concept of WAL groups would provide an extra level of
redundancy for your crash recovery files. On some of the cheaper sort of
systems that I've seen postgres deployed on this could be very useful.

[deletia]

-- 
        Negligence will never equal intent, no matter how you 
attempt to distort reality to do so. This is what separates         ||| 
the real butchers from average Joes (or Fritzes) caught up in      / | \
events not in their control.


                                                     



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

* Re: Multiple WALs for PITR ?
@ 2004-10-07 01:56 Goulet, Dick <DGoulet@vicr.com>
  2004-10-07 08:38 ` Re: Multiple WALs for PITR ? Michael Kleiser <mkl@webde-ag.de>
  2004-10-07 20:58 ` Re: Multiple WALs for PITR ? Simon Riggs <simon@2ndquadrant.com>
  0 siblings, 2 replies; 9+ messages in thread

From: Goulet, Dick @ 2004-10-07 01:56 UTC (permalink / raw)
  To: simon@2ndquadrant.com; mkl@webde-ag.de; +Cc: pgsql-admin

As an Oracle DBA primarily, moving more towards PostGreSQL as time
passes, the "alter system switch logfile" command is very often used at
the end of a hot backup to force a logfile switch and consequently
provide a full archive log set.  The command causes the database engine
to cease using the current online redo log and have it archived to the
archive log destination.  This is the only way to be sure that all
required archive logs are present in a backup to reconstitute the
database at another location.


Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: simon@2ndquadrant.com [mailto:simon@2ndquadrant.com] 
Sent: Wednesday, October 06, 2004 5:36 PM
To: mkl@webde-ag.de
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Multiple WALs for PITR ?


Michael Kleiser <mkl@webde-ag.de> wrote on 06.10.2004, 16:29:08:
> I wan`t to use Point in Time Recovery
> I allread read 
> http://developer.postgresql.org/docs/postgres/backup-online.html#BACKU
> P-ARCHIVING-WAL
> 

Good!

> But I still wonder, if it is possible for PostgreSQL 8.0 to write 
> multiple, redundant WAL-Files like Oracles with its redo-log-groups ?

Not currently.

That was something I considered, though in the end lacked both
implementation time and justification for.

If you can explain why you think that might be necessary, I'd be happy
to consider it for v8.1. 

RAID-10 seems a good solution for me...

> 
> Is threre an equivalent to Oracles "ALTER SYSTEM SWITCH LOGFILE" ?
> 

Not currently.

Again, if you can say why we might want that, it can also be added -
other than "they have it, so we want it too" :-)

This is a likely addition for 8.1, since we will most likely want to
archive xlog files every N minutes, whatever the transaction rate,
allowing people to specify a maximum period of data loss - but this
seemed like an enhancement rather than a definite feature for 8.0

> Thanks
> Michael Kleiser
> 

Glad you're interested. Your questions show you understand - good luck.

Best Regards

Simon Riggs

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
      joining column's datatypes do not match



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

* Re: Multiple WALs for PITR ?
  2004-10-07 01:56 Re: Multiple WALs for PITR ? Goulet, Dick <DGoulet@vicr.com>
@ 2004-10-07 08:38 ` Michael Kleiser <mkl@webde-ag.de>
  1 sibling, 0 replies; 9+ messages in thread

From: Michael Kleiser @ 2004-10-07 08:38 UTC (permalink / raw)
  To: simon@2ndquadrant.com; +Cc: pgsql-admin



Goulet, Dick wrote:
> As an Oracle DBA primarily, moving more towards PostGreSQL as time
> passes, the "alter system switch logfile" command is very often used at
> the end of a hot backup to force a logfile switch and consequently
> provide a full archive log set.  The command causes the database engine
> to cease using the current online redo log and have it archived to the
> archive log destination.  This is the only way to be sure that all
> required archive logs are present in a backup to reconstitute the
> database at another location.
> 
> 
> Dick Goulet
> Senior Oracle DBA
> Oracle Certified 8i DBA
> -----Original Message-----
> From: simon@2ndquadrant.com [mailto:simon@2ndquadrant.com]
> Sent: Wednesday, October 06, 2004 5:36 PM
> To: mkl@webde-ag.de
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Multiple WALs for PITR ?
> 
> 
> Michael Kleiser <mkl@webde-ag.de> wrote on 06.10.2004, 16:29:08:
>  > I wan`t to use Point in Time Recovery
>  > I allread read
>  > http://developer.postgresql.org/docs/postgres/backup-online.html#BACKU
>  > P-ARCHIVING-WAL
>  >
> 
> Good!
> 
>  > But I still wonder, if it is possible for PostgreSQL 8.0 to write
>  > multiple, redundant WAL-Files like Oracles with its redo-log-groups ?
> 
> Not currently.
> 
> That was something I considered, though in the end lacked both
> implementation time and justification for.
> 
> If you can explain why you think that might be necessary, I'd be happy
> to consider it for v8.1.
> 
> RAID-10 seems a good solution for me...

I heared about some RAIDs saying "yes your data is on disk"
to the oracle-database, but it wasn't so.
So Oracle can't do a complete restore.
The solution was to store the redo-log-files (=WALS) to local attached disk,
but of course redundant on multiple disks.
But other oracle-dba use RAIDs to store the redo-logs with no problems.

> 
>  >
>  > Is threre an equivalent to Oracles "ALTER SYSTEM SWITCH LOGFILE" ?
>  >
> 
> Not currently.
> 
> Again, if you can say why we might want that, it can also be added -
> other than "they have it, so we want it too" :-)

In the online-backup-strategie described in
http://developer.postgresql.org/docs/postgres/backup-online.html#BACKUP-ARCHIVING-WAL
thre must be a special attention fir active WAL, if one want to backup it also.
With  "ALTER SYSTEM SWITCH LOGFILE" one could make it to a not active WAL.
This would make it easier to backup it, especially if one want to use
backups-programms like amanda.
If there where multiple redundant WALs, one could compare them as a consistency-check.
But this is only possible if nobody writes to the compared files.

> 
> This is a likely addition for 8.1, since we will most likely want to
> archive xlog files every N minutes, whatever the transaction rate,
> allowing people to specify a maximum period of data loss - but this
> seemed like an enhancement rather than a definite feature for 8.0
> 
Great!

Regards
   Michael Kleiser



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

* Re: Multiple WALs for PITR ?
  2004-10-07 01:56 Re: Multiple WALs for PITR ? Goulet, Dick <DGoulet@vicr.com>
@ 2004-10-07 20:58 ` Simon Riggs <simon@2ndquadrant.com>
  1 sibling, 0 replies; 9+ messages in thread

From: Simon Riggs @ 2004-10-07 20:58 UTC (permalink / raw)
  To: Goulet, Dick <DGoulet@vicr.com>; mkl@webde-ag.de; +Cc: pgsql-admin

>Goulet, Dick
> As an Oracle DBA primarily, moving more towards PostGreSQL as time
> passes, the "alter system switch logfile" command is very often used at
> the end of a hot backup to force a logfile switch and consequently
> provide a full archive log set.  The command causes the database engine
> to cease using the current online redo log and have it archived to the
> archive log destination.  This is the only way to be sure that all
> required archive logs are present in a backup to reconstitute the
> database at another location.
> 

Yep, know what it does - just wanted to check that people really used it.

I always felt I did that as a precaution rather than for good reason.

OK - 8.1 it is then,

Best Regards

Simon Riggs



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

* Re: Multiple WALs for PITR ?
@ 2004-10-07 14:32 Goulet, Dick <DGoulet@vicr.com>
  2004-10-07 14:59 ` Re: Multiple WALs for PITR ? Jay A. Kreibich <jak@uiuc.edu>
  0 siblings, 1 reply; 9+ messages in thread

From: Goulet, Dick @ 2004-10-07 14:32 UTC (permalink / raw)
  To: pgsql-admin

 Some disk array manufacturers had a problem with their writes in that
although the write the data was being stored in volatile memory instead
of on disk.  The disk buffers were volatile in that if power suddenly
died all writes in memory that had not made it to disk were lost.
Lately most array manufacturers (at least those I have experience with
namely EMC & NetApp) have internal battery backup systems in place so
that in the event of a power failure the array will accept no new data,
but has sufficient battery power available to flush memory to a disk
drive cleanly before crashing.  This makes the probability that you end
up with a corrupt database a whole lot lower.  But there are still some
out there, names not to be mentioned, who do not provide this
functionality.  Therefore buyer beware.  Also with the advent of better
disk arrays I am not currently aware of any DBA's using Oracle's redo
mirroring.  I've stopped using it in favor of EMC's disk mirrors or
Business Continuance Volumes (BCV's).  Now when Oracle writes a redo log
file I really get three copies for the cost of one.  Works just as well
on PostGreSQL WAL & database files.  But of course having the ability to
force a wal file switch would be a real bennie.  Hope it makes it into
8.1.


Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: Michael Kleiser [mailto:mkl@webde-ag.de] 
Sent: Thursday, October 07, 2004 4:38 AM
To: simon@2ndquadrant.com
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Multiple WALs for PITR ?



Goulet, Dick wrote:
> As an Oracle DBA primarily, moving more towards PostGreSQL as time 
> passes, the "alter system switch logfile" command is very often used 
> at the end of a hot backup to force a logfile switch and consequently 
> provide a full archive log set.  The command causes the database 
> engine to cease using the current online redo log and have it archived

> to the archive log destination.  This is the only way to be sure that 
> all required archive logs are present in a backup to reconstitute the 
> database at another location.
> 
> 
> Dick Goulet
> Senior Oracle DBA
> Oracle Certified 8i DBA
> -----Original Message-----
> From: simon@2ndquadrant.com [mailto:simon@2ndquadrant.com]
> Sent: Wednesday, October 06, 2004 5:36 PM
> To: mkl@webde-ag.de
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Multiple WALs for PITR ?
> 
> 
> Michael Kleiser <mkl@webde-ag.de> wrote on 06.10.2004, 16:29:08:
>  > I wan`t to use Point in Time Recovery  > I allread read  > 
> http://developer.postgresql.org/docs/postgres/backup-online.html#BACKU
>  > P-ARCHIVING-WAL
>  >
> 
> Good!
> 
>  > But I still wonder, if it is possible for PostgreSQL 8.0 to write  
> > multiple, redundant WAL-Files like Oracles with its redo-log-groups
?
> 
> Not currently.
> 
> That was something I considered, though in the end lacked both 
> implementation time and justification for.
> 
> If you can explain why you think that might be necessary, I'd be happy

> to consider it for v8.1.
> 
> RAID-10 seems a good solution for me...

I heared about some RAIDs saying "yes your data is on disk"
to the oracle-database, but it wasn't so.
So Oracle can't do a complete restore.
The solution was to store the redo-log-files (=WALS) to local attached
disk, but of course redundant on multiple disks.
But other oracle-dba use RAIDs to store the redo-logs with no problems.

> 
>  >
>  > Is threre an equivalent to Oracles "ALTER SYSTEM SWITCH LOGFILE" ?
>  >
> 
> Not currently.
> 
> Again, if you can say why we might want that, it can also be added - 
> other than "they have it, so we want it too" :-)

In the online-backup-strategie described in
http://developer.postgresql.org/docs/postgres/backup-online.html#BACKUP-
ARCHIVING-WAL
thre must be a special attention fir active WAL, if one want to backup
it also.
With  "ALTER SYSTEM SWITCH LOGFILE" one could make it to a not active
WAL.
This would make it easier to backup it, especially if one want to use
backups-programms like amanda.
If there where multiple redundant WALs, one could compare them as a
consistency-check.
But this is only possible if nobody writes to the compared files.

> 
> This is a likely addition for 8.1, since we will most likely want to 
> archive xlog files every N minutes, whatever the transaction rate, 
> allowing people to specify a maximum period of data loss - but this 
> seemed like an enhancement rather than a definite feature for 8.0
> 
Great!

Regards
   Michael Kleiser

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
      joining column's datatypes do not match



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

* Re: Multiple WALs for PITR ?
  2004-10-07 14:32 Re: Multiple WALs for PITR ? Goulet, Dick <DGoulet@vicr.com>
@ 2004-10-07 14:59 ` Jay A. Kreibich <jak@uiuc.edu>
  0 siblings, 0 replies; 9+ messages in thread

From: Jay A. Kreibich @ 2004-10-07 14:59 UTC (permalink / raw)
  To: Goulet, Dick <DGoulet@vicr.com>; +Cc: pgsql-admin

On Thu, Oct 07, 2004 at 10:32:04AM -0400, Goulet, Dick scratched on the wall:
>  Some disk array manufacturers had a problem with their writes in that
> although the write the data was being stored in volatile memory instead
> of on disk.  The disk buffers were volatile in that if power suddenly
> died all writes in memory that had not made it to disk were lost.
> Lately most array manufacturers (at least those I have experience with
> namely EMC & NetApp) have internal battery backup systems in place so
> that in the event of a power failure the array will accept no new data,
> but has sufficient battery power available to flush memory to a disk
> drive cleanly before crashing. 

  All of the RAID systems I've dealt with work a little differently.  In
  most cases the internal array battery is only there to keep the
  contents of the buffer alive-- there is not enough power to actually
  keep a disk spinning and write the data out. You'd need a real UPS
  for that.  Typically the internal batteries have enough power to
  keep the volatile memory contents active for two weeks or more.
  Once power is re-applied to the array, the buffers are
  automatically flushed by the RAID controller.

  They are also, frankly, a pain in the butt.  We've had most of our
  batteries fail within 18 months or so, and replacements are often
  extremely expensive.  For most of our applications we've just
  disabled the RAID controller write-caches (our application writes
  data at a very very slow rate; the main reason we have RAIDs is
  protection, size, and *read* speed).

   -j

-- 
                     Jay A. Kreibich | Comm. Technologies, R&D
                        jak@uiuc.edu | Campus IT & Edu. Svcs.
          <http://www.uiuc.edu/~jak; | University of Illinois at U/C



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


end of thread, other threads:[~2004-10-07 20:58 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-06 14:29 Multiple WALs for PITR ? Michael Kleiser <mkl@webde-ag.de>
2004-10-06 22:58 ` Gaetano Mendola <mendola@bigfoot.com>
2004-10-06 21:36 Re:  Multiple WALs for PITR ? simon@2ndquadrant.com
2004-10-07 15:29 ` JEDIDIAH <jedi@nomad.mishnet>
2004-10-07 01:56 Re: Multiple WALs for PITR ? Goulet, Dick <DGoulet@vicr.com>
2004-10-07 08:38 ` Michael Kleiser <mkl@webde-ag.de>
2004-10-07 20:58 ` Simon Riggs <simon@2ndquadrant.com>
2004-10-07 14:32 Re: Multiple WALs for PITR ? Goulet, Dick <DGoulet@vicr.com>
2004-10-07 14:59 ` Jay A. Kreibich <jak@uiuc.edu>

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