pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedIssue in tablebackup
9+ messages / 7 participants
[nested] [flat]
* Issue in tablebackup
@ 2020-07-27 09:13 srini ram <seenuram777@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: srini ram @ 2020-07-27 09:13 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi Experts,
I am facing below error while taking a table back.
producation_db=# select * into tblconfmresult27072020 from tblconfmresult;
ERROR: missing chunk number 0 for toast value 4543659 in pg_toast_24175
producation_db =#
kindly assist me in taking the backup of this error table from my
production database.
I tried table vacuum and re-index of this issue table but still, it was not
helping to resolve this issue.
*Thanks&Regards,*
*Srinivasan R*
*Ph.,:9035911976*
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Issue in tablebackup
@ 2020-07-27 15:28 Moin Akther <moindba@hotmail.com>
parent: srini ram <seenuram777@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Moin Akther @ 2020-07-27 15:28 UTC (permalink / raw)
To: srini ram <seenuram777@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi Srini,
Try below steps
1. Take dump of pg_statistic.
2. Delete from pg_stattistic;
3. Analyze the <<table_name>>;
Thanks and Best Regards,
Moin Akther
From: srini ram <seenuram777@gmail.com>
Date: Monday, July 27, 2020 at 2:43 PM
To: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Subject: Issue in tablebackup
Hi Experts,
I am facing below error while taking a table back.
producation_db=# select * into tblconfmresult27072020 from tblconfmresult;
ERROR: missing chunk number 0 for toast value 4543659 in pg_toast_24175
producation_db =#
kindly assist me in taking the backup of this error table from my production database.
I tried table vacuum and re-index of this issue table but still, it was not helping to resolve this issue.
Thanks&Regards,
Srinivasan R
Ph.,:9035911976
^ permalink raw reply [nested|flat] 9+ messages in thread
* explosive WAL growth
@ 2024-02-27 18:09 Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 3 replies; 9+ messages in thread
From: Scott Ribe @ 2024-02-27 18:09 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Something is causing our WAL to grow 160GB/hour *faster* than archiving. (Archiving appears to be working normally.) This started in the past couple of days.
I am having some trouble finding the cause of this. I am looking at pg_stat_statements, # calls, time, shared blocks written. I am also looking at recent client app deployments.
My next step might be to use something like pg_waldump to see what's in this WAL.
Any suggestions?
--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: explosive WAL growth
@ 2024-02-27 18:22 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
2 siblings, 1 reply; 9+ messages in thread
From: Alvaro Herrera @ 2024-02-27 18:22 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On 2024-Feb-27, Scott Ribe wrote:
> Something is causing our WAL to grow 160GB/hour *faster* than archiving. (Archiving appears to be working normally.) This started in the past couple of days.
>
> I am having some trouble finding the cause of this. I am looking at pg_stat_statements, # calls, time, shared blocks written. I am also looking at recent client app deployments.
It could be autovacuum processing large tables that haven't been
processed in a long time, in emergency mode. This happens if you turn
it off and do nothing to substitute it.
Some backup tools implement parallel archiving of WAL files, which helps
you deal with massive accumulation of them.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: explosive WAL growth
@ 2024-02-27 18:22 Lucio Chiessi <lucio.chiessi@trustly.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
2 siblings, 0 replies; 9+ messages in thread
From: Lucio Chiessi @ 2024-02-27 18:22 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Has any autovacuum, vacuum, or reindex occurred?
These operations generate lots of WAL depending on the size of the data
related.
*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/;
On Tue, Feb 27, 2024 at 3:10 PM Scott Ribe <scott_ribe@elevated-dev.com>
wrote:
> Something is causing our WAL to grow 160GB/hour *faster* than archiving.
> (Archiving appears to be working normally.) This started in the past couple
> of days.
>
> I am having some trouble finding the cause of this. I am looking at
> pg_stat_statements, # calls, time, shared blocks written. I am also looking
> at recent client app deployments.
>
> My next step might be to use something like pg_waldump to see what's in
> this WAL.
>
> Any suggestions?
>
> --
> Scott Ribe
> scott_ribe@elevated-dev.com
> https://www.linkedin.com/in/scottribe/
>
>
>
>
>
>
--
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] 9+ messages in thread
* Re: explosive WAL growth
@ 2024-02-27 18:48 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 9+ messages in thread
From: Scott Ribe @ 2024-02-27 18:48 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Found it. Kubernetes cronjob task.
> On Feb 27, 2024, at 11:22 AM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>
> It could be autovacuum processing large tables that haven't been
> processed in a long time, in emergency mode. This happens if you turn
> it off and do nothing to substitute it.
We have autovacuum on, and pretty well tuned.
> Some backup tools implement parallel archiving of WAL files, which helps
> you deal with massive accumulation of them.
We'll get that in an upgrade we're getting ready to deploy. Ironically, it might have just hidden this problem ;-)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: explosive WAL growth
@ 2024-02-27 18:58 Karthik Yellapragada <karthik.yellapragada@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 9+ messages in thread
From: Karthik Yellapragada @ 2024-02-27 18:58 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Just out of curiosity, what was the job doing?
On Tue, Feb 27, 2024 at 10:49 AM Scott Ribe <scott_ribe@elevated-dev.com>
wrote:
> Found it. Kubernetes cronjob task.
>
> > On Feb 27, 2024, at 11:22 AM, Alvaro Herrera <alvherre@alvh.no-ip.org>
> wrote:
> >
> > It could be autovacuum processing large tables that haven't been
> > processed in a long time, in emergency mode. This happens if you turn
> > it off and do nothing to substitute it.
>
> We have autovacuum on, and pretty well tuned.
>
> > Some backup tools implement parallel archiving of WAL files, which helps
> > you deal with massive accumulation of them.
>
> We'll get that in an upgrade we're getting ready to deploy. Ironically, it
> might have just hidden this problem ;-)
>
> --
Thanks & Regards
Karthik Yellapragada
+1 860 830 5235
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: explosive WAL growth
@ 2024-02-27 19:19 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Karthik Yellapragada <karthik.yellapragada@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Scott Ribe @ 2024-02-27 19:19 UTC (permalink / raw)
To: Karthik Yellapragada <karthik.yellapragada@gmail.com>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Feb 27, 2024, at 11:58 AM, Karthik Yellapragada <karthik.yellapragada@gmail.com> wrote:
>
> Just out of curiosity, what was the job doing?
Large batch upsert. But something is wrong for it to be generating WAL that quickly. Having identified it, now we can drill down to see what's wrong with it.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: explosive WAL growth
@ 2024-02-27 19:50 vignesh kumar <vigneshkumar.venugopal@outlook.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
2 siblings, 0 replies; 9+ messages in thread
From: vignesh kumar @ 2024-02-27 19:50 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Check the Wal_keep_size or wal_keep_segments if they are too huge occupying the wal space.
Take a waldump and see what's really going on. Usually the blob will have wals split into multiple chunks to accomodate the filemaps.
Also use slot based replication so that primary need not maintain all the wals .. instead it maintains only wals required for the slot to have it replicated.
Check the min wal size and max wal size..
If none of them helps.. your application is shooting up load.
Get Outlook for Android<https://aka.ms/AAb9ysg;
________________________________
From: Scott Ribe <scott_ribe@elevated-dev.com>
Sent: Tuesday, February 27, 2024 11:39:58 PM
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: explosive WAL growth
Something is causing our WAL to grow 160GB/hour *faster* than archiving. (Archiving appears to be working normally.) This started in the past couple of days.
I am having some trouble finding the cause of this. I am looking at pg_stat_statements, # calls, time, shared blocks written. I am also looking at recent client app deployments.
My next step might be to use something like pg_waldump to see what's in this WAL.
Any suggestions?
--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2024-02-27 19:50 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-07-27 09:13 Issue in tablebackup srini ram <seenuram777@gmail.com>
2020-07-27 15:28 ` Moin Akther <moindba@hotmail.com>
2024-02-27 18:09 explosive WAL growth Scott Ribe <scott_ribe@elevated-dev.com>
2024-02-27 18:22 ` Re: explosive WAL growth Alvaro Herrera <alvherre@alvh.no-ip.org>
2024-02-27 18:48 ` Re: explosive WAL growth Scott Ribe <scott_ribe@elevated-dev.com>
2024-02-27 18:58 ` Re: explosive WAL growth Karthik Yellapragada <karthik.yellapragada@gmail.com>
2024-02-27 19:19 ` Re: explosive WAL growth Scott Ribe <scott_ribe@elevated-dev.com>
2024-02-27 18:22 ` Re: explosive WAL growth Lucio Chiessi <lucio.chiessi@trustly.com>
2024-02-27 19:50 ` Re: explosive WAL growth vignesh kumar <vigneshkumar.venugopal@outlook.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