Re: PG 8.0.4 - Does Dump + drop_db + reload reset XID to prevent wraparound?

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Saturday, June 21, 2008 11:25 AM
> To: Bill Bartlett
> Cc: pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] PG 8.0.4 - Does Dump + drop_db + reload reset XID to
> prevent wraparound?
>
> "Bill Bartlett" <bbartlett(at)softwareanalytics(dot)com> 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