On Mon, Aug 07, 2006 at 05:36:49PM -0500, Steve Peterson wrote:
> I'm doing some tests with version 8.1.4 on Windows XP and am having
> trouble recovering some disk space.
>
> The tests involve loading some largeish CSV files in via COPY. One
> of the COPY commands failed and I'm trying to recover the disk space
> without having to completely reinitialize things.
You might be able to recover disk space by running VACUUM FULL
against the table you were loading. Have you tried that? Are you
running VACUUM (with or without FULL) regularly?
> DROP TABLESPACE x returns 'tablespace "x" is not empty', but if I
> examine the 'referenced by' tab in pgAdmin nothing is listed. The
> tablespace directory contains several very large files.
>
> Can these files be deleted safely with the server shut down?
I'd recommend investigating what the files are before deleting them.
Have you queried the system catalogs directly to see if you can
identify the files? In the tablespace directory should be a file
named PG_VERSION and zero or more numbered subdirectories. Those
numbers correspond to database oids, so make sure you're connected
to the right database. For example, if a subdirectory is named
16388 then you can find out what database it is with the following
query:
SELECT datname FROM pg_database WHERE oid = 16388;
The large files are probably within a database's subdirectory and
their names are probably numbers. For example, if under the
tablespace's 16388 subdirectory are files named 706712, 706715, and
706717, then you can find out what they are by connecting to the
database identified above and querying pg_class:
SELECT t.spcname, n.nspname, c.relname, c.relfilenode, c.relkind
FROM pg_class AS c
LEFT JOIN pg_namespace AS n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace AS t ON t.oid = c.reltablespace
WHERE c.relfilenode IN (706712, 706715, 706717);
Do such queries show anything?
--
Michael Fuhr
