agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedFailover and vacuum
7+ messages / 3 participants
[nested] [flat]
* Failover and vacuum
@ 2025-03-27 17:40 Raj <rajeshkumar.dba09@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Raj @ 2025-03-27 17:40 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi
Have 2 nodes ( primary and standby postgres 15.6) in openshift kubernetes.
Patroni setup. 300gb data. No failover since last six months. Suddenly
after failover, there were lot of issues such as too many connections and
slowness.
Is it due to not analyze done in new node?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Failover and vacuum
@ 2025-03-29 03:25 Gurjeet Singh <gurjeet@singh.im>
parent: Raj <rajeshkumar.dba09@gmail.com>
1 sibling, 2 replies; 7+ messages in thread
From: Gurjeet Singh @ 2025-03-29 03:25 UTC (permalink / raw)
To: Raj <rajeshkumar.dba09@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Thu Mar 27, 2025 at 10:40 AM PDT, Raj wrote:
> Hi
>
> Have 2 nodes ( primary and standby postgres 15.6) in openshift kubernetes.
>
> Patroni setup. 300gb data. No failover since last six months. Suddenly
> after failover, there were lot of issues such as too many connections and
> slowness.
>
> Is it due to not analyze done in new node?
There isn't enough information in your message for anyone to take an
educated guess. If you see evidence of vacuum not being performed on
certain tables for a while, yes that may explain queries taking too
long, which may cause other symptoms. If analyze hasn't been performed
for a while, then you may see suboptimal plans for the queries.
But, again, you haven't provided enough information, so you may not get
very helpful answers.
Best regards,
Gurjeet
http://Gurje.et
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Failover and vacuum
@ 2025-03-29 06:39 Raj <rajeshkumar.dba09@gmail.com>
parent: Gurjeet Singh <gurjeet@singh.im>
1 sibling, 0 replies; 7+ messages in thread
From: Raj @ 2025-03-29 06:39 UTC (permalink / raw)
To: Gurjeet Singh <gurjeet@singh.im>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
What additional information required?
On Sat, 29 Mar 2025, 08:55 Gurjeet Singh, <gurjeet@singh.im> wrote:
> On Thu Mar 27, 2025 at 10:40 AM PDT, Raj wrote:
> > Hi
> >
> > Have 2 nodes ( primary and standby postgres 15.6) in openshift
> kubernetes.
> >
> > Patroni setup. 300gb data. No failover since last six months. Suddenly
> > after failover, there were lot of issues such as too many connections and
> > slowness.
> >
> > Is it due to not analyze done in new node?
>
> There isn't enough information in your message for anyone to take an
> educated guess. If you see evidence of vacuum not being performed on
> certain tables for a while, yes that may explain queries taking too
> long, which may cause other symptoms. If analyze hasn't been performed
> for a while, then you may see suboptimal plans for the queries.
>
> But, again, you haven't provided enough information, so you may not get
> very helpful answers.
>
> Best regards,
> Gurjeet
> http://Gurje.et
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Failover and vacuum
@ 2025-03-29 10:48 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Gurjeet Singh <gurjeet@singh.im>
1 sibling, 0 replies; 7+ messages in thread
From: Ron Johnson @ 2025-03-29 10:48 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Fri, Mar 28, 2025 at 11:25 PM Gurjeet Singh <gurjeet@singh.im> wrote:
> On Thu Mar 27, 2025 at 10:40 AM PDT, Raj wrote:
> > Hi
> >
> > Have 2 nodes ( primary and standby postgres 15.6) in openshift
> kubernetes.
> >
> > Patroni setup. 300gb data. No failover since last six months. Suddenly
> > after failover, there were lot of issues such as too many connections and
> > slowness.
> >
> > Is it due to not analyze done in new node?
>
> There isn't enough information in your message for anyone to take an
> educated guess. If you see evidence of vacuum not being performed on
> certain tables for a while, yes that may explain queries taking too
> long, which may cause other symptoms. If analyze hasn't been performed
> for a while, then you may see suboptimal plans for the queries.
>
> But, again, you haven't provided enough information, so you may not get
> very helpful answers.
>
But don't vacuuming and analyze statistics get replicated?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Failover and vacuum
@ 2025-03-29 11:08 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Raj <rajeshkumar.dba09@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Ron Johnson @ 2025-03-29 11:08 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Thu, Mar 27, 2025 at 1:41 PM Raj <rajeshkumar.dba09@gmail.com> wrote:
> Hi
>
> Have 2 nodes ( primary and standby postgres 15.6) in openshift
> kubernetes.
>
> Patroni setup. 300gb data. No failover since last six months. Suddenly
> after failover, there were lot of issues such as too many connections and
> slowness.
>
> Is it due to not analyze done in new node?
>
Is postgresql.conf configured the same on both nodes?
max_connections being lower on the replica node would certainly and
*immediately* cause "too many connections" errors.
diff -y --suppress-common-lines $PGDATA/postgresql.conf <(ssh -q
otherserver "cat $PGDATA/postgresql.conf")
Vacuuming and statistics_are_ replicated: that data is in tables, so *must*
be replicated). However, *when* they were last vacuumed and analyzed is
apparently not on disk. Thus, the new primary can't know the number of
tuples analyzed since the last ANALYZE, and the number of dead and inserted
records since the last ANALYZE.
Thus, I'd do a vacuumdb --analyze-in-stages soon after the switch-over.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Failover and vacuum
@ 2025-03-29 13:10 Raj <rajeshkumar.dba09@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Raj @ 2025-03-29 13:10 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Great, it's same configuration on both nodes. We are not taking connections
from standby anyway.
On Sat, 29 Mar 2025, 16:38 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
> On Thu, Mar 27, 2025 at 1:41 PM Raj <rajeshkumar.dba09@gmail.com> wrote:
>
>> Hi
>>
>> Have 2 nodes ( primary and standby postgres 15.6) in openshift
>> kubernetes.
>>
>> Patroni setup. 300gb data. No failover since last six months. Suddenly
>> after failover, there were lot of issues such as too many connections and
>> slowness.
>>
>> Is it due to not analyze done in new node?
>>
>
> Is postgresql.conf configured the same on both nodes?
>
> max_connections being lower on the replica node would certainly and
> *immediately* cause "too many connections" errors.
>
>
> diff -y --suppress-common-lines $PGDATA/postgresql.conf <(ssh -q otherserver "cat $PGDATA/postgresql.conf")
>
> Vacuuming and statistics_are_ replicated: that data is in tables, so
> *must* be replicated). However, *when* they were last vacuumed and
> analyzed is apparently not on disk. Thus, the new primary can't know the
> number of tuples analyzed since the last ANALYZE, and the number of dead
> and inserted records since the last ANALYZE.
>
> Thus, I'd do a vacuumdb --analyze-in-stages soon after the switch-over.
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Failover and vacuum
@ 2025-03-29 15:05 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Raj <rajeshkumar.dba09@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Ron Johnson @ 2025-03-29 15:05 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
So what would cause "too many connections", if both nodes have the same
mox_connections?
What does your application do when DB response time is slow? I've read
that pod-type services can be configured to spawn more connections when the
app thinks the DB isn't fast enough.
On Sat, Mar 29, 2025 at 9:10 AM Raj <rajeshkumar.dba09@gmail.com> wrote:
> Great, it's same configuration on both nodes. We are not taking
> connections from standby anyway.
>
> On Sat, 29 Mar 2025, 16:38 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
>
>> On Thu, Mar 27, 2025 at 1:41 PM Raj <rajeshkumar.dba09@gmail.com> wrote:
>>
>>> Hi
>>>
>>> Have 2 nodes ( primary and standby postgres 15.6) in openshift
>>> kubernetes.
>>>
>>> Patroni setup. 300gb data. No failover since last six months. Suddenly
>>> after failover, there were lot of issues such as too many connections and
>>> slowness.
>>>
>>> Is it due to not analyze done in new node?
>>>
>>
>> Is postgresql.conf configured the same on both nodes?
>>
>> max_connections being lower on the replica node would certainly and
>> *immediately* cause "too many connections" errors.
>>
>>
>> diff -y --suppress-common-lines $PGDATA/postgresql.conf <(ssh -q otherserver "cat $PGDATA/postgresql.conf")
>>
>> Vacuuming and statistics_are_ replicated: that data is in tables, so
>> *must* be replicated). However, *when* they were last vacuumed and
>> analyzed is apparently not on disk. Thus, the new primary can't know the
>> number of tuples analyzed since the last ANALYZE, and the number of dead
>> and inserted records since the last ANALYZE.
>>
>> Thus, I'd do a vacuumdb --analyze-in-stages soon after the switch-over.
>>
>> --
>> Death to <Redacted>, and butter sauce.
>> Don't boil me, I'm still alive.
>> <Redacted> lobster!
>>
>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-03-29 15:05 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-27 17:40 Failover and vacuum Raj <rajeshkumar.dba09@gmail.com>
2025-03-29 03:25 ` Gurjeet Singh <gurjeet@singh.im>
2025-03-29 06:39 ` Raj <rajeshkumar.dba09@gmail.com>
2025-03-29 10:48 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-03-29 11:08 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-03-29 13:10 ` Raj <rajeshkumar.dba09@gmail.com>
2025-03-29 15:05 ` Ron Johnson <ronljohnsonjr@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox