pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedRE: EXTERNAL: Re: "No Free extents", table using all allocated space but no rows!
3+ messages / 3 participants
[nested] [flat]
* RE: EXTERNAL: Re: "No Free extents", table using all allocated space but no rows!
@ 2023-03-16 13:20 Dolan, Sean <sean.dolan@lmco.com>
0 siblings, 1 reply; 3+ messages in thread
From: Dolan, Sean @ 2023-03-16 13:20 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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?
-----Original Message-----
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Wednesday, March 15, 2023 11:45 PM
To: Dolan, Sean (US N-ISYS Technologies Inc.) <sean.dolan@lmco.com>; pgsql-general@lists.postgresql.org
Subject: EXTERNAL: Re: "No Free extents", table using all allocated space but no rows!
On Thu, 2023-03-16 at 01:58 +0000, Dolan, Sean wrote:
> Environment: PostGres 13 on RedHat 7.9.
>
> I am using logical replication (publisher/subscriber) between two
> databases and there are times where one of our schemas gets to 100% of allocated space (No Free Extents).
> I went into the schema and did a \dt+ to see the amount of size being
> used and I could see one of the tables somehow shows 16GB, essentially the amount of allocated size.
> Wanting to see what is in that table, I did a simple select * from the
> table and it returns no rows. Doing a count(*) also returns 0 rows.
>
> How can the table be using all that space but there is nothing “in” the table?
> I don’t care about the data (as I am testing) so I can drop and recreate that one table.
> \dt+ would then show 0 bytes. Later, I will then create a
> subscription and then I will get a No Free Extents error again and again the table has filled up.
>
> What can I look for?
I don't think that there is an error message "no free extents".
It can easily happen that a table is large, but SELECT count(*) returns 0.
That would mean that either the table is empty and VACUUM truncation didn't work, or that the table contains tuples that are not visible to your user, either because VACUUM didn't process the table yet, or because your snapshot is too old to see the data, or because the transaction that created the rows is still open.
If you don't care about the data, your easiest option is to TRUNCATE the table.
If TRUNCATE is blocked, kill all transactions that block it.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: EXTERNAL: Re: "No Free extents", table using all allocated space but no rows!
@ 2023-03-16 19:24 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Dolan, Sean <sean.dolan@lmco.com>
0 siblings, 1 reply; 3+ messages in thread
From: Laurenz Albe @ 2023-03-16 19:24 UTC (permalink / raw)
To: Dolan, Sean <sean.dolan@lmco.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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?
Ah, that's different.
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.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: EXTERNAL: Re: "No Free extents", table using all allocated space but no rows!
@ 2023-03-17 23:37 Peter J. Holzer <hjp-pgsql@hjp.at>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 3+ messages in thread
From: Peter J. Holzer @ 2023-03-17 23:37 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-03-17 23:37 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-16 13:20 RE: EXTERNAL: Re: "No Free extents", table using all allocated space but no rows! Dolan, Sean <sean.dolan@lmco.com>
2023-03-16 19:24 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-03-17 23:37 ` Peter J. Holzer <hjp-pgsql@hjp.at>
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