Re: Server move using rsync

Thanks for sharing your experience and thoughts Venkat,

Venkat Balaji said:

> We are performing backups to our production server exactly the same way. We have been through some problems while restoring and bringing up the database. If you are planning to take initial complete rsync with subsequent "incremental rsyncs", then you need to make sure that you have all the WAL archives starting from the initial rsync on Day 1. 
>
> Also are you doing the following?
>
> 1. pg_start_backup() - rsync - pg_stop_backup() ?
> 2. Please let us know your WAL Archive backup strategy.

We're not doing this long-term, in order to have a backup server we can fail-over to, but rather as a one-off low impact move of our database. Consequently, instead of using pg_start_backup and pg_stop_backup, and keeping all WAL, we're stopping the database, rsync of everything, and starting the database in the new server, with it appearing to the new server (if it was capable of noticing such things) that it had simply been shutdown and restarted.

The initial and repeated rsyncs while the first server is running and in use, are solely in order to reduce the time that the rsync takes while the postgresql application is stopped.

Do you still think we need to do anything special with pg_start_backup, pg_stop_backup, and WAL archives?

>> Is there any way during that week, that we can verify whether our partially completed database move process is going to result in a >> database that starts up ok?
>
> In general, yes, database can start up normally. Without WAL Archives, recovering to a particular time would not be possible.

Without doing pg_start_backup, and with rsync not performing a "snapshot" backup, my assumption is that until we do an rsync with the service shutdown, whatever we've got at the location we're copying to, is not self-consistent.

If we start up postgresql on it, won't it think it is recovering from a sudden crash? I think it may either appear to recover ok, or complain about various things, and not start up ok, with neither option providing us with much insight, as all that could tell us is that either some disk blocks are consistent, or some are not, which is our starting assumption anyway.

Starting up postgresql would probably result in more disk block changes that will result in more work next time we rsync.

I'm wondering whether it's worth doing anyway, simply to check that it doesn't do something completely unexpected, which would presumably alert us to something we hadn't considered.

How badly can we screw things up, given we intend to perform a final rsync with no postgresql services running? What should we try and avoid doing, and why?

We might simply compare some hashes between the two systems, of some files that haven't had their last-modified dates changed since the last rsync.

Regards,
Stephen.
This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by replying immediately, destroy it and do not copy, disclose or use it in any way.

Please consider the environment before printing this e-mail
__________________________________________________________________
This email has been scanned by the DMZGlobal Business Quality
Electronic Messaging Suite.
Please see http://www.dmzglobal.com/dmzmessaging.htm for details.
__________________________________________________________________