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

* WAL and pg_dump
@ 2005-12-12 22:18  Mike C <smith.not.western@gmail.com>
  0 siblings, 1 reply; 12+ messages in thread

From: Mike C @ 2005-12-12 22:18 UTC (permalink / raw)
  To: pgsql-admin

All the documentation I've seen for PITR points to having to do a file
system copy of sorts.

Is it possible to have a weekly pg_dump and restore the database up to the
last archived WAL file if the db server fails? If so, how? Is it just a
matter of restoring the database then copying the archived WAL files into
the xlog dir and creating a recovery.conf file?

Cheers,

Mike C.

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

* Re: WAL and pg_dump
@ 2005-12-21 23:33  Simon Riggs <simon@2ndquadrant.com>
  parent: Mike C <smith.not.western@gmail.com>
  0 siblings, 1 reply; 12+ messages in thread

From: Simon Riggs @ 2005-12-21 23:33 UTC (permalink / raw)
  To: Mike C <smith.not.western@gmail.com>; +Cc: pgsql-admin

On Tue, 2005-12-13 at 11:18 +1300, Mike C wrote:
> All the documentation I've seen for PITR points to having to do a file
> system copy of sorts. 

Yes. There's no other way, but why would you want another way?

> Is it possible to have a weekly pg_dump and restore the database up to
> the last archived WAL file if the db server fails? 

No.

pg_dump only copies the data, not the current state of the database.

> If so, how? Is it just a matter of restoring the database then copying
> the archived WAL files into the xlog dir and creating a recovery.conf
> file?

Best Regards, Simon Riggs




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

* Re: WAL and pg_dump
@ 2005-12-22 21:09  Mike C <smith.not.western@gmail.com>
  parent: Simon Riggs <simon@2ndquadrant.com>
  0 siblings, 2 replies; 12+ messages in thread

From: Mike C @ 2005-12-22 21:09 UTC (permalink / raw)
  To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-admin

On 12/22/05, Simon Riggs <simon@2ndquadrant.com> wrote:
>
> On Tue, 2005-12-13 at 11:18 +1300, Mike C wrote:
> > All the documentation I've seen for PITR points to having to do a file
> > system copy of sorts.
>
> Yes. There's no other way, but why would you want another way?


Mainly because a pg_dump backup takes up a lot less disk space than a
filesystem copy (in my case, approx 3.5GB backup from a 25GB db).

> Is it possible to have a weekly pg_dump and restore the database up to
> > the last archived WAL file if the db server fails?
>
> No.
>
> pg_dump only copies the data, not the current state of the database.


I guess if pg_dump was modified to record the last complete transaction id
(in archive format maybe) then the WAL files could be used?

Thanks for the response,

Mike C.

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

* Re: WAL and pg_dump
@ 2005-12-22 22:17  Simon Riggs <simon@2ndquadrant.com>
  parent: Mike C <smith.not.western@gmail.com>
  1 sibling, 1 reply; 12+ messages in thread

From: Simon Riggs @ 2005-12-22 22:17 UTC (permalink / raw)
  To: Mike C <smith.not.western@gmail.com>; +Cc: pgsql-admin

On Fri, 2005-12-23 at 10:09 +1300, Mike C wrote:
> On 12/22/05, Simon Riggs <simon@2ndquadrant.com> wrote:
>         On Tue, 2005-12-13 at 11:18 +1300, Mike C wrote:
>         > All the documentation I've seen for PITR points to having to
>         do a file
>         > system copy of sorts.
>         
>         Yes. There's no other way, but why would you want another way?
> 
> Mainly because a pg_dump backup takes up a lot less disk space than a
> filesystem copy (in my case, approx 3.5GB backup from a 25GB db). 

What is your backup time in each case, and what is your recovery time?

Is space your limiting factor? 

Best Regards, Simon Riggs





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

* Re: WAL and pg_dump
@ 2005-12-22 23:32  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Mike C <smith.not.western@gmail.com>
  1 sibling, 0 replies; 12+ messages in thread

From: Tom Lane @ 2005-12-22 23:32 UTC (permalink / raw)
  To: Mike C <smith.not.western@gmail.com>; +Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-admin

Mike C <smith.not.western@gmail.com> writes:
> I guess if pg_dump was modified to record the last complete transaction id
> (in archive format maybe) then the WAL files could be used?

No.  pg_dump output and WAL files are at two utterly different levels of
abstraction --- the WAL files describe the exact placement of tuples
within tables and indexes, to mention just one example.  There's no hope
of using pg_dump output to reproduce the state of a database exactly
enough that you could apply WAL files to it.

			regards, tom lane



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

* Re: WAL and pg_dump
@ 2005-12-23 01:34  Mike C <smith.not.western@gmail.com>
  parent: Simon Riggs <simon@2ndquadrant.com>
  0 siblings, 2 replies; 12+ messages in thread

From: Mike C @ 2005-12-23 01:34 UTC (permalink / raw)
  To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-admin

On 12/23/05, Simon Riggs <simon@2ndquadrant.com> wrote:
>
> On Fri, 2005-12-23 at 10:09 +1300, Mike C wrote:
> > On 12/22/05, Simon Riggs <simon@2ndquadrant.com> wrote:
> >         On Tue, 2005-12-13 at 11:18 +1300, Mike C wrote:
> >         > All the documentation I've seen for PITR points to having to
> >         do a file
> >         > system copy of sorts.
> >
> >         Yes. There's no other way, but why would you want another way?
> >
> > Mainly because a pg_dump backup takes up a lot less disk space than a
> > filesystem copy (in my case, approx 3.5GB backup from a 25GB db).
>
> What is your backup time in each case, and what is your recovery time?
>
> Is space your limiting factor?


Yes, space is our limiting factor. A pg_dump archive format backup takes
about 40mins for 25GB. A restore IIRC takes about 2 hours. However the size
of the database is expected to grow to about 120 GB within a few months and
by the end of 2006 over 400 GB. The current capacity of our DS 400 SAN is
600 GB. at 400GB my only backup option with the existing hardware is
pg_dump. However I suspect now that I really should be looking at additional
hardware.

Cheers,

Mike

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

* Re: WAL and pg_dump
@ 2005-12-23 01:50  Stephen Frost <sfrost@snowman.net>
  parent: Mike C <smith.not.western@gmail.com>
  1 sibling, 1 reply; 12+ messages in thread

From: Stephen Frost @ 2005-12-23 01:50 UTC (permalink / raw)
  To: Mike C <smith.not.western@gmail.com>; +Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-admin

* Mike C (smith.not.western@gmail.com) wrote:
> Yes, space is our limiting factor. A pg_dump archive format backup takes
> about 40mins for 25GB. A restore IIRC takes about 2 hours. However the size
> of the database is expected to grow to about 120 GB within a few months and
> by the end of 2006 over 400 GB. The current capacity of our DS 400 SAN is
> 600 GB. at 400GB my only backup option with the existing hardware is
> pg_dump. However I suspect now that I really should be looking at additional
> hardware.

Yeah, that pg_dump restore time is pretty rough.

As I recall, the initial backup of our 360GB (or so) database took 
about 6 hours and the restore only took about 2 hours.  One nice thing
(well, in a way I guess) was that the whole thing compressed down to
under 100GB.  This was on an IBM DS4600 SAN (iirc, something along those 
lines) with 5 320GB FC disks in a RAID-5.

I'm not really looking forward to the day I get to pg_dump and
pg_restore the whole thing to move to 8.1. :)

	Thanks,

		Stephen

Attachments:

  signature.asc (application/pgp-signature, 188B) download

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

* Re: WAL and pg_dump
@ 2005-12-23 04:40  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Stephen Frost <sfrost@snowman.net>
  0 siblings, 1 reply; 12+ messages in thread

From: Tom Lane @ 2005-12-23 04:40 UTC (permalink / raw)
  To: Stephen Frost <sfrost@snowman.net>; +Cc: Mike C <smith.not.western@gmail.com>; Simon Riggs <simon@2ndquadrant.com>; pgsql-admin

Stephen Frost <sfrost@snowman.net> writes:
> As I recall, the initial backup of our 360GB (or so) database took
> about 6 hours and the restore only took about 2 hours.

Really?  I'd certainly have guessed the opposite (mainly because of
index build time, constraint checking, etc during reload).  Could it
be that compression of the pg_dump output is swamping all else during
the backup phase?

			regards, tom lane



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

* Re: WAL and pg_dump
@ 2005-12-23 09:48  Simon Riggs <simon@2ndquadrant.com>
  parent: Mike C <smith.not.western@gmail.com>
  1 sibling, 0 replies; 12+ messages in thread

From: Simon Riggs @ 2005-12-23 09:48 UTC (permalink / raw)
  To: Stephen Frost <sfrost@snowman.net>; Mike C <smith.not.western@gmail.com>; +Cc: pgsql-admin

On Fri, 2005-12-23 at 14:34 +1300, Mike C wrote:

>         Is space your limiting factor?
> 
> Yes, space is our limiting factor. A pg_dump archive format backup
> takes about 40mins for 25GB. A restore IIRC takes about 2 hours.
> However the size of the database is expected to grow to about 120 GB
> within a few months and by the end of 2006 over 400 GB. The current
> capacity of our DS 400 SAN is 600 GB. at 400GB my only backup option
> with the existing hardware is pg_dump. However I suspect now that I
> really should be looking at additional hardware.

You should be looking at backup designs that do not require you to dump
the complete database each time. That way your backup and recovery times
increase linearly.

Best Regards, Simon Riggs




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

* Re: WAL and pg_dump
@ 2005-12-23 13:43  Stephen Frost <sfrost@snowman.net>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 12+ messages in thread

From: Stephen Frost @ 2005-12-23 13:43 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Mike C <smith.not.western@gmail.com>; Simon Riggs <simon@2ndquadrant.com>; pgsql-admin

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > As I recall, the initial backup of our 360GB (or so) database took
> > about 6 hours and the restore only took about 2 hours.
> 
> Really?  I'd certainly have guessed the opposite (mainly because of
> index build time, constraint checking, etc during reload).  Could it
> be that compression of the pg_dump output is swamping all else during
> the backup phase?

Sorry, I thought I was being clear (guess not)- I wasn't talking about
using pg_dump but rather PITR and tar/untar.  I was trying to point out
that using PITR and tar/untar can be much, much, much nicer when you
have lots and lots of data to deal with (like a data warehouse would
have...).  Of course, I can also do snapshots with the SAN, but that's a
one-time thing unlike PITR where you can choose any point in time to
recover to.

	Thanks,

		Stephen

Attachments:

  signature.asc (application/pgp-signature, 188B) download

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

* Re: WAL and pg_dump
@ 2005-12-23 16:22  Simon Riggs <simon@2ndquadrant.com>
  parent: Stephen Frost <sfrost@snowman.net>
  0 siblings, 1 reply; 12+ messages in thread

From: Simon Riggs @ 2005-12-23 16:22 UTC (permalink / raw)
  To: Stephen Frost <sfrost@snowman.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Mike C <smith.not.western@gmail.com>; pgsql-admin

On Fri, 2005-12-23 at 08:43 -0500, Stephen Frost wrote:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> > Stephen Frost <sfrost@snowman.net> writes:
> > > As I recall, the initial backup of our 360GB (or so) database took
> > > about 6 hours and the restore only took about 2 hours.
> > 
> > Really?  I'd certainly have guessed the opposite (mainly because of
> > index build time, constraint checking, etc during reload).  Could it
> > be that compression of the pg_dump output is swamping all else during
> > the backup phase?
> 
> Sorry, I thought I was being clear (guess not)- I wasn't talking about
> using pg_dump but rather PITR and tar/untar.  I was trying to point out
> that using PITR and tar/untar can be much, much, much nicer when you
> have lots and lots of data to deal with (like a data warehouse would
> have...).  

Yes, I don't think many people realise the performance you can get by
doing hot physical backup/restores.

I personally do not advocate any particular system architecture without
knowing the specific case. My work has been to provide the system
designer with more options should circumstances allow. Many systems
don't ever look sufficiently like just one of the classic design
patterns for us to use those designs directly, but we still need those
patterns to allow discussion.

> Of course, I can also do snapshots with the SAN, but that's a
> one-time thing unlike PITR where you can choose any point in time to
> recover to.

Why not use SAN Snapshots *and* PITR? They play nicely.

Best Regards, Simon Riggs





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

* Re: WAL and pg_dump
@ 2005-12-23 17:54  Stephen Frost <sfrost@snowman.net>
  parent: Simon Riggs <simon@2ndquadrant.com>
  0 siblings, 0 replies; 12+ messages in thread

From: Stephen Frost @ 2005-12-23 17:54 UTC (permalink / raw)
  To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Mike C <smith.not.western@gmail.com>; pgsql-admin

* Simon Riggs (simon@2ndquadrant.com) wrote:
> On Fri, 2005-12-23 at 08:43 -0500, Stephen Frost wrote:
> > Of course, I can also do snapshots with the SAN, but that's a
> > one-time thing unlike PITR where you can choose any point in time to
> > recover to.
> 
> Why not use SAN Snapshots *and* PITR? They play nicely.

Yeah, that's something we're working towards actually.  I havn't played
with the command-line client quite enough yet to be comfortable sticking
it in a script to do the snapshotting.  We also don't have everything on
the SAN (some things can't be due to different security requirements and
some paranoia) so we're making sure that what we're doing will work for
those systems as well.  Once I'm happier with the SAN command-line
client I might substitute that in for the tar generation we're doing now
for those databases on the SAN.

	Thanks,

		Stephen

Attachments:

  signature.asc (application/pgp-signature, 188B) download

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


end of thread, other threads:[~2005-12-23 17:54 UTC | newest]

Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-12 22:18 WAL and pg_dump Mike C <smith.not.western@gmail.com>
2005-12-21 23:33 ` Simon Riggs <simon@2ndquadrant.com>
2005-12-22 21:09   ` Mike C <smith.not.western@gmail.com>
2005-12-22 22:17     ` Simon Riggs <simon@2ndquadrant.com>
2005-12-23 01:34       ` Mike C <smith.not.western@gmail.com>
2005-12-23 01:50         ` Stephen Frost <sfrost@snowman.net>
2005-12-23 04:40           ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-23 13:43             ` Stephen Frost <sfrost@snowman.net>
2005-12-23 16:22               ` Simon Riggs <simon@2ndquadrant.com>
2005-12-23 17:54                 ` Stephen Frost <sfrost@snowman.net>
2005-12-23 09:48         ` Simon Riggs <simon@2ndquadrant.com>
2005-12-22 23:32     ` Tom Lane <tgl@sss.pgh.pa.us>

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