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
