On 2023-03-16 20:24:49 +0100, Laurenz Albe wrote:
> On Thu, 2023-03-16 at 13:20 +0000, Dolan, Sean wrote:
> > I messed up and confused issues. The error is :
> > ERROR: Could not extend pg_tblspc/16555/PG_13_20200/xxxx/xxxx No space left on device
> > HINT: Check free disk space
> >
> > So the schema is "full" and the offender is this one table.
> > I can't TRUNCATE as there needs to be space to perform the action.
> > Is there a way to see if there is a transaction on that table like you allude to?
>
> If you don't have enough space to run TRUNCATE, and you don't feel like
> extending the disk space, DROP TABLE would be a convenient alternative.
If you need to get just a little bit free space, you may be able to
reduce the reserved space on the file system. For example, on ext4 there
are typically 5 % reserved for root, so by reducing that to 2 % you get
about 3 % of extra space:
kitty:~/tmp 0:26 :-) 19% dd if=/dev/zero of=fill bs=1024k
dd: error writing 'fill': No space left on device
kitty:~/tmp 0:27 :-( 20% df -h .
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/kitty--vg-home 4.8G 4.5G 0 100% /home
kitty:~/tmp 0:27 :-) 21% echo test > small
echo: write error: no space left on device
kitty# tune2fs -m 2 /dev/kitty-vg/home
tune2fs 1.46.2 (28-Feb-2021)
Setting reserved blocks percentage to 2% (25784 blocks)
kitty:~/tmp 0:27 :-( 22% df -h .
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/kitty--vg-home 4.8G 4.5G 152M 97% /home
kitty:~/tmp 0:29 :-) 23% echo test > small
kitty:~/tmp 0:29 :-) 24% cat small
test
(You should restore the reserved space to the default afterwards. The
BSD file system and its descendants (like ext4) don't like getting
completely full.)
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"