Date: Wed, 28 Feb 2024 12:16:43 +0100 (CET)
From: Dimitrios Apostolou <jimis@gmx.net>
To: pgsql-general@lists.postgresql.org
Subject: Orphan files filling root partition after crash
Message-ID: <d15a32be-915e-3cd8-4622-5a052d621f13@gmx.net>
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset=US-ASCII
UI-OutboundReport: notjunk:1;M01:P0:PQkfP0cdIY0=;OjLodXSqeL6x8RaSCcsHVqrg/9e
 M/QeOJwlnt6ybpujRA4NZ5AsaZUvQDA8DLlHj2+EYZeav5otKdUFaxqMLZ89CX6eiTY1ZLkLw
 O9hF6iTo0QmcPWOIzS3uB6ZEuEIHb7iwfCMfjttfshMg6NiPjSAhYnfZpEEJJpfGpQgNakkfo
 a2w/S99hRqv2X3NJkzVKQB8OQAwHF9S2xs514O1USligjlBPkrZ0oNaCbgL0vg+WKaUkWAIY8
 msgnY+hWkFjqeV5KdoZRDpPRR/scfK852QKE3+yMHCuTtRmwNAQARIHTpcIOeIjLHjLLM/3Rp
 g+lukkfVM46LkfcLtOyHxDntonhQdf3xzzgquDT1MWDyxC4ZNSu0ynTHEDFd5QkxkanY3Vlde
 kB/ayYAeoo1PQ2v3fJWvIsN3Fm5kznElIZ1R8UZVnmpPRVSI3xM1ggljLrjLxZVcS5sbpMSw9
 uLpkWdHxXq9cHILjax5Qk/oiqOJO6MhBhmIM2k5ZbPAPY/aHMTlGUq0OnaNm0La18b17NwAby
 DPoA/Ah7DS+THXGJ4tCi7nQflRQxo45ELvYdqZ8nyTMDavfsEp+k9dbL/5/WwEB7+dMoBeCzP
 uK2LD9M6TZX3RffvbtYC6EsFGrbAD/eKEmX7MlmbuRpddTHcVpFfHvyG6flmSmEW7wTMA/eo7
 LUduNemnWUjSHC56D++ae/io5TR8hF18gu6zaNshoUsQVgyimz3hRlHE18Ilr6gJoMqI1teZf
 5pM/wkXZ+QLow60ASuDYj6V67QSjfHIaQXHZQ7vcdNfhZo5rAqXbP4SHyGaakQTJCuKWT8Lq1
 mMisOVlkJg97fLtkQD8k1KHWpQhc6MZAuqZIw0Jx+6LGs=
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/d15a32be-915e-3cd8-4622-5a052d621f13%40gmx.net>
Precedence: bulk

Hello list,

yesterday I was doing:

ALTER TABLE x ADD UNIQUE ... , ADD PRIMARY KEY ...;

The table is almost a billion rows long but lies in its own TABLESPACE
that has plenty of space.  But apparently the ALTER TABLE command is
writing a lot to the default tablespace (not the temp_tablespace, that is
already moved to a different partition).

That quickly filled up the 50GB free space in my root partition:


20:18:04.222 UTC [94144] PANIC:  could not write to file "pg_wal/xlogtemp.=
94144": No space left on device
[...]
20:19:11.578 UTC [94140] LOG:  WAL writer process (PID 94144) was terminat=
ed by signal 6: Aborted
20:19:11.578 UTC [94140] LOG:  terminating any other active server process=
es


After postgresql crashed and restarted, the disk space in the root
partition was still not freed! I believe this is because of "orphaned
files" as discussed in mailing list thread [1].

[1] https://www.postgresql.org/message-id/CAN-RpxDBA7HbTsJPq4t4VznmRFJkssP=
2SNEMuG%3DoNJ%2B%3DsxLQew%40mail.gmail.com

I ended up doing some risky actions to remediate the problem: Find the
filenames that have no identically named "oid" in pg_class, and delete
(move to backup) the biggest ones while the database is stopped.
Fortunately the database started up fine after that!

So what is the moral of the story? How to guard against this?

Why did the database write so much to the default tablespace, even when
both the table and the temp tablespace are elsewhere?  Also should one
always keep the default tablespace away from the wal partition? (I guess
it would have helped to avoid the crash, but the ALTER TABLE command would
have still run out of space, so I'm not sure if the orphan files would
have been avoided)?

Needless to say, I would have hoped the database cleaned-up after itself
even after an uncontrolled crash, or that it provided some tools for the
job. (I tried VACUUM FULL on the table, but the orphaned files did not go
away).

My postgresql version is 16.2 installed on Ubuntu.

Thank you,
Dimitris