At 07:06 PM 8/7/2006, Michael Fuhr wrote:
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?
It's a new install as of yesterday; some DDL and the COPY operations
are the only things that have been done to it. I've done a VACUUM
and a VACUUM FULL on it with no effect.
Note that if needed I can whack the database and start over very
easily, if that's the most expedient way out of this.
> 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;
database 'postgres'
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);
The directory contains the following files:
17383
17383.1
17385
17385.1
17387
17388
The query returns no rows with an IN clause of
...
WHERE c.relfilenode IN (17383, 17385, 17387, 17388);
Note that during one of the copies the system became quiescent and I
ended up stopping what I thought was the server process that had hung:
2006-08-07 16:34:00 LOG: checkpoints are occurring too frequently
(12 seconds apart)
2006-08-07 16:34:00 HINT: Consider increasing the configuration
parameter "checkpoint_segments".
2006-08-07 16:34:09 LOG: checkpoints are occurring too frequently (9
seconds apart)
2006-08-07 16:34:09 HINT: Consider increasing the configuration
parameter "checkpoint_segments".
2006-08-07 16:34:19 LOG: checkpoints are occurring too frequently
(10 seconds apart)
2006-08-07 16:34:19 HINT: Consider increasing the configuration
parameter "checkpoint_segments".
2006-08-07 16:35:00 LOG: autovacuum: processing database "template1"
2006-08-07 16:36:04 LOG: autovacuum: processing database "postgres"
2006-08-07 16:37:08 LOG: autovacuum: processing database "template1"
2006-08-07 16:38:11 LOG: autovacuum: processing database "postgres"
2006-08-07 16:39:16 LOG: autovacuum: processing database "template1"
2006-08-07 16:40:19 LOG: autovacuum: processing database "postgres"
2006-08-07 16:41:23 LOG: autovacuum: processing database "template1"
2006-08-07 16:42:27 LOG: autovacuum: processing database "postgres"
2006-08-07 16:43:28 LOG: autovacuum: processing database "template1"
2006-08-07 16:44:29 LOG: autovacuum: processing database "postgres"
2006-08-07 16:45:33 LOG: autovacuum: processing database "template1"
2006-08-07 16:46:35 LOG: autovacuum: processing database "postgres"
2006-08-07 16:47:39 LOG: autovacuum: processing database "template1"
2006-08-07 16:47:56 LOG: server process (PID 5140) was terminated by signal 1
2006-08-07 16:47:56 LOG: terminating any other active server processes
2006-08-07 16:47:56 LOG: all server processes terminated; reinitializing
2006-08-07 16:47:56 LOG: database system was interrupted at
2006-08-07 16:39:19 Central Daylight Time
2006-08-07 16:47:56 LOG: checkpoint record is at 0/ED0AC1E8
2006-08-07 16:47:56 LOG: redo record is at 0/ED0AC1E8; undo record
is at 0/0; shutdown FALSE
2006-08-07 16:47:56 LOG: next transaction ID: 1995; next OID: 25168
2006-08-07 16:47:56 LOG: next MultiXactId: 1; next MultiXactOffset: 0
2006-08-07 16:47:56 LOG: database system was not properly shut down;
automatic recovery in progress
2006-08-07 16:47:56 LOG: record with zero length at 0/ED0AC230
2006-08-07 16:47:56 LOG: redo is not required
2006-08-07 16:47:56 LOG: database system is ready
2006-08-07 16:47:56 LOG: transaction ID wrap limit is 1073742403,
limited by database "template1"
Do such queries show anything?
--
Michael Fuhr
