pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedon-line backup questions
3+ messages / 2 participants
[nested] [flat]
* on-line backup questions
@ 2007-12-22 02:02 Tom Davies <tgdavies@gmail.com>
2007-12-22 02:33 ` Re: on-line backup questions Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Davies @ 2007-12-22 02:02 UTC (permalink / raw)
To: pgsql-admin
I've been working on backup restore scripts for a PostgreSQL DB hosted
on an Amazon EC2 instance. EC2 instances don't have any persistent
storage, so I archive the WAL files to Amazon S3. I do periodic
backups, and restore and roll forward when an instance is restarted.
I've followed the excellent documentation at http://www.postgresql.org/docs/8.0/interactive/backup-online.html
, and everything is working well, but I have a few further questions.
1. Purely out of curiousity, what's the nnnnnnnn.history file which is
requested from the archive when you restore? I realise that the
restore process looking for files which don't exist is not a problem,
I'm just being inquisitive.
2. When I do a backup, Postgres requests archiving of n.m.backup and
the WAL file n immediately, so I only copy WAL files with numbers > n
from pg_xlog. I've seen suggestions that I should be copying WAL files
with numbers >= n -- is there any reason to do so?
3. What's the best thing to do when I deliberately shut down
PostgreSQL (i.e. pg_ctl stop)? When I start again I will be restoring
from the most recent backup and rolling forward over the archived WAL
files. I believe that shutdown leaves me with unarchived WAL files in
pg_xlog. Can I simply copy these to S3 as if they were WAL files which
were copied from pg_xlog at the time of the last backup, and then put
them back into pg_xlog as part of my restore process?
4. I'm using PostgreSQL 8.0 -- are there any significant improvements
in on-line backups in later versions?
Thanks,
Tom
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: on-line backup questions
2007-12-22 02:02 on-line backup questions Tom Davies <tgdavies@gmail.com>
@ 2007-12-22 02:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-12-22 05:20 ` Re: on-line backup questions Tom Davies <tgdavies@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2007-12-22 02:33 UTC (permalink / raw)
To: Tom Davies <tgdavies@gmail.com>; +Cc: pgsql-admin
Tom Davies <tgdavies@gmail.com> writes:
> 1. Purely out of curiousity, what's the nnnnnnnn.history file which is
> requested from the archive when you restore?
Read the doc section about timelines --- if a history file exists,
it's needed to allow proper tracing of the "timeline" through multiple
recovery attempts.
> 3. What's the best thing to do when I deliberately shut down
> PostgreSQL (i.e. pg_ctl stop)? When I start again I will be restoring
> from the most recent backup and rolling forward over the archived WAL
> files. I believe that shutdown leaves me with unarchived WAL files in
> pg_xlog.
Yeah, you should archive the latest WAL file, but in 8.0 you'd have to
do that manually. (IIRC there isn't even a forced-xlog-switch function
in that version to help you.)
> 4. I'm using PostgreSQL 8.0 -- are there any significant improvements
> in on-line backups in later versions?
Get thyself onto 8.2 ASAP, or maybe go to 8.3 shortly after the
holidays. 8.0 is basically our stone age for PITR support; while
the concepts haven't changed since then, we've filed off a whole lot
of rough edges in operational details. In a situation where you're
depending on archive recovery as much as this, you *need* those fixes.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: on-line backup questions
2007-12-22 02:02 on-line backup questions Tom Davies <tgdavies@gmail.com>
2007-12-22 02:33 ` Re: on-line backup questions Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-12-22 05:20 ` Tom Davies <tgdavies@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Davies @ 2007-12-22 05:20 UTC (permalink / raw)
To: pgsql-admin
Thanks for the prompt response!
On 22/12/2007, at 1:33 PM, Tom Lane wrote:
> Tom Davies <tgdavies@gmail.com> writes:
>>
>> 3. What's the best thing to do when I deliberately shut down
>> PostgreSQL (i.e. pg_ctl stop)? When I start again I will be restoring
>> from the most recent backup and rolling forward over the archived WAL
>> files. I believe that shutdown leaves me with unarchived WAL files in
>> pg_xlog.
>
> Yeah, you should archive the latest WAL file, but in 8.0 you'd have to
> do that manually. (IIRC there isn't even a forced-xlog-switch
> function
> in that version to help you.)
So in 8.2, when I do a backup I *don't* need to manually copy any WAL
files?, and when I shut down I should:
1 call pg_switch_xlog()
2 wait for the WAL to be archived *if* pg_switch_xlog return a
location after the end of the previously archived WAL
3 actually call pg_ctl stop
Do I need to do 2, above, or can postgres wait until the previous WAL
archive is complete?
Thanks,
Tom
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-12-22 05:20 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-22 02:02 on-line backup questions Tom Davies <tgdavies@gmail.com>
2007-12-22 02:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-12-22 05:20 ` Tom Davies <tgdavies@gmail.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