Thread: Getting weird pg_tblspc error, has anyone seen this before?
I have a client who is running this query(just brings back info about the databases on the server): select pgd.datname as database, pdesc.description, pgr.rolname as owner, pgt.spcname as tablespace, pg_size_pretty(pg_database_size(pgd.oid)) as dbsize, pg_encoding_to_char(encoding) as encoding, pgd.oid from pg_database pgd LEFT JOIN pg_roles pgr on pgr.oid = pgd.datdba LEFT JOIN pg_description pdesc on pdesc.objoid = pgd.oid LEFT JOIN pg_tablespace pgt on pgd.dattablespace = pgt.oid WHERE pgd.datname not like 'template%' ORDER BY pgd.datname; The exact error is(as reported from PG Admin III): ERROR: could not open tablespace directory "pg_tblspc": No such file or directory SQL state: 58P01 The user is running on a Mac PC that is booting Windows XP via Bootcamp. The user also claims that the error only occurs when running the query locally i.e. localhost, but when he runs the query from a different PC it works and he gets a result set back. The error occurs regardless of the client as well, same error is reported via PG Admin III, PSQL, and LA. Other queries such as select * from pg_tablespace work fine so I am at a loss as why this query gives a pg_tblspc error. He is running 8.2.6 win32 version on a Mac core 2 system that dual boots to XP. I have been trying for days to reproduce the error on my PCs running the client and server on the same PC with no luck, I have tried the same version of PGSQL he is running as well as 8.3 with no luck. I am missing some info from the client like what user he is running as and what his pg_hba.conf looks like. If anyone has seen this before please let me know. Thanks, Tony Caduto
Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:
> The exact error is(as reported from PG Admin III):
> ERROR: could not open tablespace directory "pg_tblspc": No such file or
> directory
This must be coming from calculate_database_size(). Is
$PGDATA/pg_tblspc actually missing? If the guy has no custom
tablespaces, I can believe that nothing except pg_database_size()
or pg_tablespace_size() would try to touch that subdirectory, so
he might not otherwise notice that it'd gone missing.
> The user also claims that the error only occurs when running the query
> locally i.e. localhost, but when he runs the query from a different PC
> it works and he gets a result set back.
That's kind of in the category of "I don't believe it". I suspect pilot
error, ie he was talking to a different postmaster.
regards, tom lane
Tom Lane wrote: > This must be coming from calculate_database_size(). Is > $PGDATA/pg_tblspc actually missing? If the guy has no custom > tablespaces, I can believe that nothing except pg_database_size() > or pg_tablespace_size() would try to touch that subdirectory, so > he might not otherwise notice that it'd gone missing. > > Thanks Tom, I will follow up with the client and have him check if that directory is missing. I think he may have used the Postbooks win32 installer to install his server, so it might be that their installer is messed up and not creating the directory properly or messing something up with the environment vars where it can't find the directory. Later, Tony Caduto
Tony Caduto wrote: > Tom Lane wrote: >> This must be coming from calculate_database_size(). Is >> $PGDATA/pg_tblspc actually missing? If the guy has no custom >> tablespaces, I can believe that nothing except pg_database_size() >> or pg_tablespace_size() would try to touch that subdirectory, so >> he might not otherwise notice that it'd gone missing. >> > Thanks Tom, > I will follow up with the client and have him check if that directory is > missing. > I think he may have used the Postbooks win32 installer to install his > server, so it might be that their installer is messed up and not > creating the directory properly or messing something up with the > environment vars where it can't find the directory. The pg_tblspc directory is created by initdb, not the installer. And IIRC doesn't touch any environment variables. It does set the data path on the commandline, but if that one is incorrect you shouldn't even get that far. //Magnus
Magnus Hagander wrote: > Tony Caduto wrote: >> I will follow up with the client and have him check if that directory >> is missing. >> I think he may have used the Postbooks win32 installer to install his >> server, so it might be that their installer is messed up and not >> creating the directory properly or messing something up with the >> environment vars where it can't find the directory. > > The pg_tblspc directory is created by initdb, not the installer. And > IIRC doesn't touch any environment variables. It does set the data > path on the commandline, but if that one is incorrect you shouldn't > even get that far. > > //Magnus > Hi, I just used the postbooks installer and it did not create the pg_tblspc directory, so the issue appears to be caused by a messed up Postbooks win32 installer. Check out this screenshot: http://www.milwaukeesoft.com/postbooks_datadir.png They must be deleting it after the initdb, maybe they figured since it was empty they did not need it? Later, Tony
Tony Caduto <tony_caduto@amsoftwaredesign.com> writes: > I just used the postbooks installer and it did not create the pg_tblspc > directory, so the issue appears to be caused by a messed up Postbooks > win32 installer. Check out this screenshot: > http://www.milwaukeesoft.com/postbooks_datadir.png Bizarre. I hope you'll tell them to fix that. In the meantime, manually creating that directory should fix the problem, as long as you make sure it's got the same ownership and permissions as the other subdirectories. regards, tom lane
Tom Lane wrote: > Bizarre. I hope you'll tell them to fix that. > > > Agreed :-) I bet they just took a snapshot of a install dir that had the postmaster stopped and used that in their setup. It probably does not do a initdb during the setup. Why it's missing that directory is a mystery :-) I did report the issue on the PostBooks sourceforge project forum. Later, Tony Caduto