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.
