> -----Original Message----- > From: Tom Lane [mailto:tgl@xxxxxxxxxxxxx] > Sent: Saturday, June 21, 2008 11:25 AM > To: Bill Bartlett > Cc: pgsql-admin@xxxxxxxxxxxxxx > Subject: Re: [ADMIN] PG 8.0.4 - Does Dump + drop_db + reload reset XID to > prevent wraparound? > > "Bill Bartlett" <bbartlett@xxxxxxxxxxxxxxxxxxxxx> writes: > > In a PostgreSQL 8.0.4 environment, does dropping the database and > > reloading it completely reset the XID information that causes > > transaction ID wraparound? > > I gather from the reference to "d:" that you're running on Windows. On these specific servers, yes. > You really, really, really need to put an urgent priority on getting > off 8.0.x. We're working on it, but it's a MAJOR undertaking to do. Unfortunately, because of all the changes that are made on each release of PostgreSQL (some intentional changes, some accidental "breakages"), we need to be VERY careful that none of these impact our code and then change our code where they do impact us. In many cases, due to the complexity of our app even some of the changes in minor point releases require code changes. Ultimately, this then requires retesting of the entire app [client app, server app, database code, several associated supporting apps, etc], so the work effort there is substantial and the consequences of missing something are large. (For example, we missed one single line in the release notes when converting from PostgreSQL 7.2 to 7.3 ["The data type timestamp is now equivalent to timestamp without time zone, instead of timestamp with time zone."], the impact of which ultimately required a 3 man-week project to reconcile data that had not synchronized properly. Admittedly this was our developer's fault for not reviewing the release notes carefully enough, but as he explained, he never expected the meaning of a data type to change once it had been established.) Finally we have the issue of rolling out the updated database engine itself; this requires a "dump database, uninstall existing PostgreSQL version, install new PostgreSQL version, reload the database and do any cleanup or reindexing needed" process ... on each of 150+ servers, all of which are running at 150+ different remote sites. Thus we either write VERY careful scripts to do each step and handle errors and recovery/rollback on each step or we have to do this manually on each server. Doable, but a substantial amount of work. (I completely agree that it definitely needs to be done; however, given the effort required, we didn't want to do this until we were absolutely sure that the PostgreSQL crashes that we frequently see were completely fixed. We were about to move to 8.3.1, but then saw the posting about JDBC performance being severely impacted by an 8.3.x change so plans went on hold until 8.3.2; now it's a matter of trying to schedule the time again.) > (Or get off Windows, but I suppose I'm wasting my breath suggesting that.) We actually used to be a purely Linux shop, but got burned so badly by a wide range of Linux issues (constant changes in CONF file locations with every release of included apps [making automated server builds very difficult], OS upgrades dictated by hardware changes, drivers that would appear or disappear with each version of the OS change, security updates that would require kernel rebuilds that led to other apps breaking, lack of support for minor point differences in distro or OS versions by commercial products we used, lack of specific types of apps (either free or commercial) that we needed, the lack of enough benevolent dictators in the Linux space to enforce any real sort of standardization, etc. etc.) that we eventually had to embark on a [now 2+ year] project to move off Linux onto Windows servers instead. (I haven't had time to blog about that whole painful background and journey, but one of these days I need to find the time to do so.) > The rename problem that you're hitting is fixed in > 8.2 and up, as are a bunch of other Windows-specific problems that > will never be fixed in pre-8.2 branches, because we have abandoned > support for those branches on Windows. I thought I had seen listserv posts that said the rename problem still happened at least in some of the 8.2 versions, although it was supposedly finally fully fixed in 8.3? > As for the specific question, I think it'd work as long as template0 > has never been unfrozen, but an initdb would be a lot more certain > --- and since you apparently have only one user database, there's > no difference in how much reload work you'd have to do. > > Lastly, there is no need to use VACUUM FULL for wraparound protection; > plain VACUUM is sufficient. The critical point though is that it has > to be a database-wide VACUUM (and done by a superuser); 8.0 doesn't > track this at a per-table grain, only per-database. > > regards, tom lane Both the "dump, drop and reload" and the "vacuum [without FULL]" processes did work fine, although the "dump, drop and reload" was much faster (17 hrs vs 36 hrs). (We have two identical databases, so I tried one method on one and the other on the other.) Thanks much! - Bill
