We're intending to move a 470GB PostgreSQL 8.3.13 database using the following
technique from http://www.postgresql.org/docs/8.3/interactive/backup-file.html
"Another option is to use rsync to perform a file system backup. This is done
by first running rsync while the database server is running, then shutting down
the database server just long enough to do a second rsync. The second rsync
will be much quicker than the first, because it has relatively little data to
transfer, and the end result will be consistent because the server was down.
This method allows a file system backup to be performed with minimal downtime."
Except that we plan on an initial rsync which we think might take a couple of
days, then subsequent daily rsyncs for up to a week to keep it up to date till
we stop the old database, rsync again, and start the new database.
A very rough approximation of our database would be half a dozen large tables
taking up 1/3 of the disk space, and lots of indexes on those tables taking the
other 2/3 of the space.
If we assume usage characteristics of:
Much less than 1% of indexed data changing per day, with almost all of those
updates being within the 1% of most recently added data.
Much less than 1% of historical indexed data being deleted per day with most of
the deletions expected to affect sets of contiguous file pages.
About 1% of new indexed data added per day
I'm curious of the impact of vacuum (automatic and manual) during that process
on expected amount of work rsync will have to do, and time it will take, and on
what the update pattern is on files of Btree indexes.
Is it worth making sure vacuum is not run, in order to reduce the amount of
files that change during that period?
Do a number of additions evenly spread through the domain of an indexed field's
values result in localized changes to the indexes files, or changes throughout
the files?
How about for additions to the end of the domain of an indexed field's values
(e.g. adding current dates)?
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?
Regards, Stephen Denne.
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.
__________________________________________________________________
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
