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