pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRDS No free space
15+ messages / 10 participants
[nested] [flat]
* RDS No free space
@ 2023-05-21 17:15 Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:23 ` Re: RDS No free space Tom Lane <tgl@sss.pgh.pa.us>
2023-05-22 12:49 ` Re: RDS No free space Wilson Coelho <wilson.coelho@tecnisys.com.br>
2023-05-22 19:07 ` Re: RDS No free space Jeff Janes <jeff.janes@gmail.com>
0 siblings, 3 replies; 15+ messages in thread
From: Wells Oliver @ 2023-05-21 17:15 UTC (permalink / raw)
To: pgsql-admin
I am seeing a lot of this morning:
ERROR: could not extend file "base/16411/616989679.62": No space left on
devi2023-05-21 17:11:45 UTC::@:[386]:LOG: all server processes terminated;
reinitializing
2023-05-21 17:04:02 UTC:172.31.21.22(55060):woliver@db:[31430]:ERROR: could
not extend file "base/16411/616989585.45": No space left on device
Etc
However, when I can connect, the DB is well below the provisioned space.
I am trying to figure this out but not sure where to turn yet.
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: RDS No free space
2023-05-21 17:15 RDS No free space Wells Oliver <wells.oliver@gmail.com>
@ 2023-05-21 17:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-05-21 17:37 ` Re: RDS No free space Wells Oliver <wells.oliver@gmail.com>
2 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2023-05-21 17:23 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
Wells Oliver <wells.oliver@gmail.com> writes:
> I am seeing a lot of this morning:
> ERROR: could not extend file "base/16411/616989679.62": No space left on
> devi2023-05-21 17:11:45 UTC::@:[386]:LOG: all server processes terminated;
> reinitializing
*Something* is preventing PG from using more disk space. Check ulimit,
cgroups, container limits, etc.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: RDS No free space
2023-05-21 17:15 RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:23 ` Re: RDS No free space Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-05-21 17:37 ` Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:42 ` Re: RDS No free space Jim Mlodgenski <jimmy76@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Wells Oliver @ 2023-05-21 17:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
So we run on RDS, and we clearly used up all of our provisioned storage.
However, I am baffled, and while I am emailing our AWS support, I wondered
if this list might point me in some direction too.
Our provisioned storage was 15TB. The size of our database -- shown
in pg_database -- is only 6TB. What in the world could be using that
remaining space? I am at a loss, that's a _ton_ of space being used up. Is
it some temporary allocation during script execution (seems ginormous,
impossible)? It it some WAL log thing?
I know this is part AWS RDS and part PG but it's difficult to track down.
Welcome to any ideas to dig into.
On Sun, May 21, 2023 at 10:23 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Wells Oliver <wells.oliver@gmail.com> writes:
> > I am seeing a lot of this morning:
> > ERROR: could not extend file "base/16411/616989679.62": No space left on
> > devi2023-05-21 17:11:45 UTC::@:[386]:LOG: all server processes
> terminated;
> > reinitializing
>
> *Something* is preventing PG from using more disk space. Check ulimit,
> cgroups, container limits, etc.
>
> regards, tom lane
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: RDS No free space
2023-05-21 17:15 RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:23 ` Re: RDS No free space Tom Lane <tgl@sss.pgh.pa.us>
2023-05-21 17:37 ` Re: RDS No free space Wells Oliver <wells.oliver@gmail.com>
@ 2023-05-21 17:42 ` Jim Mlodgenski <jimmy76@gmail.com>
2023-05-21 17:47 ` Re: RDS No free space Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Jim Mlodgenski @ 2023-05-21 17:42 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
On Sun, May 21, 2023 at 1:38 PM Wells Oliver <wells.oliver@gmail.com> wrote:
> So we run on RDS, and we clearly used up all of our provisioned storage.
> However, I am baffled, and while I am emailing our AWS support, I wondered
> if this list might point me in some direction too.
>
> Our provisioned storage was 15TB. The size of our database -- shown
> in pg_database -- is only 6TB. What in the world could be using that
> remaining space? I am at a loss, that's a _ton_ of space being used up. Is
> it some temporary allocation during script execution (seems ginormous,
> impossible)? It it some WAL log thing?
>
A fairly common cause of this is orphan replication slots so WAL files are
retained. Check
to see if there is an inactive slot that may be preventing the files to be
removed.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: RDS No free space
2023-05-21 17:15 RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:23 ` Re: RDS No free space Tom Lane <tgl@sss.pgh.pa.us>
2023-05-21 17:37 ` Re: RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:42 ` Re: RDS No free space Jim Mlodgenski <jimmy76@gmail.com>
@ 2023-05-21 17:47 ` Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:52 ` Re: RDS No free space Lucio Chiessi <lucio.chiessi@trustly.com>
2023-05-21 17:59 ` RE: RDS No free space Dustin Jantz <djantz@frontporch.com>
0 siblings, 2 replies; 15+ messages in thread
From: Wells Oliver @ 2023-05-21 17:47 UTC (permalink / raw)
To: Jim Mlodgenski <jimmy76@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
I'm not too familiar with that. Can you point me in the direction of some
config settings and maybe queries to execute?
On Sun, May 21, 2023 at 10:43 AM Jim Mlodgenski <jimmy76@gmail.com> wrote:
>
>
> On Sun, May 21, 2023 at 1:38 PM Wells Oliver <wells.oliver@gmail.com>
> wrote:
>
>> So we run on RDS, and we clearly used up all of our provisioned storage.
>> However, I am baffled, and while I am emailing our AWS support, I wondered
>> if this list might point me in some direction too.
>>
>> Our provisioned storage was 15TB. The size of our database -- shown
>> in pg_database -- is only 6TB. What in the world could be using that
>> remaining space? I am at a loss, that's a _ton_ of space being used up. Is
>> it some temporary allocation during script execution (seems ginormous,
>> impossible)? It it some WAL log thing?
>>
>
> A fairly common cause of this is orphan replication slots so WAL files are
> retained. Check
> to see if there is an inactive slot that may be preventing the files to be
> removed.
>
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: RDS No free space
2023-05-21 17:15 RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:23 ` Re: RDS No free space Tom Lane <tgl@sss.pgh.pa.us>
2023-05-21 17:37 ` Re: RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:42 ` Re: RDS No free space Jim Mlodgenski <jimmy76@gmail.com>
2023-05-21 17:47 ` Re: RDS No free space Wells Oliver <wells.oliver@gmail.com>
@ 2023-05-21 17:52 ` Lucio Chiessi <lucio.chiessi@trustly.com>
2023-05-21 17:58 ` Re: RDS No free space Saikat Banerjee <mr.banerjeesaikat@gmail.com>
2023-05-21 19:57 ` Re: RDS No free space Ron <ronljohnsonjr@gmail.com>
1 sibling, 2 replies; 15+ messages in thread
From: Lucio Chiessi @ 2023-05-21 17:52 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
You can check if you having some strong queries that were generating a lot
of temp files, that will can eat all off free space.
On Sun, 21 May 2023 at 14:48 Wells Oliver <wells.oliver@gmail.com> wrote:
> I'm not too familiar with that. Can you point me in the direction of some
> config settings and maybe queries to execute?
>
> On Sun, May 21, 2023 at 10:43 AM Jim Mlodgenski <jimmy76@gmail.com> wrote:
>
>>
>>
>> On Sun, May 21, 2023 at 1:38 PM Wells Oliver <wells.oliver@gmail.com>
>> wrote:
>>
>>> So we run on RDS, and we clearly used up all of our provisioned storage.
>>> However, I am baffled, and while I am emailing our AWS support, I wondered
>>> if this list might point me in some direction too.
>>>
>>> Our provisioned storage was 15TB. The size of our database -- shown
>>> in pg_database -- is only 6TB. What in the world could be using that
>>> remaining space? I am at a loss, that's a _ton_ of space being used up. Is
>>> it some temporary allocation during script execution (seems ginormous,
>>> impossible)? It it some WAL log thing?
>>>
>>
>> A fairly common cause of this is orphan replication slots so WAL files
>> are retained. Check
>> to see if there is an inactive slot that may be preventing the files to
>> be removed.
>>
>>
>
> --
> Wells Oliver
> wells.oliver@gmail.com <wellsoliver@gmail.com>
>
--
*Lucio Chiessi*
Senior Database Administrator
Trustly, Inc.
M: +55 27 996360276
<https://www.linkedin.com/company/trustly/;
<https://www.facebook.com/trustly; <https://twitter.com/Trustly;
<https://www.linkedin.com/company/trustly/;
<https://www.facebook.com/trustly; <https://twitter.com/Trustly;
<https://www.trustly.net/;
PayWith*MyBank®* is now part of *Trustly*
--
Please read our privacy policy here
<https://www.trustly.net/about-us/privacy-policy; on how we process your
personal data in accordance with the General Data Protection Regulation
(EU) 2016/679 (the “GDPR”) and other applicable data protection legislation
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: RDS No free space
2023-05-21 17:15 RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:23 ` Re: RDS No free space Tom Lane <tgl@sss.pgh.pa.us>
2023-05-21 17:37 ` Re: RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:42 ` Re: RDS No free space Jim Mlodgenski <jimmy76@gmail.com>
2023-05-21 17:47 ` Re: RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:52 ` Re: RDS No free space Lucio Chiessi <lucio.chiessi@trustly.com>
@ 2023-05-21 17:58 ` Saikat Banerjee <mr.banerjeesaikat@gmail.com>
1 sibling, 0 replies; 15+ messages in thread
From: Saikat Banerjee @ 2023-05-21 17:58 UTC (permalink / raw)
To: Lucio Chiessi <lucio.chiessi@trustly.com>; +Cc: pgsql-admin
This might help-
[+] https://repost.aws/knowledge-center/diskfull-error-rds-postgresql
On Sun, May 21, 2023 at 1:52 PM Lucio Chiessi <lucio.chiessi@trustly.com>
wrote:
> You can check if you having some strong queries that were generating a lot
> of temp files, that will can eat all off free space.
>
> On Sun, 21 May 2023 at 14:48 Wells Oliver <wells.oliver@gmail.com> wrote:
>
>> I'm not too familiar with that. Can you point me in the direction of some
>> config settings and maybe queries to execute?
>>
>> On Sun, May 21, 2023 at 10:43 AM Jim Mlodgenski <jimmy76@gmail.com>
>> wrote:
>>
>>>
>>>
>>> On Sun, May 21, 2023 at 1:38 PM Wells Oliver <wells.oliver@gmail.com>
>>> wrote:
>>>
>>>> So we run on RDS, and we clearly used up all of our provisioned
>>>> storage. However, I am baffled, and while I am emailing our AWS support, I
>>>> wondered if this list might point me in some direction too.
>>>>
>>>> Our provisioned storage was 15TB. The size of our database -- shown
>>>> in pg_database -- is only 6TB. What in the world could be using that
>>>> remaining space? I am at a loss, that's a _ton_ of space being used up. Is
>>>> it some temporary allocation during script execution (seems ginormous,
>>>> impossible)? It it some WAL log thing?
>>>>
>>>
>>> A fairly common cause of this is orphan replication slots so WAL files
>>> are retained. Check
>>> to see if there is an inactive slot that may be preventing the files to
>>> be removed.
>>>
>>>
>>
>> --
>> Wells Oliver
>> wells.oliver@gmail.com <wellsoliver@gmail.com>
>>
> --
>
> *Lucio Chiessi*
>
> Senior Database Administrator
>
> Trustly, Inc.
>
> M: +55 27 996360276
>
> <https://www.linkedin.com/company/trustly/;
> <https://www.facebook.com/trustly; <https://twitter.com/Trustly;
>
> <https://www.linkedin.com/company/trustly/;
> <https://www.facebook.com/trustly; <https://twitter.com/Trustly;
>
> <https://www.trustly.net/;
>
> PayWith*MyBank®* is now part of *Trustly*
>
> Please read our privacy policy *here
> <https://www.trustly.net/about-us/privacy-policy; *on how we process your
> personal data in accordance with the General Data Protection Regulation (EU)
> 2016/679 (the “GDPR”) and other applicable data protection legislation
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: RDS No free space
2023-05-21 17:15 RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:23 ` Re: RDS No free space Tom Lane <tgl@sss.pgh.pa.us>
2023-05-21 17:37 ` Re: RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:42 ` Re: RDS No free space Jim Mlodgenski <jimmy76@gmail.com>
2023-05-21 17:47 ` Re: RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:52 ` Re: RDS No free space Lucio Chiessi <lucio.chiessi@trustly.com>
@ 2023-05-21 19:57 ` Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 15+ messages in thread
From: Ron @ 2023-05-21 19:57 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 5/21/23 12:52, Lucio Chiessi wrote:
> You can check if you having some strong queries that were generating a lot
> of temp files, that will can eat all off free space.
That was my first thought.
>
> On Sun, 21 May 2023 at 14:48 Wells Oliver <wells.oliver@gmail.com> wrote:
>
> I'm not too familiar with that. Can you point me in the direction of
> some config settings and maybe queries to execute?
>
> On Sun, May 21, 2023 at 10:43 AM Jim Mlodgenski <jimmy76@gmail.com> wrote:
>
>
>
> On Sun, May 21, 2023 at 1:38 PM Wells Oliver
> <wells.oliver@gmail.com> wrote:
>
> So we run on RDS, and we clearly used up all of our
> provisioned storage. However, I am baffled, and while I am
> emailing our AWS support, I wondered if this list might point
> me in some direction too.
>
> Our provisioned storage was 15TB. The size of our database --
> shown in pg_database -- is only 6TB. What in the world could
> be using that remaining space? I am at a loss, that's a _ton_
> of space being used up. Is it some temporary allocation during
> script execution (seems ginormous, impossible)? It it some WAL
> log thing?
>
>
> A fairly common cause of this is orphan replication slots so WAL
> files are retained. Check
> to see if there is an inactive slot that may be preventing the
> files to be removed.
>
>
>
> --
> Wells Oliver
> wells.oliver@gmail.com <mailto:wellsoliver@gmail.com>
>
> --
>
> *Lucio Chiessi*
>
> Senior Database Administrator
>
> Trustly, Inc.
>
> M: +55 27 996360276
>
> <https://www.linkedin.com/company/trustly/;
> <https://www.facebook.com/trustly; <https://twitter.com/Trustly;
>
> <https://www.linkedin.com/company/trustly/><https://www.facebook.com/trustly><https://twi...;
>
> <https://www.trustly.net/;
>
> PayWith*MyBank^® * is now part of***Trustly*
>
>
> Please read our privacy policy *here
> <https://www.trustly.net/about-us/privacy-policy; *on how we process your
> personal data in accordancewiththeGeneral Data Protection Regulation(EU)
> 2016/679 (the “GDPR”) and other applicable data protection legislation
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 15+ messages in thread
* RE: RDS No free space
2023-05-21 17:15 RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:23 ` Re: RDS No free space Tom Lane <tgl@sss.pgh.pa.us>
2023-05-21 17:37 ` Re: RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:42 ` Re: RDS No free space Jim Mlodgenski <jimmy76@gmail.com>
2023-05-21 17:47 ` Re: RDS No free space Wells Oliver <wells.oliver@gmail.com>
@ 2023-05-21 17:59 ` Dustin Jantz <djantz@frontporch.com>
2023-05-21 18:14 ` Re: RDS No free space Wells Oliver <wells.oliver@gmail.com>
1 sibling, 1 reply; 15+ messages in thread
From: Dustin Jantz @ 2023-05-21 17:59 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; Jim Mlodgenski <jimmy76@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
To check the replication slots run the following query. The lag is how much the WAL file is holding on to data. If the slot is not active you can remove the slot and that will free up the space the WAL file is holding on to.
SELECT
rps.slot_name,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) pretty_replication_slot_lag,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) pretty_confirmed_lag,
rps.active slot_active,pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) as replication_slot_
lag,pg_wal_lsn_diff(pg_current_wal_lsn(),
confirmed_flush_lsn) as confirmed_lag,
rps.active_pid
FROM pg_replication_slots rps
Remove the inactive replication slot:
SELECT pg_drop_replication_slot('<slot_name>');
The only other time I had this issue was because of an incomplete vacuum full on a large table. This caused there to be orphaned files which took up a lot of space.
Kind regards,
Dustin Jantz
djantz@frontporch.com
From: Wells Oliver <wells.oliver@gmail.com>
Sent: Sunday, May 21, 2023 10:48 AM
To: Jim Mlodgenski <jimmy76@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: RDS No free space
I'm not too familiar with that. Can you point me in the direction of some config settings and maybe queries to execute?
On Sun, May 21, 2023 at 10:43 AM Jim Mlodgenski <jimmy76@gmail.com<mailto:jimmy76@gmail.com>> wrote:
On Sun, May 21, 2023 at 1:38 PM Wells Oliver <wells.oliver@gmail.com<mailto:wells.oliver@gmail.com>> wrote:
So we run on RDS, and we clearly used up all of our provisioned storage. However, I am baffled, and while I am emailing our AWS support, I wondered if this list might point me in some direction too.
Our provisioned storage was 15TB. The size of our database -- shown in pg_database -- is only 6TB. What in the world could be using that remaining space? I am at a loss, that's a _ton_ of space being used up. Is it some temporary allocation during script execution (seems ginormous, impossible)? It it some WAL log thing?
A fairly common cause of this is orphan replication slots so WAL files are retained. Check
to see if there is an inactive slot that may be preventing the files to be removed.
--
Wells Oliver
wells.oliver@gmail.com<mailto:wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: RDS No free space
2023-05-21 17:15 RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:23 ` Re: RDS No free space Tom Lane <tgl@sss.pgh.pa.us>
2023-05-21 17:37 ` Re: RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:42 ` Re: RDS No free space Jim Mlodgenski <jimmy76@gmail.com>
2023-05-21 17:47 ` Re: RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:59 ` RE: RDS No free space Dustin Jantz <djantz@frontporch.com>
@ 2023-05-21 18:14 ` Wells Oliver <wells.oliver@gmail.com>
2023-05-21 18:20 ` Re: RDS No free space Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Wells Oliver @ 2023-05-21 18:14 UTC (permalink / raw)
To: Dustin Jantz <djantz@frontporch.com>; +Cc: Jim Mlodgenski <jimmy76@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
Dustin, how did you come to see the incomplete vacuum and the orphaned
files?
On Sun, May 21, 2023 at 10:59 AM Dustin Jantz <djantz@frontporch.com> wrote:
> To check the replication slots run the following query. The lag is how
> much the WAL file is holding on to data. If the slot is not active you can
> remove the slot and that will free up the space the WAL file is holding on
> to.
>
> SELECT
>
> rps.slot_name,
>
> pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))
> pretty_replication_slot_lag,
>
> pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn))
> pretty_confirmed_lag,
>
> rps.active slot_active,pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
> as replication_slot_
>
> lag,pg_wal_lsn_diff(pg_current_wal_lsn(),
>
> confirmed_flush_lsn) as confirmed_lag,
>
> rps.active_pid
>
> FROM pg_replication_slots rps
>
>
>
>
>
> Remove the inactive replication slot:
>
>
>
> SELECT pg_drop_replication_slot('<slot_name>');
>
>
>
>
>
>
>
> The only other time I had this issue was because of an incomplete vacuum
> full on a large table. This caused there to be orphaned files which took up
> a lot of space.
>
>
>
>
>
>
>
> Kind regards,
>
>
>
>
>
> Dustin Jantz
>
> djantz@frontporch.com
>
>
>
> *From:* Wells Oliver <wells.oliver@gmail.com>
> *Sent:* Sunday, May 21, 2023 10:48 AM
> *To:* Jim Mlodgenski <jimmy76@gmail.com>
> *Cc:* Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin <
> pgsql-admin@postgresql.org>
> *Subject:* Re: RDS No free space
>
>
>
> I'm not too familiar with that. Can you point me in the direction of some
> config settings and maybe queries to execute?
>
>
>
> On Sun, May 21, 2023 at 10:43 AM Jim Mlodgenski <jimmy76@gmail.com> wrote:
>
>
>
>
>
> On Sun, May 21, 2023 at 1:38 PM Wells Oliver <wells.oliver@gmail.com>
> wrote:
>
> So we run on RDS, and we clearly used up all of our provisioned storage.
> However, I am baffled, and while I am emailing our AWS support, I wondered
> if this list might point me in some direction too.
>
>
>
> Our provisioned storage was 15TB. The size of our database -- shown
> in pg_database -- is only 6TB. What in the world could be using that
> remaining space? I am at a loss, that's a _ton_ of space being used up. Is
> it some temporary allocation during script execution (seems ginormous,
> impossible)? It it some WAL log thing?
>
>
>
> A fairly common cause of this is orphan replication slots so WAL files are
> retained. Check
>
> to see if there is an inactive slot that may be preventing the files to be
> removed.
>
>
>
>
>
>
> --
>
> Wells Oliver
> wells.oliver@gmail.com <wellsoliver@gmail.com>
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: RDS No free space
2023-05-21 17:15 RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:23 ` Re: RDS No free space Tom Lane <tgl@sss.pgh.pa.us>
2023-05-21 17:37 ` Re: RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:42 ` Re: RDS No free space Jim Mlodgenski <jimmy76@gmail.com>
2023-05-21 17:47 ` Re: RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:59 ` RE: RDS No free space Dustin Jantz <djantz@frontporch.com>
2023-05-21 18:14 ` Re: RDS No free space Wells Oliver <wells.oliver@gmail.com>
@ 2023-05-21 18:20 ` Wells Oliver <wells.oliver@gmail.com>
2023-05-21 18:42 ` Re: RDS No free space Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Wells Oliver @ 2023-05-21 18:20 UTC (permalink / raw)
To: Dustin Jantz <djantz@frontporch.com>; +Cc: Jim Mlodgenski <jimmy76@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
We've dug a little further and we feel somewhat confident that it's
retaining WAL logs forever. Our free storage starts to drop
steadily/precipitously when we enabled WAL. Could someone point me to some
settings/queries to dig a bit on that?
On Sun, May 21, 2023 at 11:14 AM Wells Oliver <wells.oliver@gmail.com>
wrote:
> Dustin, how did you come to see the incomplete vacuum and the orphaned
> files?
>
> On Sun, May 21, 2023 at 10:59 AM Dustin Jantz <djantz@frontporch.com>
> wrote:
>
>> To check the replication slots run the following query. The lag is how
>> much the WAL file is holding on to data. If the slot is not active you can
>> remove the slot and that will free up the space the WAL file is holding on
>> to.
>>
>> SELECT
>>
>> rps.slot_name,
>>
>> pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))
>> pretty_replication_slot_lag,
>>
>> pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),
>> confirmed_flush_lsn)) pretty_confirmed_lag,
>>
>> rps.active slot_active,pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
>> as replication_slot_
>>
>> lag,pg_wal_lsn_diff(pg_current_wal_lsn(),
>>
>> confirmed_flush_lsn) as confirmed_lag,
>>
>> rps.active_pid
>>
>> FROM pg_replication_slots rps
>>
>>
>>
>>
>>
>> Remove the inactive replication slot:
>>
>>
>>
>> SELECT pg_drop_replication_slot('<slot_name>');
>>
>>
>>
>>
>>
>>
>>
>> The only other time I had this issue was because of an incomplete vacuum
>> full on a large table. This caused there to be orphaned files which took up
>> a lot of space.
>>
>>
>>
>>
>>
>>
>>
>> Kind regards,
>>
>>
>>
>>
>>
>> Dustin Jantz
>>
>> djantz@frontporch.com
>>
>>
>>
>> *From:* Wells Oliver <wells.oliver@gmail.com>
>> *Sent:* Sunday, May 21, 2023 10:48 AM
>> *To:* Jim Mlodgenski <jimmy76@gmail.com>
>> *Cc:* Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin <
>> pgsql-admin@postgresql.org>
>> *Subject:* Re: RDS No free space
>>
>>
>>
>> I'm not too familiar with that. Can you point me in the direction of some
>> config settings and maybe queries to execute?
>>
>>
>>
>> On Sun, May 21, 2023 at 10:43 AM Jim Mlodgenski <jimmy76@gmail.com>
>> wrote:
>>
>>
>>
>>
>>
>> On Sun, May 21, 2023 at 1:38 PM Wells Oliver <wells.oliver@gmail.com>
>> wrote:
>>
>> So we run on RDS, and we clearly used up all of our provisioned storage.
>> However, I am baffled, and while I am emailing our AWS support, I wondered
>> if this list might point me in some direction too.
>>
>>
>>
>> Our provisioned storage was 15TB. The size of our database -- shown
>> in pg_database -- is only 6TB. What in the world could be using that
>> remaining space? I am at a loss, that's a _ton_ of space being used up. Is
>> it some temporary allocation during script execution (seems ginormous,
>> impossible)? It it some WAL log thing?
>>
>>
>>
>> A fairly common cause of this is orphan replication slots so WAL files
>> are retained. Check
>>
>> to see if there is an inactive slot that may be preventing the files to
>> be removed.
>>
>>
>>
>>
>>
>>
>> --
>>
>> Wells Oliver
>> wells.oliver@gmail.com <wellsoliver@gmail.com>
>>
>
>
> --
> Wells Oliver
> wells.oliver@gmail.com <wellsoliver@gmail.com>
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: RDS No free space
2023-05-21 17:15 RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:23 ` Re: RDS No free space Tom Lane <tgl@sss.pgh.pa.us>
2023-05-21 17:37 ` Re: RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:42 ` Re: RDS No free space Jim Mlodgenski <jimmy76@gmail.com>
2023-05-21 17:47 ` Re: RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:59 ` RE: RDS No free space Dustin Jantz <djantz@frontporch.com>
2023-05-21 18:14 ` Re: RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 18:20 ` Re: RDS No free space Wells Oliver <wells.oliver@gmail.com>
@ 2023-05-21 18:42 ` Wells Oliver <wells.oliver@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Wells Oliver @ 2023-05-21 18:42 UTC (permalink / raw)
To: Dustin Jantz <djantz@frontporch.com>; +Cc: Jim Mlodgenski <jimmy76@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
Hi all, we've identified the issue. We had a very laggy replication
instance a high volume of data in our replication slots, I'm not sure the
underlying PG number here, but the RDS metric is "OldestReplicationSlotLag"
and it was by terabytes (as the replication instance had crashed).
I appreciate the info and ideas here. Very grateful for this mailing list.
On Sun, May 21, 2023 at 11:20 AM Wells Oliver <wells.oliver@gmail.com>
wrote:
> We've dug a little further and we feel somewhat confident that it's
> retaining WAL logs forever. Our free storage starts to drop
> steadily/precipitously when we enabled WAL. Could someone point me to some
> settings/queries to dig a bit on that?
>
> On Sun, May 21, 2023 at 11:14 AM Wells Oliver <wells.oliver@gmail.com>
> wrote:
>
>> Dustin, how did you come to see the incomplete vacuum and the orphaned
>> files?
>>
>> On Sun, May 21, 2023 at 10:59 AM Dustin Jantz <djantz@frontporch.com>
>> wrote:
>>
>>> To check the replication slots run the following query. The lag is how
>>> much the WAL file is holding on to data. If the slot is not active you can
>>> remove the slot and that will free up the space the WAL file is holding on
>>> to.
>>>
>>> SELECT
>>>
>>> rps.slot_name,
>>>
>>> pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))
>>> pretty_replication_slot_lag,
>>>
>>> pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),
>>> confirmed_flush_lsn)) pretty_confirmed_lag,
>>>
>>> rps.active slot_active,pg_wal_lsn_diff(pg_current_wal_lsn(),
>>> restart_lsn) as replication_slot_
>>>
>>> lag,pg_wal_lsn_diff(pg_current_wal_lsn(),
>>>
>>> confirmed_flush_lsn) as confirmed_lag,
>>>
>>> rps.active_pid
>>>
>>> FROM pg_replication_slots rps
>>>
>>>
>>>
>>>
>>>
>>> Remove the inactive replication slot:
>>>
>>>
>>>
>>> SELECT pg_drop_replication_slot('<slot_name>');
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> The only other time I had this issue was because of an incomplete vacuum
>>> full on a large table. This caused there to be orphaned files which took up
>>> a lot of space.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> Kind regards,
>>>
>>>
>>>
>>>
>>>
>>> Dustin Jantz
>>>
>>> djantz@frontporch.com
>>>
>>>
>>>
>>> *From:* Wells Oliver <wells.oliver@gmail.com>
>>> *Sent:* Sunday, May 21, 2023 10:48 AM
>>> *To:* Jim Mlodgenski <jimmy76@gmail.com>
>>> *Cc:* Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin <
>>> pgsql-admin@postgresql.org>
>>> *Subject:* Re: RDS No free space
>>>
>>>
>>>
>>> I'm not too familiar with that. Can you point me in the direction of
>>> some config settings and maybe queries to execute?
>>>
>>>
>>>
>>> On Sun, May 21, 2023 at 10:43 AM Jim Mlodgenski <jimmy76@gmail.com>
>>> wrote:
>>>
>>>
>>>
>>>
>>>
>>> On Sun, May 21, 2023 at 1:38 PM Wells Oliver <wells.oliver@gmail.com>
>>> wrote:
>>>
>>> So we run on RDS, and we clearly used up all of our provisioned storage.
>>> However, I am baffled, and while I am emailing our AWS support, I wondered
>>> if this list might point me in some direction too.
>>>
>>>
>>>
>>> Our provisioned storage was 15TB. The size of our database -- shown
>>> in pg_database -- is only 6TB. What in the world could be using that
>>> remaining space? I am at a loss, that's a _ton_ of space being used up. Is
>>> it some temporary allocation during script execution (seems ginormous,
>>> impossible)? It it some WAL log thing?
>>>
>>>
>>>
>>> A fairly common cause of this is orphan replication slots so WAL files
>>> are retained. Check
>>>
>>> to see if there is an inactive slot that may be preventing the files to
>>> be removed.
>>>
>>>
>>>
>>>
>>>
>>>
>>> --
>>>
>>> Wells Oliver
>>> wells.oliver@gmail.com <wellsoliver@gmail.com>
>>>
>>
>>
>> --
>> Wells Oliver
>> wells.oliver@gmail.com <wellsoliver@gmail.com>
>>
>
>
> --
> Wells Oliver
> wells.oliver@gmail.com <wellsoliver@gmail.com>
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: RDS No free space
2023-05-21 17:15 RDS No free space Wells Oliver <wells.oliver@gmail.com>
@ 2023-05-22 12:49 ` Wilson Coelho <wilson.coelho@tecnisys.com.br>
2023-05-22 12:54 ` Re: RDS No free space Priancka Chatz <pc9926@gmail.com>
2 siblings, 1 reply; 15+ messages in thread
From: Wilson Coelho @ 2023-05-22 12:49 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
Hi Oliver
You need to check where are the postgres data directory and verify if
there is enough available space on it to the operation you trying to do
over the database.
It seems to be a problem related with a partition scheme and not the DB
per se.
Also take a look at the distribution of your data over the partitions.
If that file is on a partition with no space, try to resize or, move to
other partition
Regards
Wilson Coelho
---
Wilson Moraes Coelho
Especialista
Sia Trecho 08, lotes 245 / 255 / 265 ||
Tel.:+55 (61) 3039-9700 - (61) 99989-8932
71205-080 || Guará || Brasília, DF 0800-6020097
www.tecnisys.com.br [1]
Em 21/05/2023 14:15, Wells Oliver escreveu:
> I am seeing a lot of this morning:
>
> ERROR: could not extend file "base/16411/616989679.62": No space left on devi2023-05-21 17:11:45 UTC::@:[386]:LOG: all server processes terminated; reinitializing
>
> 2023-05-21 17:04:02 UTC:172.31.21.22(55060):woliver@db:[31430]:ERROR: could not extend file "base/16411/616989585.45": No space left on device
>
> Etc
>
> However, when I can connect, the DB is well below the provisioned space.
>
> I am trying to figure this out but not sure where to turn yet.
> --
>
> Wells Oliver
> wells.oliver@gmail.com
Links:
------
[1] http://www.tecnisys.com.br
Attachments:
[image/gif] f62593aa.gif (43B, ../../d2d7a8b5d8dcf6491b9e1d51d7b9229d@tecnisys.com.br/3-f62593aa.gif)
download | view image
[image/gif] 04237a95.gif (3.1K, ../../d2d7a8b5d8dcf6491b9e1d51d7b9229d@tecnisys.com.br/4-04237a95.gif)
download | view image
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: RDS No free space
2023-05-21 17:15 RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-22 12:49 ` Re: RDS No free space Wilson Coelho <wilson.coelho@tecnisys.com.br>
@ 2023-05-22 12:54 ` Priancka Chatz <pc9926@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Priancka Chatz @ 2023-05-22 12:54 UTC (permalink / raw)
To: Wilson Coelho <wilson.coelho@tecnisys.com.br>; +Cc: Wells Oliver <wells.oliver@gmail.com>; pgsql-admin
Hi Oliver,
I think for RDS Postgres space issue this is good guidance:
https://repost.aws/knowledge-center/diskfull-error-rds-postgresql .
Regards,
Priyanka
On Mon, May 22, 2023 at 2:49 PM Wilson Coelho <wilson.coelho@tecnisys.com.br>
wrote:
> Hi Oliver
>
> You need to check where are the postgres data directory and verify if
> there is enough available space on it to the operation you trying to do
> over the database.
> It seems to be a problem related with a partition scheme and not the DB
> per se.
> Also take a look at the distribution of your data over the partitions. If
> that file is on a partition with no space, try to resize or, move to other
> partition
>
> Regards
>
> Wilson Coelho
> ---
>
> *Wilson *Moraes* Coelho*
> Especialista
>
> [image: Logo Tecnisys]
>
> Sia Trecho 08, lotes 245 / 255 / 265 ||
>
> Tel.:+55 (61) 3039-9700 - (61) 99989-8932
>
> 71205-080 || Guará || Brasília, DF 0800-6020097
>
> www.tecnisys.com.br
>
>
> Em 21/05/2023 14:15, Wells Oliver escreveu:
>
> I am seeing a lot of this morning:
>
> ERROR: could not extend file "base/16411/616989679.62": No space left on
> devi2023-05-21 17:11:45 UTC::@:[386]:LOG: all server processes
> terminated; reinitializing
>
> 2023-05-21 17:04:02 UTC:172.31.21.22(55060):woliver@db:[31430]:ERROR:
> could not extend file "base/16411/616989585.45": No space left on device
>
> Etc
>
> However, when I can connect, the DB is well below the provisioned space.
>
> I am trying to figure this out but not sure where to turn yet.
>
> --
> Wells Oliver
> wells.oliver@gmail.com <wellsoliver@gmail.com>
>
>
Attachments:
[image/gif] f62593aa.gif (43B, ../../CANnOdgbnKnABeJgLCOzZUJZHfG0yMn0eSqhe=NUXXJODRm47vw@mail.gmail.com/3-f62593aa.gif)
download | view image
[image/gif] 04237a95.gif (3.1K, ../../CANnOdgbnKnABeJgLCOzZUJZHfG0yMn0eSqhe=NUXXJODRm47vw@mail.gmail.com/4-04237a95.gif)
download | view image
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: RDS No free space
2023-05-21 17:15 RDS No free space Wells Oliver <wells.oliver@gmail.com>
@ 2023-05-22 19:07 ` Jeff Janes <jeff.janes@gmail.com>
2 siblings, 0 replies; 15+ messages in thread
From: Jeff Janes @ 2023-05-22 19:07 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
On Sun, May 21, 2023 at 1:15 PM Wells Oliver <wells.oliver@gmail.com> wrote:
> I am seeing a lot of this morning:
>
> ERROR: could not extend file "base/16411/616989679.62": No space left on
> devi2023-05-21 17:11:45 UTC::@:[386]:LOG: all server processes
> terminated; reinitializing
>
> 2023-05-21 17:04:02 UTC:172.31.21.22(55060):woliver@db:[31430]:ERROR:
> could not extend file "base/16411/616989585.45": No space left on device
>
The 'all server processes terminated' message indicates that there has been
a PANIC, not merely an ERROR. Was there also an earlier PANIC message?
> Etc
>
> However, when I can connect, the DB is well below the provisioned space.
>
Do objects with those relfilenodes exist? What statements were executing
when the errors were thrown? (the statement text should be included in the
error message)
Cheers,
Jeff
>
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2023-05-22 19:07 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-21 17:15 RDS No free space Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-05-21 17:37 ` Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:42 ` Jim Mlodgenski <jimmy76@gmail.com>
2023-05-21 17:47 ` Wells Oliver <wells.oliver@gmail.com>
2023-05-21 17:52 ` Lucio Chiessi <lucio.chiessi@trustly.com>
2023-05-21 17:58 ` Saikat Banerjee <mr.banerjeesaikat@gmail.com>
2023-05-21 19:57 ` Ron <ronljohnsonjr@gmail.com>
2023-05-21 17:59 ` Dustin Jantz <djantz@frontporch.com>
2023-05-21 18:14 ` Wells Oliver <wells.oliver@gmail.com>
2023-05-21 18:20 ` Wells Oliver <wells.oliver@gmail.com>
2023-05-21 18:42 ` Wells Oliver <wells.oliver@gmail.com>
2023-05-22 12:49 ` Wilson Coelho <wilson.coelho@tecnisys.com.br>
2023-05-22 12:54 ` Priancka Chatz <pc9926@gmail.com>
2023-05-22 19:07 ` Jeff Janes <jeff.janes@gmail.com>
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