Alexandre Leclerc <aleclerc(at)ipso(dot)ca> wrote:
> - PostgreSQL 8.1 on Windows Server
That's not a supported environment.
http://www.postgresql.org/about/news.865
They should really be looking at upgrading.
> - The customer has disabled regular VACUUM jobs for backup to be
> taken, a year or two ago.
Ouch. Once you recover from the immediate emergency, you they need
to institute a sane vacuum/analyze policy, probably using some
combination of autovacuum and scheduled database vacuums.
> - Wednesday morning at 10:55:50: database is shut down to avoid
> wraparound data loss in database *db*
> - The message requested a VACUUM FULL
No. It didn't. It said "execute a full-database VACUUM".
> so we stopped the postmaster and started postgres.exe to launch a
> VACUUM FULL.
It was a mistake to use FULL, since that can run for days, and will
bloat indexes. It's almost never the right thing to do.
> - During the night an employee of our client has stop (CTRL+C)
> and restarted many many times the VACUUM FULL (trying to see the
> progress of it).
Each time that's done it will add bloat, making things worse.
> - So yesterday morning, knowing that we gave instructions to let
> the job go without interruptions, which they did.
> - It worked for about 24 hours now, and we don't see the end of
> it. The DB folder is now 38 GB (original DB was probably around
> 7GB of real data - but these were the numbers two ago).
Yeah, you're going to want to clean up all the bloat from these
mis-steps, but you have more immediate issues.
> - 2. Could we stop VACUUM FULL and simply restart postmaster and
> starting a normal VACUUM even if it's slow?
I would do that, but I'm not at all sure it would be safe for anyone
to try to use the database before the VACUUM completes. Once the
database VACUUM completes, they can use the database, but they're
likely to notice it's a bit slow. In the first available
maintenance window after that (e.g., a weekend), I would recommend
that they do a pg_dump of the database and restore it, followed by
VACUUM ANALYZE (again, not FULL). And then they should work out a
plan for an upgrade to a supported version.
> - 3. Is it possible to increase the transactions limit to
> something bigger as a temporary solution so that the customer can
> continue its work?
No.
-Kevin