pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Remote On-line Backup
4+ messages / 3 participants
[nested] [flat]

* Remote On-line Backup
@ 2006-03-28 20:31  Thomas F. O'Connell <tfo@sitening.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Thomas F. O'Connell @ 2006-03-28 20:31 UTC (permalink / raw)
  To: pgsql-admin

I just want to make sure that I've got a good enough understanding of  
the built-in on-line backup facility to be able to minimize data loss  
and unavailability of the database during a remote recovery from on- 
line backup.

Here are the steps I'm proposing:

1. Set up archive_command in postgresql.conf on oldhost to archive to  
remote repository on newhost.
2. Perform base backup on oldhost. (I'll probably just use rsync to  
backup directly to newhost.)
3. On newhost, remove postmaster.pid from $PGDATA, disable  
archive_command in postgresql.conf, and create clean pg_xlog tree.
4. Stop the postmaster on oldhost.
5. If the WAL file referenced by the backup file in my archive  
directory on newhost is not archived when the postmaster is stopped,  
copy it from oldhost to pg_xlog on newhost.
6. Create recovery.conf on newhost.
7. Start the postmaster on newhost.
8. Rejoice when recovery.done appears.

The part I most want to make sure I understand well enough is step 5,  
which I'm understanding to be a modification of steps 2 and 6 from  
section 23.3.3 in the docs. As I understand it, there's a pretty good  
possibility that the WAL file referenced by stop_backup() will not be  
archived by the time I stop the postmaster on oldhost. In which case,  
I should be in good shape to recover if I have a base backup, the  
archived WAL files up to that final file referenced by stop_backup(),  
and the partial segment file referenced by stop_backup(), which  
should be the only unarchived WAL segment file and just needs to  
exist in pg_xlog on newhost for things to run smoothly.

Does this seem right? Or will I rather want to copy all the contents  
of pg_xlog from oldhost as they represent current (as of stopping the  
postmaster) unarchived WAL activity?

--
Thomas F. O'Connell
Database Architecture and Programming
Co-Founder
Sitening, LLC

http://www.sitening.com/
3004 B Poston Avenue
Nashville, TN 37203-1314
615-260-0005 (cell)
615-469-5150 (office)
615-469-5151 (fax)




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

* Re: Remote On-line Backup
@ 2006-03-29 19:43  Simon Riggs <simon@2ndquadrant.com>
  parent: Thomas F. O'Connell <tfo@sitening.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Simon Riggs @ 2006-03-29 19:43 UTC (permalink / raw)
  To: Thomas F. O'Connell <tfo@sitening.com>; +Cc: pgsql-admin

On Tue, 2006-03-28 at 14:31 -0600, Thomas F. O'Connell wrote:

> Here are the steps I'm proposing:
> 
> 1. Set up archive_command in postgresql.conf on oldhost to archive to  
> remote repository on newhost.
> 2. Perform base backup on oldhost. (I'll probably just use rsync to  
> backup directly to newhost.)
> 3. On newhost, remove postmaster.pid from $PGDATA, disable  
> archive_command in postgresql.conf, and create clean pg_xlog tree.
> 4. Stop the postmaster on oldhost.
> 5. If the WAL file referenced by the backup file in my archive  
> directory on newhost is not archived when the postmaster is stopped,  
> copy it from oldhost to pg_xlog on newhost.
> 6. Create recovery.conf on newhost.
> 7. Start the postmaster on newhost.
> 8. Rejoice when recovery.done appears.
> 
> The part I most want to make sure I understand well enough is step 5,  
> which I'm understanding to be a modification of steps 2 and 6 from  
> section 23.3.3 in the docs. As I understand it, there's a pretty good  
> possibility that the WAL file referenced by stop_backup() will not be  
> archived by the time I stop the postmaster on oldhost. In which case,  
> I should be in good shape to recover if I have a base backup, the  
> archived WAL files up to that final file referenced by stop_backup(),  
> and the partial segment file referenced by stop_backup(), which  
> should be the only unarchived WAL segment file and just needs to  
> exist in pg_xlog on newhost for things to run smoothly.
> 
> Does this seem right? Or will I rather want to copy all the contents  
> of pg_xlog from oldhost as they represent current (as of stopping the  
> postmaster) unarchived WAL activity?

The steps above show a one-time migration. If that is what you want then
I suggest that the steps are:

1. Shutdown oldhost cleanly.
2. Copy all data directory and all files to newhost.
3. Edit any configuration file changes required
4. Startup on newhost
5. Delete files on oldhost so it is not started up again.

This will be slower, but has less risk if you are unsure of the process.

I'd suggest you follow your own procedure on a test box without step
(4), so you can check you've done it right before you stop oldhost for
good. Once you are happy with that, go for it in live.

Best Regards, Simon Riggs




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

* Re: Remote On-line Backup
@ 2006-03-29 21:17  Thomas F. O'Connell <tfo@sitening.com>
  parent: Simon Riggs <simon@2ndquadrant.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Thomas F. O'Connell @ 2006-03-29 21:17 UTC (permalink / raw)
  To: pgsql-admin


On Mar 29, 2006, at 1:43 PM, Simon Riggs wrote:

> On Tue, 2006-03-28 at 14:31 -0600, Thomas F. O'Connell wrote:
>
>> Here are the steps I'm proposing:
>>
>> 1. Set up archive_command in postgresql.conf on oldhost to archive to
>> remote repository on newhost.
>> 2. Perform base backup on oldhost. (I'll probably just use rsync to
>> backup directly to newhost.)
>> 3. On newhost, remove postmaster.pid from $PGDATA, disable
>> archive_command in postgresql.conf, and create clean pg_xlog tree.
>> 4. Stop the postmaster on oldhost.
>> 5. If the WAL file referenced by the backup file in my archive
>> directory on newhost is not archived when the postmaster is stopped,
>> copy it from oldhost to pg_xlog on newhost.
>> 6. Create recovery.conf on newhost.
>> 7. Start the postmaster on newhost.
>> 8. Rejoice when recovery.done appears.
>>
>> The part I most want to make sure I understand well enough is step 5,
>> which I'm understanding to be a modification of steps 2 and 6 from
>> section 23.3.3 in the docs. As I understand it, there's a pretty good
>> possibility that the WAL file referenced by stop_backup() will not be
>> archived by the time I stop the postmaster on oldhost. In which case,
>> I should be in good shape to recover if I have a base backup, the
>> archived WAL files up to that final file referenced by stop_backup(),
>> and the partial segment file referenced by stop_backup(), which
>> should be the only unarchived WAL segment file and just needs to
>> exist in pg_xlog on newhost for things to run smoothly.
>>
>> Does this seem right? Or will I rather want to copy all the contents
>> of pg_xlog from oldhost as they represent current (as of stopping the
>> postmaster) unarchived WAL activity?
>
> The steps above show a one-time migration. If that is what you want  
> then
> I suggest that the steps are:
>
> 1. Shutdown oldhost cleanly.
> 2. Copy all data directory and all files to newhost.
> 3. Edit any configuration file changes required
> 4. Startup on newhost
> 5. Delete files on oldhost so it is not started up again.
>
> This will be slower, but has less risk if you are unsure of the  
> process.
>
> I'd suggest you follow your own procedure on a test box without step
> (4), so you can check you've done it right before you stop oldhost for
> good. Once you are happy with that, go for it in live.
>
> Best Regards, Simon Riggs

For now, all I need is the one-time migration. The reason I was  
hoping to include the additional steps that you roll into step 2 is  
to minimize the downtime. I could leave the postmaster on oldhost  
running while I perform the base backup.

I'm looking to avoid being unsure of the process, which is why I  
posted. :)

Is there any potential for data loss in the original scenario I  
proposed?

--
Thomas F. O'Connell
Database Architecture and Programming
Co-Founder
Sitening, LLC

http://www.sitening.com/
3004 B Poston Avenue
Nashville, TN 37203-1314
615-260-0005 (cell)
615-469-5150 (office)
615-469-5151 (fax)





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

* Re: Remote On-line Backup
@ 2006-03-30 04:14  Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
  parent: Thomas F. O'Connell <tfo@sitening.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Rajesh Kumar Mallah @ 2006-03-30 04:14 UTC (permalink / raw)
  To: Thomas F. O'Connell <tfo@sitening.com>; +Cc: pgsql-admin

On 3/29/06, Thomas F. O'Connell <tfo@sitening.com> wrote:
> I just want to make sure that I've got a good enough understanding of
> the built-in on-line backup facility to be able to minimize data loss
> and unavailability of the database during a remote recovery from on-
> line backup.
>
> Here are the steps I'm proposing:
>
> 1. Set up archive_command in postgresql.conf on oldhost to archive to
> remote repository on newhost.
> 2. Perform base backup on oldhost. (I'll probably just use rsync to
> backup directly to newhost.)
> 3. On newhost, remove postmaster.pid from $PGDATA, disable
> archive_command in postgresql.conf, and create clean pg_xlog tree.
> 4. Stop the postmaster on oldhost.
> 5. If the WAL file referenced by the backup file in my archive
> directory on newhost is not archived when the postmaster is stopped,
> copy it from oldhost to pg_xlog on newhost.

even if it(STOP WAL) was archived in the new machine ,
you should also copy the last partially filled WAL log from pg_xlog
that was created just after the file refrenced in backup file
was archived.

I think the overall process is fine , you may consider
executing a test run skipping 4

Regds
Rajesh Kumar Mallah.




> 6. Create recovery.conf on newhost.
> 7. Start the postmaster on newhost.
> 8. Rejoice when recovery.done appears.
>
> The part I most want to make sure I understand well enough is step 5,
> which I'm understanding to be a modification of steps 2 and 6 from
> section 23.3.3 in the docs. As I understand it, there's a pretty good
> possibility that the WAL file referenced by stop_backup() will not be
> archived by the time I stop the postmaster on oldhost. In which case,
> I should be in good shape to recover if I have a base backup, the
> archived WAL files up to that final file referenced by stop_backup(),
> and the partial segment file referenced by stop_backup(), which
> should be the only unarchived WAL segment file and just needs to
> exist in pg_xlog on newhost for things to run smoothly.
>
> Does this seem right? Or will I rather want to copy all the contents
> of pg_xlog from oldhost as they represent current (as of stopping the
> postmaster) unarchived WAL activity?
>
> --
> Thomas F. O'Connell
> Database Architecture and Programming
> Co-Founder
> Sitening, LLC
>
> http://www.sitening.com/
> 3004 B Poston Avenue
> Nashville, TN 37203-1314
> 615-260-0005 (cell)
> 615-469-5150 (office)
> 615-469-5151 (fax)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>



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


end of thread, other threads:[~2006-03-30 04:14 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-28 20:31 Remote On-line Backup Thomas F. O'Connell <tfo@sitening.com>
2006-03-29 19:43 ` Simon Riggs <simon@2ndquadrant.com>
2006-03-29 21:17   ` Thomas F. O'Connell <tfo@sitening.com>
2006-03-30 04:14 ` Rajesh Kumar Mallah <mallah.rajesh@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