Re: DROP TABLESPACE fails

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