Vacuum error message

[Raymond McKolay]

The 16396 is a directory under the postgresql/data/base path and
within it are file files named 16768, 16768.1, 16768.2, 16768.3 and
16768.4
And can you copy those somewhere else?

I moved the files to the desktop of the server and changed the max_fsm_pages to 819200 and the same error message still comes up.
Do I need more free space on the server than what the size of the DB is in order to Vacuum the DB?
The reason I changed the fsm_pages vaule was due to a hint in the message log.

Ray

[Kevin Grittner]

The 16396 is a directory under the postgresql/data/base path and
within it are file files named 16768, 16768.1, 16768.2, 16768.3
and 16768.4

And can you copy those somewhere else?

I moved the files to the desktop of the server [...] and the same
error message still comes up.

I'm not sure what you mean by "moved" -- the point was to copy the
contents of the files so that you could determine whether the OS
could read all the pages in the file. Under some definitions of
"move" you would only have touched the directory entries for the
files, which wouldn't give us the information we were looking for.

Do I need more free space on the server than what the size of the
DB is in order to Vacuum the DB?

A normal vacuum of the database doesn't require much (if any) disk
space. VACUUM FULL is another story, but you probably don't want to
go there.

-Kevin

[Raymond McKolay]

I moved the files to the desktop of the server [...] and the same
error message still comes up.
I'm not sure what you mean by "moved" -- the point was to copy the contents of the files so that you could determine whether the OS could read all the pages in the file. Under >some definitions of "move" you would only have touched the directory entries for the files, which wouldn't give us the information we were looking for.

I took the files from the directory they were in and changed the path.
I guess I don't understand how to copy the contents of the file to accomplish what you are asking.
I presumed that by changing the path of the files the vacuum operation would either skip them or error out saying that they were missing.
Could you help with that please?

Do I need more free space on the server than what the size of the DB
is in order to Vacuum the DB?
A normal vacuum of the database doesn't require much (if any) disk space. VACUUM FULL is another story, but you probably don't want to go there.

I am pretty sure that a vacuum full is eventually what needs to happen since the DB contains useless records and I need the disk space given back to the OS.
But then again I'm no expert with postgres so I am only guessing.

Ray

[Tom Lane]

Post by Raymond McKolay
I took the files from the directory they were in and changed the path.
I guess I don't understand how to copy the contents of the file to accomplish what you are asking.
I presumed that by changing the path of the files the vacuum operation would either skip them or error out saying that they were missing.

I'd have expected a missing-files failure, too. Are you sure the error
message was the same thing for the same files? If this is a filesystem
corruption, it's quite possible it affects more than one file. There's
no very good reason to assume VACUUM would visit the files in the same
order every time, either, so it's possible it would fail on some other
table before getting to this one.

But anyway, what Kevin and I were suggesting was that you try to
duplicate the files, using "cp" on the command line, or Duplicate
in the Finder.

Post by Raymond McKolay
I am pretty sure that a vacuum full is eventually what needs to happen since the DB contains useless records and I need the disk space given back to the OS.
But then again I'm no expert with postgres so I am only guessing.

The error message you have told us about is *entirely* unrelated to
needing extra disk space. It's about being unable to read a file that
is already there.

regards, tom lane

[Raymond McKolay]

Solved. It was a combination of OS corruption and limits on max_fsm_pages being too small.
Thanks for all of your help. It was a learning experience.

-----Original Message-----
From: Tom Lane [mailto:***@sss.pgh.pa.us]
Sent: Wednesday, August 22, 2012 10:51 AM
To: Raymond McKolay
Cc: Kevin Grittner; pgsql-***@postgresql.org
Subject: Re: [NOVICE] Vacuum error message

Post by Raymond McKolay
I took the files from the directory they were in and changed the path.
I guess I don't understand how to copy the contents of the file to accomplish what you are asking.
I presumed that by changing the path of the files the vacuum operation would either skip them or error out saying that they were missing.

I'd have expected a missing-files failure, too. Are you sure the error message was the same thing for the same files? If this is a filesystem corruption, it's quite possible it affects more than one file. There's no very good reason to assume VACUUM would visit the files in the same order every time, either, so it's possible it would fail on some other table before getting to this one.

But anyway, what Kevin and I were suggesting was that you try to duplicate the files, using "cp" on the command line, or Duplicate in the Finder.

Post by Raymond McKolay
I am pretty sure that a vacuum full is eventually what needs to happen since the DB contains useless records and I need the disk space given back to the OS.
But then again I'm no expert with postgres so I am only guessing.

The error message you have told us about is *entirely* unrelated to needing extra disk space. It's about being unable to read a file that is already there.

regards, tom lane