0

I have been looking through old questions but have not found a question that is close enough to my issue. If anyone has seen this issue elsewhere, please advise.

My situation. A past DBA created user-defined tablespaces in PostgreSQL. We are planning to convert from PostgreSQL 9.0.23 to 9.4.6. However, we see no advantage to continuing to manage user-defined tablespaces and are in the process of converting them to the "pg_default" tablespaces.

This worked successfully for 217 tablespaces. However with 3 of the tablespaces, after the conversion, we were not able to drop the old user-defined tablespace due to a "tablespace not empty" error. Queries of the 3 tablespaces showed no objects in them. Queries of pg_default showed the objects formerly in the user-defined tablespaces now in pg_default. An examination of the rhel directories where the old user-defined tablespace data resided shows that the directory structure still exists in those locations. Those structures were deleted in the other 217 tablespaces.

An article that discusses this problem in some degree is: How can I tell what is in a Postgresql tablespace? with the following advice:

“Prowl around in there to see if something real is in your way. I'd be really cautious about trying to delete anything in there apart from using PostgreSQL's interface, though.”

(I was able to drop the tablespaces in a test environment by deleting these linux directories. However, in our production instance, we want to drop the tablespaces from inside the PostgreSQL interface. Has anyone seen this problem and had a solution?

Community
  • 1
  • 1
Shane Furlong
  • 1
  • 3
  • You should probably edit this so it's readable; it's a wall of text I can barely follow. – Craig Ringer Feb 29 '16 at 15:29
  • You could use pg_dump and grep for in its output. – joop Feb 29 '16 at 15:38
  • BTW: tablespaces are installation-wide, so they could be refferred by other databases within your cluster (eg: dev-id or test-db) – joop Feb 29 '16 at 16:03
  • FYI. Looking at the files in the old linux locations, each of the tablespaces has the same orphaned object. It has an oid of 16745 which does not match up with any currently active objects. If the object that matched oid 16745 no longer exists, it would be impossible to move that object to a new tablespace. I am trying now to locate an old version of this database which might still have that object active. It is possible that that oid corresponds to an old schema. The number is similar to some current schemas. – Shane Furlong Mar 01 '16 at 15:47
  • Silly me. That OID was the database OID. The OIDs for two of the "ophaned" tablespaces are on the same table. One tablespace is for the indexes referencing that table. The other tablespace is for the table files. The interesting thing is that the same table and index are now in pg_default after the alter default tablespace command was issued. I believe the third tablespace will drop once these first 2 are dealt with. I am still reluctant to just delete the linux files and then drop the old tablespaces. This has worked in a testbed situation. – Shane Furlong Mar 02 '16 at 14:33

0 Answers0