Обсуждение: [HACKERS] FW: VACUUM FULL Error

Поиск
Список
Период
Сортировка

[HACKERS] FW: VACUUM FULL Error

От
"Hayes, Patrick"
Дата:

Hi there

Any suggestion how to get around this issue I am having with vacuum command I’m running on 8.1 version of prostgre SQL.

The VACUUM FULL command seems to get stuck on vacuuming "pg_catalog.pg_largeobject" (last message for Verbose)

 Now attempting below  - but not hopeful that it will complete successfully.

> VACUUM VERBOSE pg_catalog.pg_largeobject;

With initial Message

INFO:  vacuuming "pg_catalog.pg_largeobject"

How long should I wait for this to complete – if it ever does? It has currently been running for over 30 minutes.

 

Refer to the forwarded message below for additional information.

 

My fallback is that an archive of the existing DB (almost 2 TBytes) has been made and verified (via VEEAM Clone process). It contains all of the historical records the need to be retained in a read-only DB. The only option I seem to have is to drop the DB and start with a blank canvas. Not an option I want to take as I am not postgre SQL expect.

 

Help!!!

From: Hayes, Patrick
Sent: 29 December 2016 10:53
To: 'pgadmin-support@postgresql.org' <pgadmin-support@postgresql.org>
Subject: VACUUM FULL Error

 

Hi pgAdmin support,

 

Recently truncated tables in Postgre SQL DB and now unable to free up disk space on server with the VACUUM FULL command.

The DB is 1.91TBytes in size.

Server is running Win 2008 R2 Standard Operating System.

Postgre SQL is version 8.1

 

Error message reads as follows:

ERROR:  out of memory

DETAIL:  Failed on request of size 134217728.

 

Adding memory to server does not seem to make any difference. Re-starting the server makes no difference. The VACUUM command always ends in the same record 134217728.

Is there any way to selectively VACUUM tables – divide and conquer approach.

Thank you in advance for any support you can provide

 

Patrick Hayes

 

Re: [HACKERS] FW: VACUUM FULL Error

От
Tom Lane
Дата:
"Hayes, Patrick" <Patrick.Hayes@stryker.com> writes:
> Any suggestion how to get around this issue I am having with vacuum command I’m running on 8.1 version of prostgre
SQL.

You realize, I hope, that 8.1 has been out of support for more than six
years.

> The VACUUM FULL command seems to get stuck on vacuuming "pg_catalog.pg_largeobject" (last message for Verbose)

If that table is very large --- check with, eg,select pg_size_pretty(pg_relation_size('pg_largeobject'));
then VACUUM FULL is going to take a heck of a long time, particularly
with the old implementation that was used in 8.1.  But if your objective
is to return disk space to the OS, you may not have much choice; plain
VACUUM doesn't try very hard to do that.

> Error message reads as follows:
> ERROR:  out of memory
> DETAIL:  Failed on request of size 134217728.

I'm assuming that you are saying that VACUUM FULL fails with that, which
is not what "getting stuck" seems to mean otherwise.

If that happens to be equal to your current maintenance_work_mem setting,
you could probably dodge the problem by reducing maintenance_work_mem.
That would make it even slower :-( but at least you'd have hope of
completing eventually.

Personally I'd think very hard about going the dump-and-restore route and
updating to a somewhat modern version of Postgres while you're at it.
There are an awful lot of known bugs in 8.1, even assuming that you're
on the last minor release 8.1.23.

Updating to an OS that's still supported by its maker would be a bright
move as well.
        regards, tom lane