Thread: DROP TABLESPACE fails

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.

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?

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

Michael Fuhr <mike@fuhr.org> writes:
> On Mon, Aug 07, 2006 at 05:36:49PM -0500, Steve Peterson wrote:
>> 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.

Indeed.

> The large files are probably within a database's subdirectory and
> their names are probably numbers.

Yeah, most likely they belong to a different database.  We should try
harder to give a helpful error message in this case ... we can't really
identify the files but we could tell you which database they belong to.

            regards, tom lane

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

On Tue, Aug 08, 2006 at 11:25:23AM -0500, Steve Peterson wrote:
> Note that if needed I can whack the database and start over very
> easily, if that's the most expedient way out of this.

That might be the easiest thing to do, but it might also be worth
investigating what happened.  It's possible that you've encountered
a case where files should have been removed but weren't, in which
case the developers might be interested in finding out why.

> >SELECT datname FROM pg_database WHERE oid = 16388;
>
> database 'postgres'

And that's the database you were connected to when making the
following query?  Just making sure.

> 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:47:56 LOG:  server process (PID 5140) was terminated by signal 1

Maybe one of the developers can comment on whether this might have
caused a problem.

--
Michael Fuhr