Vacuum Full (PG 8.1) - Urgent help needed - Cancel & transaction "liberation"

From: Alexandre Leclerc <aleclerc(at)ipso(dot)ca>
To: pgsql-admin(at)postgresql(dot)org
Subject: Vacuum Full (PG 8.1) - Urgent help needed - Cancel & transaction "liberation"
Date: 2010-04-16 13:18:20
Message-ID: 4BC8639C.4010202@ipso.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi all,

I'm sorry for the urgency of the question. (We have a customer whose DB
is "down" since 36 hours and business operations are compromised. Thank
you for your help.)

*Background:*
- PostgreSQL 8.1 on Windows Server
- The customer has disabled regular VACUUM jobs for backup to be taken,
a year or two ago.
- They didn't told us (as far as I can remember).
- Wednesday morning at 10:55:50: database is shut down to avoid
wraparound data loss in database *db*

*What has been done:*
- The message requested a VACUUM FULL so we stopped the postmaster and
started postgres.exe to launch a VACUUM FULL.
- 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).
- 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).

*What can we do?*
- 1.1 Can we stop the VACUUM FULL (CTRL+C in the shell) and start
postmaster again? So the company can continue working and then continue
the VACUUM FULL during the weekend?
- 1.2 Will the transactions to avoid warparound data loss be available
(in part at least) even if we stop the vacuum?
- 2. Could we stop VACUUM FULL and simply restart postmaster and
starting a normal VACUUM even if it's slow?
- 3. Is it possible to increase the transactions limit to something
bigger as a temporary solution so that the customer can continue its work?

I feel the pain of my client and understand that they need access to
their data, but I would not like to loose all the cleaning that has been
done. They have couple tables that are couple GB in size. And if
stopping the VACUUM FULL does not give access to available transactions
before the warparound shut down security, well it give nothing to stop
it: that would be worst.

I would need an expert's advice on the question.

Being very grateful for your help,

--
Alexandre Leclerc

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Greg Smith 2010-04-16 13:59:03 Re: Vacuum Full (PG 8.1) - Urgent help needed - Cancel & transaction "liberation"
Previous Message Khangelani Gama 2010-04-16 08:22:27 How can I find a broken row in a table