Thread: Why can't I drop a tablespace?
Dear Experts, Yesterday I had a disk-nearly-full problem, and decided to try to resolve it by moving one large table to a spare disk in a new tablespace: =# create tablespace tempspace location "/db_temp"; =# alter table requests set tablespace tempspace; That didn't work; I think disk space had actually reached zero: PANIC: could not write to file "pg_wal/xlogtemp.19369": No space left on device STATEMENT: alter table requests set tablespace tempspace; So I shut down the database and resolved the problem in a more conventional way by resizing the filesystem. It is now (apparently) functioning normally. BUT: I am unable to drop the tablespace that I created: =# drop tablespace tempspace; ERROR: tablespace "tempspace" is not empty On inspection /db_temp does contain a few GB of data that looks consistent with my table "requests" that I had tried to move. postgres:/db_temp/PG_11_201809051$ ls -l 17829/ total 2894972 -rw------- 1 postgres postgres 32137216 Jul 8 18:35 486095 -rw------- 1 postgres postgres 37240832 Jul 8 18:57 494286 -rw------- 1 postgres postgres 1073741824 Jul 8 19:02 502478 -rw------- 1 postgres postgres 1073741824 Jul 8 19:03 502478.1 -rw------- 1 postgres postgres 747577344 Jul 8 19:03 502478.2 I can't find what is using it: =# select * from pg_tables where tablespace = 'tempspace'; (0 rows) 17829 is the OID for the correct database, but I've not been able to find anything with OIDs corresponding to those filenames. I'm aware that things other than tables can be in tablespaces; I've also checked pg_indexes. But having only ever referred to this tablespace in the one failed command there must be a limit to what it could be. I have VACUUM FULLed this table during the recovery process, and I believe that changes the OID. I've also added a new column. What should I do? On thing I tried was =# alter table all in tablespace tempspace set tablespace ???; but that raises the question of what the tablespace name should be when moving to the default tablespace. (Is it even possible to move a table back to the default tablespace?) I am replicating from this database, could that cause any issues? (During my attempt to recover from the full disk, one issue that cropped up was that the replica needed me to create an empty directory for the new tablespace. That replica contains the same file names and all but one are identical.) This is PostgreSQL 11.12 on Debian. Thanks for any suggestions. Regards, Phil.
Phil Endecott wrote: > Yesterday I had a disk-nearly-full problem, and decided to try to resolve > it by moving one large table to a spare disk in a new tablespace: > > =# create tablespace tempspace location "/db_temp"; > =# alter table requests set tablespace tempspace; > > That didn't work; I think disk space had actually reached zero: > > PANIC: could not write to file "pg_wal/xlogtemp.19369": No space left on device > STATEMENT: alter table requests set tablespace tempspace; > > So I shut down the database and resolved the problem in a more conventional > way by resizing the filesystem. It is now (apparently) functioning normally. > > BUT: I am unable to drop the tablespace that I created: > > =# drop tablespace tempspace; > ERROR: tablespace "tempspace" is not empty > > On inspection /db_temp does contain a few GB of data that looks > consistent with my table "requests" that I had tried to move. > > postgres:/db_temp/PG_11_201809051$ ls -l 17829/ > total 2894972 > -rw------- 1 postgres postgres 32137216 Jul 8 18:35 486095 > -rw------- 1 postgres postgres 37240832 Jul 8 18:57 494286 > -rw------- 1 postgres postgres 1073741824 Jul 8 19:02 502478 > -rw------- 1 postgres postgres 1073741824 Jul 8 19:03 502478.1 > -rw------- 1 postgres postgres 747577344 Jul 8 19:03 502478.2 > > I can't find what is using it: > > =# select * from pg_tables where tablespace = 'tempspace'; > (0 rows) I've now had a look at the source for "DROP TABLESPACE" and also for the pg_tablespace_databases function, and it seems that they both inspect the filesystem, rather than looking in any catalog tables, to determine whether a database has any tables in the tablespace. Since the tablespace doesn't seem to be mentioned in pg_tables or pg_indexes, or anywhere else that I've looked, my guess is that these files are orphans that shouldn't be there - perhaps they should have been deleted when the "alter table set tablespace" or the "vacuum full" failed, but weren't. So I am tempted to delete them (or at least move them away, initially). Questions: - Any advice about identifying what the tables are? I note that the first one, 486095, is the OID of the tablespace itself plus one; does that give a clue? - Is my idea that these files should have been deleted but weren't during the disk-full panic plausible? - If I do move them away, what will Postgres do if it tries to access them and finds they are missing? Is it well-behaved in this case? - If I move them away, I would try to exercise the database (e.g. do variants of "select sum(column1) from table") in some way to make sure that it is all functioning. Any suggestions about how best to do this? Regards, Phil.
On 7/10/21 10:43 AM, Phil Endecott wrote: > Phil Endecott wrote: >> Yesterday I had a disk-nearly-full problem, and decided to try to resolve >> it by moving one large table to a spare disk in a new tablespace: >> >> =# create tablespace tempspace location "/db_temp"; >> =# alter table requests set tablespace tempspace; [snip] > - Is my idea that these files should have been deleted but weren't > during the disk-full panic plausible? > > - If I do move them away, what will Postgres do if it tries to access > them and finds they are missing? Is it well-behaved in this case? > > - If I move them away, I would try to exercise the database (e.g. > do variants of "select sum(column1) from table") in some way to make > sure that it is all functioning. Any suggestions about how best to do > this? I'd shut down postgres, rename those files, and then analyzing the relevant tables after startup. Probably also run amcheck against the relevant tables' indices. https://access.crunchydata.com/documentation/amcheck-next/1.5/#interface -- Angular momentum makes the world go 'round.
Laurenz Albe wrote: > On Fri, 2021-07-09 at 20:04 +0100, Phil Endecott wrote: >> =# create tablespace tempspace location "/db_temp"; >> =# alter table requests set tablespace tempspace; >> >> That didn't work; I think disk space had actually reached zero: >> >> PANIC: could not write to file "pg_wal/xlogtemp.19369": No space left on device >> STATEMENT: alter table requests set tablespace tempspace; >> >> So I shut down the database and resolved the problem in a more conventional >> way by resizing the filesystem. It is now (apparently) functioning normally. >> >> BUT: I am unable to drop the tablespace that I created: >> >> =# drop tablespace tempspace; >> ERROR: tablespace "tempspace" is not empty >> >> On inspection /db_temp does contain a few GB of data that looks >> consistent with my table "requests" that I had tried to move. >> >> postgres:/db_temp/PG_11_201809051$ ls -l 17829/ >> total 2894972 >> -rw------- 1 postgres postgres 32137216 Jul 8 18:35 486095 >> -rw------- 1 postgres postgres 37240832 Jul 8 18:57 494286 >> -rw------- 1 postgres postgres 1073741824 Jul 8 19:02 502478 >> -rw------- 1 postgres postgres 1073741824 Jul 8 19:03 502478.1 >> -rw------- 1 postgres postgres 747577344 Jul 8 19:03 502478.2 >> >> I can't find what is using it: > > These files don't get cleaned up after a crash, so they may well be > leftovers you can remove. Thanks Laurenz. I was looking at the source for "alter table set tablespace" yesterday trying to work out what is supposed to happen. There is a comment at tablecmds.c line 3989: "Thanks to the magic of MVCC, an error anywhere along the way rolls back the whole operation; we don't have to do anything special to clean up." But I guess that creating an entirely new file on a different filesystem is an exception to that. > Use \dt+ and \di+ to determine if the table or any of its indexes > actually resides in the new tablespace. Don't forget the TOAST table. "select distinct(reltablespace) from pg_class" gives only pg_global and null. Is that including TOAST tables etc.? > If they are all still in the original tablespace as they should be > on account of the transactional guarantees, go ahead and manually > remove the files. My plan is to wait for a couple of days to see if there are any other opinions here, and then do that. Thanks again, Phil.
"Phil Endecott" <spam_from_pgsql_list@chezphil.org> writes:
> Thanks Laurenz. I was looking at the source for "alter table set
> tablespace" yesterday trying to work out what is supposed to happen.
> There is a comment at tablecmds.c line 3989: "Thanks to the magic of
> MVCC, an error anywhere along the way rolls back the whole operation;
> we don't have to do anything special to clean up." But I guess that
> creating an entirely new file on a different filesystem is an
> exception to that.
No, but PANIC'ing during commit is :-(. Ordinarily the files created by a
failed transaction would be removed during transaction cleanup, but we
did not reach that code. So these were left behind, but the table's
original files in the original tablespace should be undamaged.
regards, tom lane
Tom Lane wrote: > "Phil Endecott" <spam_from_pgsql_list@chezphil.org> writes: >> Thanks Laurenz. I was looking at the source for "alter table set >> tablespace" yesterday trying to work out what is supposed to happen. >> There is a comment at tablecmds.c line 3989: "Thanks to the magic of >> MVCC, an error anywhere along the way rolls back the whole operation; >> we don't have to do anything special to clean up." But I guess that >> creating an entirely new file on a different filesystem is an >> exception to that. > > No, but PANIC'ing during commit is :-(. Ordinarily the files created by a > failed transaction would be removed during transaction cleanup, but we > did not reach that code. So these were left behind, but the table's > original files in the original tablespace should be undamaged. OK, I've removed them - fingers crossed! Thanks to everyone who replied for your advice. Regards, Phil.