pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Peter J. Holzer <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: EXTERNAL: Re: "No Free extents",  table using all allocated space but no rows!
Date: Sat, 18 Mar 2023 00:37:10 +0100
Message-ID: <20230317233710.em2fpiwom7s35cel@hjp.at> (raw)
In-Reply-To: <47d0b525ad3ba570b4100e7b8d2a2b002ceba232.camel@cybertec.at>
References: <0eda9b7d13e34cc786d30c602510f3e4@lmco.com>
	<c78803e2308015fcbedd36b4056f4cfeeb5f6816.camel@cybertec.at>
	<6eec8fc3dfa743ff9b19d8ce0e87763d@lmco.com>
	<47d0b525ad3ba570b4100e7b8d2a2b002ceba232.camel@cybertec.at>

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!"

Attachments:

  signature.asc (application/pgp-signature, 832B) download

view thread (3+ messages)

Message-ID: <20230317233710.em2fpiwom7s35cel@hjp.at>
Permalink:  ../20230317233710.em2fpiwom7s35cel@hjp.at/
Also on:    postgresql.org/message-id/20230317233710.em2fpiwom7s35cel@hjp.at

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: hjp-pgsql@hjp.at
  Subject: Re: EXTERNAL: Re: "No Free extents",  table using all allocated space but no rows!
  In-Reply-To: <20230317233710.em2fpiwom7s35cel@hjp.at>
  References: <0eda9b7d13e34cc786d30c602510f3e4@lmco.com> <c78803e2308015fcbedd36b4056f4cfeeb5f6816.camel@cybertec.at> <6eec8fc3dfa743ff9b19d8ce0e87763d@lmco.com> <47d0b525ad3ba570b4100e7b8d2a2b002ceba232.camel@cybertec.at> <20230317233710.em2fpiwom7s35cel@hjp.at>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox