pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Davies <tgdavies@gmail.com>
To: pgsql-admin@postgresql.org
Subject: on-line backup questions
Date: Sat, 22 Dec 2007 13:02:50 +1100
Message-ID: <0FBA939C-2ED1-4400-BD3B-5290193837D0@gmail.com> (raw)

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



view thread (3+ messages)  latest in thread

Message-ID: <0FBA939C-2ED1-4400-BD3B-5290193837D0@gmail.com>
Permalink:  ../0FBA939C-2ED1-4400-BD3B-5290193837D0@gmail.com/
Also on:    postgresql.org/message-id/0FBA939C-2ED1-4400-BD3B-5290193837D0@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: tgdavies@gmail.com
  Subject: Re: on-line backup questions
  In-Reply-To: <0FBA939C-2ED1-4400-BD3B-5290193837D0@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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