pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
on-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