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

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
am assuming that it does, but wanted to double-check before doing this.)

From what I can tell, I am just about to hit the XID wraparound problem, given
the following results:

   SELECT datname, age(datfrozenxid) FROM pg_database;
     datname  |    age
   -----------+------------
   datadb    | 2002912692
    template1 | 1034177089
    template0 | 1034177089
   (3 rows)

I know that a VACUUM FULL will fix this, but on this database a VACUUM FULL
usually takes more than 48 hrs to complete and [more importantly], a VACUUM FULL
usually crashes the PostgreSQL engine.  (We _very_ frequently crash with a '
could not rename "d:/pgsql/data/pg_xlog/0000000100000504000000D5" to
"d:/pgsql/data/pg_xlog/0000000100000504000000E8", continuing to try ' error just
with our normal volume of database activity; running any decent-sized VACUUM is
almost always guaranteed to crash at some point with this error or with one of
the "could not fsync" or "could not unlink" errors.)

So ... I'm looking for a viable alternative to a VACUUM FULL to prevent the
impending transaction wraparound.  (Note that I _have_ been doing vacuums on all
of the individual tables in this database every week but I don't think this
helps prevent the XID wraparound problem in a PG 8.0.x environment?  [By
vacuuming the tables one at a time we've managed to get a 30-40% success rate of
getting through vacuuming all the tables without the database crashing.])

I do understand that this version of PostgreSQL is very old; however, at the
moment I am just looking for an immediate solution to this problem. (The reasons
for it still being at this old version are long and at some point need to be the
subject of another posting.  We are looking at moving it to PG 8.3.x since the
"rename" problem appears to finally be fixed; however, we need to wait until
8.3.2 or later because much of the database access is via JDBC.)

Thanks in advance for any insights...

- Bill

"Bill Bartlett" <bbartlett@softwareanalytics.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.
You really, really, really need to put an urgent priority on getting
off 8.0.x.  (Or get off Windows, but I suppose I'm wasting my breath
suggesting that.)  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.

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

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