Re: ERROR: tablespace "archive2" is not empty

On 10/20/2015 12:22 AM, Josip Rodin wrote:
> On Mon, Oct 19, 2015 at 12:02:17PM -0700, Adrian Klaver wrote:
>>> No idea, I inherited this machine. But like Tom said, spclocation being
>>> wrong is apparently harmless.
>>
>> Other then you cannot DROP the tablespace:) This will probably
>> needed to be resolved for the reasons that came up in this thread:
>>
>> http://www.postgresql.org/message-id/CAAW2xfeA1mPw4RW3-4LUTW+y4ruQFPT5Bx4c3DKUb01f4ejabw@mail.gmail.com
>>
>> it is a very long thread so you might to take a look at this:
>>
>> http://www.postgresql.org/message-id/7681.1389548920@sss.pgh.pa.us
>>
>>
>> What happens if you change spclocation to the correct location?
>
> The suggestion makes perfect sense, but sadly there's no difference:
>
> % sudo -H -u postgres psql template1 -c "update pg_tablespace set spclocation = '/media/ssd/archive2/postgresql' where spcname = 'archive2';"
> UPDATE 1
> % sudo -H -u postgres psql template1 -c "drop tablespace archive2;"
> ERROR: tablespace "archive2" is not empty
>
> I guess UTSL would be my remaining recourse, but I was hoping someone
> who already knows that code path offhand could put me at ease :)

This database(cluster) has been running without any issues related to
not finding archive2 tablespace objects, correct?

The following found nothing in pg_class that is using the tablespace:

mydb=> select oid, * from pg_tablespace where spcname = 'archive2';
oid | spcname | spcowner | spclocation | spcacl | spcoptions
---------+----------+----------+----------------------+--------+------------

7849107 | archive2 | 10 | /media/archive2/psql | |

mydb=> select * from pg_class where reltablespace = 7849107;
(0 rows)

It seems at a practical level the tablespace does not exist from the
view point of the database. Assuming you have had no tablespace issues
to date, I would be inclined to go the manual route. As always backup
before proceeding. By manual, I mean mv the files in:

/media/ssd/archive2/postgresql/PG_9.1_201105231/117264/

to somewhere else, then try to DROP the tablespace.

>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com