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