Hello dear all, [Please CC your replies to me as I am on the digest mode]
Here's finally a very high-level design proposal of the pg_upgrade feature I was handwaiving a couple of weeks ago. Since, I am almost done with the moving, I can allocate some time for this for 8.1/8.2. If this topic is of interest to you, please read on until the very end before flaming or bashing the ideas out. I had designed that thing and kept updating (the design) more or less regularly, and also reflected some issues from the nearby threads [1] and [2]. This design is very high-level at the moment and is not very detailed. I will need to figure out more stuff as I go and design some aspects in finer detail. I started to poke around asking for initdb-forcing code paths in [3], but got no response so far. But I guess if the general idea or, rather, ideas accepted I will insist on more information more aggressively :) if I can't figure something out for myself. [1] http://archives.postgresql.org/pgsql-hackers/2004-09/msg00000.php [2] http://archives.postgresql.org/pgsql-hackers/2004-09/msg00382.php [3] http://archives.postgresql.org/pgsql-hackers/2004-08/msg01594.php Comments are very welcome, especially _*CONSTRUCTIVE*_... Thank you, and now sit back and read... CONTENTS: ========= 1. The Need 1. Utilities and User's View of the pg_upgrade Feature 2. Storage Management - Storage Managers and the smgr API 3. Source Code Maintenance Aspects 2. The Upgrade Sequence 4. Proposed Implementation Plan - initdb() API - upgrade API 1. The Need ----------- It's been a problem for PG for quite awhile now to have a less painful upgrade procedure with every new revision of PostgreSQL, so the dump/restore sequence is required. That can take a while for a production DB, while keeping it offline. The new replication-related solutions, such as Slony I, pg_pool, and others can remedy the problem somewhat, but require to roughly double the storage requirements of a given database while replicating from the older server to a newer one. The proposed implementation of an in-server pg_upgrade facility attempts to address both issues at the same time -- a possibility to keep the server running and upgrading lazily w/o doubling the storage requirements (there will be some extra disk space taken, but far from doubling the size). The in-process upgrade will not take much of down time and won't require that much memory/disk/network resources as replication solutions do. Prerequisites ------------- Ideally, the (maybe not so anymore) ambitious goal is to simply be able to "drop in" the new binaries of the new server and kick off on the older version of data files. I think is this feasible now a lot more than before since we have those things available, which should ease up the implementation: - bgwriter - pg_autovacuum (the one to be integrated into the backend in 8.1) - smgr API for pluggable storage managers - initdb in C - ... initdb in C, bgwriter and pg_autovacuum, and pluggable storage manager have made the possibility of creation of the Upgrade Subsystem for PostgreSQL to be something more reasonable, complete, feasible, and sane to a point. Utilities and the User's (DBA) View of the Feature -------------------------------------------------- Two instances exist: pg_upgrade (in C) A standalone utility to upgrade the binary on-disk format from one version to another when the database is offline. We should always have this as an option. pg_upgrade will accept sub/super set of pg_dump(all)/pg_restore options that do not require a connection. I haven't thought through this in detail yet. pg_autoupgrade a postgres subprocess, modeled after bgwriter and pg_autovacuum daemons. This will work when the database system is running on old data directory, and lazily converting relations to the new format. pg_autoupgrade daemon can be triggered by the following events in addition to the lazy upgrade process: "SQL" level: UPGRADE <ALL | relation_name [, relation_name]> [NOW | time] While the database won't be offline running over older database files, SELECT/read-only queries would be allowed using older storage managers*. Any write operation on old data will act using write-invalidate approach that will force the upgrade the affected relations to the new format to be scheduled after the relation-in-progress. (* See the "Storage Management" section.) Availability of the relations while upgrade is in progress is likely to be the same as in VACUUM FULL for that relation, i.e. the entire relation is locked until the upgrade is complete. Maybe we could optimize that by locking only particular pages of relations, I have to figure that out. The upgrade of indices can be done using REINDEX, which seems far less complicated than trying to convert its on-disk representation. This has to be done after the relation is converted. Alternatively, the index upgrade can simply be done by "CREATE INDEX" after the upgrade of relations. The relations to be upgraded are ordered according to some priority, e.g. system relations being first, then user-owned relations. System relations upgrade is forced upon the postmaster startup, and then user relations are processed lazily. So, in a sense, pg_autoupgrade will act like a proxy choosing appropriate storage manager (like a driver) between the new server and the old data file upgrading them on-demand. For that purpose we might need to add a pg_upgradeproxy to intercept backend requests and use appropriate storage manager. There will be one proxy process per backend. Storage Management ================== Somebody has made a possibility to plug a different storage manager in postgres and we even had two of them at some point . for the magnetic disk and the main memory. The main memory one is gone, but the smgr API is still there. Some were dubious why we would ever need another third-party storage manager, but here I propose to "plug in" storage managers from the older Postgres versions itself! Here is where the pluggable storage manager API would be handy once fully resurrected. Instead of trying to plug some third party storage managers it will primarily be used by the storage managers of different versions of Postgres. We can take the storage manager code from the past maintenance releases, namely 6.5.3, 7.0.3, 7.1.3, 7.2.5, 7.3.7, 7.4.5, and 8.0, and arrange them in appropriate fashion and have them implement the API properly. Anyone can contribute a storage manager as they see fit, there's no need to get them all at once. As a trial implementation I will try to do the last three or four maybe. Where to put relations being upgraded? -------------------------------------- At the beginning of the upgrade process if pg detects the old version of data files, it moves them under $PGDATA/<ver>, and keeps the old relations there until upgraded. The relations to be upgraded will be kept in the pg_upgrade_catalog. Once all relations upgraded, the <ver> directory is removed and the auto and proxy processes are shut down. The contents of the pg_upgrade_catalog emptied. The only issue remains is how to deal with tablespaces (or LOCATION in 7.* releases) elsewhere .- this can probably be addressed in the similar fashion, but having a /my/tablespace/<ver> directory. Source Code Maintenance ======================= Now, after the above some of you may get scared on the amount of similar code to possibly maintain in all those storage managers, but in reality they would require as much maintenance as the corresponding releases do get back-patched in that code area, and some are not being maintained for quite some time already. Plus, I should be around to maintain it, should this become realized. Release-time Maintenance ------------------------ For maintenance of pg_upgrade itself, one will have to fork out a new storage manager from the previous stable release and "register" it within the system. Alternatively, the new storage manager can be forked when the new release cycle begins. Additionally, a pg_upgrade version has to be added implementing the API steps outlined in the pg_upgrade API section. Implementation Steps ==================== To materialize the above idea, I'd proceed as follows: *) Provide the initdb() API (quick) *) Resurrect the pluggable storage manager API to be usable for the purpose. *) Document it *) Implement pg_upgrade API for 8.0 and 7.4.5. *) Extract 8.0 and 7.4.5 storage managers and have them implement the API as a proof of concept. Massage the API as needed. *) Document the process of adding new storage managers and pg_upgrade drivers. *) Extract other versions storage managers. pg_upgrade sequence ------------------- pg_upgrade API for the steps below to update for the next release. What to do with WAL?? Maybe upgrade can simply be done using WAL replay with old WAL manager? Not, fully, because not everything is in WAL, but some WAL recovery maybe needed in case the server was not shutdown cleanly before the upgrade. pg_upgrade will proceed as follows: - move PGDATA to PGDATA/<major pg version> - move tablespaces likewise - optional recovery from WAL in case old server was not shutdown properly -? Shall I upgrade PITR logs of 8.x??? So one can recover to a point-in-time in the upgraded database? - CLUSTER all old data - ANALYZE all old data - initdb() new system catalogs - Merge in modifications from old system catalogs - upgrade schemas/users -- variations - upgrade user relations Upgrade API: ------------ First draft, to be refined multiple times, but to convey the ideas behind: moveData() movePGData() moveTablespaces() 8.0+ moveDbLocation() < 8.0 preliminaryRecovery() - WAL?? - PITR 8.0+?? preliminaryCleanup() CLUSTER -- recover some dead space ANALYZE -- gives us stats upgradeSystemInfo() initdb() mergeOldCatalogs() mergeOldTemplates() upgradeUsers() upgradeSchemas() - > 7.2, else NULL upgradeUserRelations() upgradeIndices() DROP/CREATE upgradeInit() { } The main body in pseudocode: upgradeLoop() { moveData(); preliminaryRecovery(); preliminaryCleanup(); upgradeSystemInfo(); upgradeUsers(); upgradeSchemas(); upgradeUserRelations(); } Something along these lines the API would be: typedef struct t_upgrade { bool (*moveData) (void); bool (*preliminaryRecovery) (void); /* may be NULL */ bool (*preliminaryCleanup) (void); /* may be NULL */ bool (*upgradeSystemInfo) (void); /* may be NULL */ bool (*upgradeUsers) (void); /* may be NULL */ bool (*upgradeSchemas) (void); /* may be NULL */ bool (*upgradeUserRelations) (void); /* may be NULL */ } t_upgrade; The above sequence is executed by either pg_upgrade utility uninterrupted or by the pg_autoupgrade daemon. In the former the upgrade priority is simply by OID, in the latter also, but can be overridden by the user using the UPGRADE command to schedule relations upgrade, write operation can also change such schedule, with user's selected choice to be first. The more write requests a relation receives while in the upgrade queue, its priority increases; thus, the relation with most hits is on top. In case of tie, OID is the decision mark. Some issues to look into: - catalog merger - a crash in the middle of upgrade - PITR logs for 8.x+ - ... Flames and Handwaiving ---------------------- Okay, flame is on, but before you flame, mind you, this is a very initial version of the design. Some of the ideas may seem far fetched, the contents may seem messy, but I believe it's now more doable than ever and I am willing to put effort in it for the next release or two and then maintain it afterwards. It's not going to be done in one shot maybe, but incrementally, using input, feedback, and hints from you, guys. Thank you for reading till this far :-) I.d like to hear from you if any of this made sense to you. Truly yours, -- Serguei A. Mokhov | /~\ The ASCII Computer Science Department | \ / Ribbon Campaign Concordia University | X Against HTML Montreal, Quebec, Canada | / \ Email! ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
