pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPlanning hot/live backups?
9+ messages / 6 participants
[nested] [flat]
* Planning hot/live backups?
@ 2008-03-24 20:23 Steve Poe <steve.poe@gmail.com>
2008-03-24 20:39 ` Re: Planning hot/live backups? Campbell, Lance <lance@uiuc.edu>
2008-03-24 20:43 ` Re: Planning hot/live backups? Matthew T. O'Connor <matthew@zeut.net>
0 siblings, 2 replies; 9+ messages in thread
From: Steve Poe @ 2008-03-24 20:23 UTC (permalink / raw)
To: pgsql-performance
The owners of the animal hospital where I work at want to consider live/hot
backups through out the day so we're less likely to lose a whole
day of transaction. We use Postgresql 8.0.15. We do 3AM
backups, using pg_dumpall, to a file when there is very little activity.
The hospital enjoys the overall performance of the veterinary
application running
on Postgresql. I know doing a mid-day backup when up to 60 computers
(consistently
35-40) are access client/patient information, it will cause some
frustration. I understand
there needs to be balance of performance and backup of current records.
While I know that not all situations are the same, I am hoping there
is a performance
latency that others have experienced when doing backups during the day and/or
planning for cluster (or other types of redundancy).
My animal hospital operates 24x7 and is in the south part of the San
Francisco Bay area. Outside
of sharing your experiences/input with me, I would not mind if you/your company
do this type of consulting offline.
Thank you.
Steve
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Planning hot/live backups?
2008-03-24 20:23 Planning hot/live backups? Steve Poe <steve.poe@gmail.com>
@ 2008-03-24 20:39 ` Campbell, Lance <lance@uiuc.edu>
1 sibling, 0 replies; 9+ messages in thread
From: Campbell, Lance @ 2008-03-24 20:39 UTC (permalink / raw)
To: Steve Poe <steve.poe@gmail.com>; pgsql-performance
I back up around 10 Gig of data every half hour using pg_dump. I don't
backup the entire database at once. Instead I backup at the schema
namespace level. But I do all of them every half hour. It takes four
minutes. That includes the time to copy the files to the backup server.
I do each schema namespace backup consecutively. I also run vacuum full
analyze once a day. My system is up 24/7 as well. I don't backup in
the middle of the night. There is so little back. But I could. I am
able to have more backups by not doing it when there are only a handful
of transactions.
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Steve Poe
Sent: Monday, March 24, 2008 3:23 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Planning hot/live backups?
The owners of the animal hospital where I work at want to consider
live/hot
backups through out the day so we're less likely to lose a whole
day of transaction. We use Postgresql 8.0.15. We do 3AM
backups, using pg_dumpall, to a file when there is very little activity.
The hospital enjoys the overall performance of the veterinary
application running
on Postgresql. I know doing a mid-day backup when up to 60 computers
(consistently
35-40) are access client/patient information, it will cause some
frustration. I understand
there needs to be balance of performance and backup of current records.
While I know that not all situations are the same, I am hoping there
is a performance
latency that others have experienced when doing backups during the day
and/or
planning for cluster (or other types of redundancy).
My animal hospital operates 24x7 and is in the south part of the San
Francisco Bay area. Outside
of sharing your experiences/input with me, I would not mind if you/your
company
do this type of consulting offline.
Thank you.
Steve
-
Sent via pgsql-performance mailing list
(pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Planning hot/live backups?
2008-03-24 20:23 Planning hot/live backups? Steve Poe <steve.poe@gmail.com>
@ 2008-03-24 20:43 ` Matthew T. O'Connor <matthew@zeut.net>
2008-03-24 21:22 ` Re: Planning hot/live backups? paul rivers <rivers.paul@gmail.com>
2008-03-24 22:23 ` Re: Planning hot/live backups? Tom Lane <tgl@sss.pgh.pa.us>
2008-03-24 22:28 ` Re: Planning hot/live backups? Matthew T. O'Connor <matthew@zeut.net>
1 sibling, 3 replies; 9+ messages in thread
From: Matthew T. O'Connor @ 2008-03-24 20:43 UTC (permalink / raw)
To: Steve Poe <steve.poe@gmail.com>; +Cc: pgsql-performance
Steve Poe wrote:
> The owners of the animal hospital where I work at want to consider live/hot
> backups through out the day so we're less likely to lose a whole
> day of transaction. We use Postgresql 8.0.15. We do 3AM
> backups, using pg_dumpall, to a file when there is very little activity.
You probably want to look into PITR, you can have a constant ongoing
backup of your data and never lose more than a few minutes of data. The
overhead isn't all the big especially if you are shipping the log files
to a separate server.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Planning hot/live backups?
2008-03-24 20:23 Planning hot/live backups? Steve Poe <steve.poe@gmail.com>
2008-03-24 20:43 ` Re: Planning hot/live backups? Matthew T. O'Connor <matthew@zeut.net>
@ 2008-03-24 21:22 ` paul rivers <rivers.paul@gmail.com>
2 siblings, 0 replies; 9+ messages in thread
From: paul rivers @ 2008-03-24 21:22 UTC (permalink / raw)
To: Matthew T. O'Connor <matthew@zeut.net>; +Cc: Steve Poe <steve.poe@gmail.com>; pgsql-performance
Matthew T. O'Connor wrote:
> Steve Poe wrote:
>> The owners of the animal hospital where I work at want to consider
>> live/hot
>> backups through out the day so we're less likely to lose a whole
>> day of transaction. We use Postgresql 8.0.15. We do 3AM
>> backups, using pg_dumpall, to a file when there is very little activity.
>
>
>
> You probably want to look into PITR, you can have a constant ongoing
> backup of your data and never lose more than a few minutes of data.
> The overhead isn't all the big especially if you are shipping the log
> files to a separate server.
>
>
I'll second that. PITR is IMHO the way to go, and I believe you'll be
pleasantly surprised how easy it is to do. As always, test your backup
strategy by restoring. Even better, make a point of periodically
testing a restore of production backups to a non-production system.
Paul
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Planning hot/live backups?
2008-03-24 20:23 Planning hot/live backups? Steve Poe <steve.poe@gmail.com>
2008-03-24 20:43 ` Re: Planning hot/live backups? Matthew T. O'Connor <matthew@zeut.net>
@ 2008-03-24 22:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-24 22:49 ` Re: Planning hot/live backups? Steve Poe <steve.poe@gmail.com>
2 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2008-03-24 22:23 UTC (permalink / raw)
To: Matthew T. O'Connor <matthew@zeut.net>; +Cc: Steve Poe <steve.poe@gmail.com>; pgsql-performance
"Matthew T. O'Connor" <matthew@zeut.net> writes:
> Steve Poe wrote:
>> The owners of the animal hospital where I work at want to consider live/hot
>> backups through out the day so we're less likely to lose a whole
>> day of transaction. We use Postgresql 8.0.15. We do 3AM
>> backups, using pg_dumpall, to a file when there is very little activity.
> You probably want to look into PITR, you can have a constant ongoing
> backup of your data and never lose more than a few minutes of data. The
> overhead isn't all the big especially if you are shipping the log files
> to a separate server.
But note that you really need to update to a newer major release before
depending on PITR. While 8.0 nominally has support for it, it's taken
us several releases to really get the operational gotchas sorted out.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Planning hot/live backups?
2008-03-24 20:23 Planning hot/live backups? Steve Poe <steve.poe@gmail.com>
2008-03-24 20:43 ` Re: Planning hot/live backups? Matthew T. O'Connor <matthew@zeut.net>
2008-03-24 22:23 ` Re: Planning hot/live backups? Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-03-24 22:49 ` Steve Poe <steve.poe@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Steve Poe @ 2008-03-24 22:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Matthew T. O'Connor <matthew@zeut.net>; pgsql-performance
Tom,
So, are you saying we need to get to at least 8.1.x before considering PITR
for a production environment? Unfortunately, the vendor/supplier of
our veterinary application
does not support higher versions. We would be proceeding "at our own risk".
Is there anything else we can do we 8.0.15 version?
Steve
On Mon, Mar 24, 2008 at 3:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Matthew T. O'Connor" <matthew@zeut.net> writes:
> > Steve Poe wrote:
> >> The owners of the animal hospital where I work at want to consider live/hot
> >> backups through out the day so we're less likely to lose a whole
> >> day of transaction. We use Postgresql 8.0.15. We do 3AM
> >> backups, using pg_dumpall, to a file when there is very little activity.
>
> > You probably want to look into PITR, you can have a constant ongoing
> > backup of your data and never lose more than a few minutes of data. The
> > overhead isn't all the big especially if you are shipping the log files
> > to a separate server.
>
> But note that you really need to update to a newer major release before
> depending on PITR. While 8.0 nominally has support for it, it's taken
> us several releases to really get the operational gotchas sorted out.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Planning hot/live backups?
2008-03-24 20:23 Planning hot/live backups? Steve Poe <steve.poe@gmail.com>
2008-03-24 20:43 ` Re: Planning hot/live backups? Matthew T. O'Connor <matthew@zeut.net>
@ 2008-03-24 22:28 ` Matthew T. O'Connor <matthew@zeut.net>
2008-03-24 23:45 ` Re: Planning hot/live backups? Miguel Arroz <arroz@guiamac.com>
2 siblings, 1 reply; 9+ messages in thread
From: Matthew T. O'Connor @ 2008-03-24 22:28 UTC (permalink / raw)
To: Steve Poe <steve.poe@gmail.com>; pgsql-performance
Steve Poe wrote:
> At this point, I am just moving the pg_dumpall file to another server. Pardon
> my question: how would you 'ship the log files'?
>
[ You should cc the mailing list so that everyone can benefit from the
conversation. ]
RTM:
http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Planning hot/live backups?
2008-03-24 20:23 Planning hot/live backups? Steve Poe <steve.poe@gmail.com>
2008-03-24 20:43 ` Re: Planning hot/live backups? Matthew T. O'Connor <matthew@zeut.net>
2008-03-24 22:28 ` Re: Planning hot/live backups? Matthew T. O'Connor <matthew@zeut.net>
@ 2008-03-24 23:45 ` Miguel Arroz <arroz@guiamac.com>
2008-03-25 04:50 ` Re: Planning hot/live backups? Matthew T. O'Connor <matthew@zeut.net>
0 siblings, 1 reply; 9+ messages in thread
From: Miguel Arroz @ 2008-03-24 23:45 UTC (permalink / raw)
To: Matthew T. O'Connor <matthew@zeut.net>; +Cc: Steve Poe <steve.poe@gmail.com>; pgsql-performance
Hi!
Going a bit off topic, but one quick question: to avoid storing GB
of WAL files that will probably take a lot of time to reload, how can
the backup be "reset"? I suspect that it's something like stopping the
WAL archiving, doing a new base backup, and restart archiving, but
I've never done it (have been using SQL dumps), so...
Yours
Miguel Arroz
On 2008/03/24, at 22:28, Matthew T. O'Connor wrote:
> Steve Poe wrote:
>> At this point, I am just moving the pg_dumpall file to another
>> server. Pardon
>> my question: how would you 'ship the log files'?
>>
>
> [ You should cc the mailing list so that everyone can benefit from
> the conversation. ]
>
> RTM: http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html
>
>
> -
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
Miguel Arroz
http://www.terminalapp.net
http://www.ipragma.com
Attachments:
smime.p7s (application/pkcs7-signature, 2.4K) download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Planning hot/live backups?
2008-03-24 20:23 Planning hot/live backups? Steve Poe <steve.poe@gmail.com>
2008-03-24 20:43 ` Re: Planning hot/live backups? Matthew T. O'Connor <matthew@zeut.net>
2008-03-24 22:28 ` Re: Planning hot/live backups? Matthew T. O'Connor <matthew@zeut.net>
2008-03-24 23:45 ` Re: Planning hot/live backups? Miguel Arroz <arroz@guiamac.com>
@ 2008-03-25 04:50 ` Matthew T. O'Connor <matthew@zeut.net>
0 siblings, 0 replies; 9+ messages in thread
From: Matthew T. O'Connor @ 2008-03-25 04:50 UTC (permalink / raw)
To: Miguel Arroz <arroz@guiamac.com>; +Cc: Steve Poe <steve.poe@gmail.com>; pgsql-performance
Miguel Arroz wrote:
> Going a bit off topic, but one quick question: to avoid storing GB of
> WAL files that will probably take a lot of time to reload, how can the
> backup be "reset"? I suspect that it's something like stopping the WAL
> archiving, doing a new base backup, and restart archiving, but I've
> never done it (have been using SQL dumps), so...
Basically, you only need WAL logs from the last time you made a back-up
of your data directory. We do nightly re-rsync of our data directory
and then purge old WAL files that are no longer needed.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2008-03-25 04:50 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-24 20:23 Planning hot/live backups? Steve Poe <steve.poe@gmail.com>
2008-03-24 20:39 ` Campbell, Lance <lance@uiuc.edu>
2008-03-24 20:43 ` Matthew T. O'Connor <matthew@zeut.net>
2008-03-24 21:22 ` paul rivers <rivers.paul@gmail.com>
2008-03-24 22:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-24 22:49 ` Steve Poe <steve.poe@gmail.com>
2008-03-24 22:28 ` Matthew T. O'Connor <matthew@zeut.net>
2008-03-24 23:45 ` Miguel Arroz <arroz@guiamac.com>
2008-03-25 04:50 ` Matthew T. O'Connor <matthew@zeut.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