I'm in charge of a very large database, and we're using a highly decrepit
version of Postgresql currently. After searching through the archives, Google,
and trying out several replication engines, I have a question.
I had originally considered Slony-I, as it doesn't seem to require version
compatibility between nodes like pgCluster, so upgrading from 7.4.2 to 8.1.3
would be a possible, if slow process. But after looking into the level of
micro-management necessary, such as defining sets of every table on a
per-database level, then having it add artificial primary-keys to applicable
tables, it just doesn't seem like a good choice. Not a fault of Slony-I, but
several multi-gig databases hosting hundreds of tables would be a nightmare to
use with Slony-I.
Then I thought about the backup/recovery system and the WAL files. Would this
scenario be possible:
1. Do a pg_dumpall on the existing database running 7.4.2.
2. Do a psql -f foo template1 on the new database running 8.1.3.
3. Wait a very long time while the new database loads.
4. Shut down old database.
5. Start the new database in restore mode, and point it to the WAL
files from the old database.
6. Wait for restore to finish.
7. Restart the new database.
I wondered about this, as the pg_dumpall/restore would take a very long time
for a 50GB database cluster, but theoretically the WAL files would continue to
accumulate on the old db while this loading was taking place. If the WAL
formats were compatible, the total upgrade time would only be restricted to how
long it took to replay the WAL files in the new database. Does the current
format of the WAL files make this possible? If not, is such an option for the
future?
Thanks in advance.
--
Shaun Thomas
Database Administrator
Leapfrog Online
847-440-8253
CONFIDENTIALITY NOTE
The document(s) accompanying this e-mail transmission, if any, and the e-mail
transmittal message contain information from Leapfrog Online Customer
Acquisition, LLC is confidential or privileged. The information is intended to
be for the use of the individual(s) or entity(ies) named on this e-mail
transmission message. If you are not the intended recipient, be aware that any
disclosure, copying, distribution or use of the contents of this e-mail is
prohibited. If you have received this e-mail in error, please immediately
delete this e-mail and notify us by telephone of the error.
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
