Re: Streaming Replication Randomly Locking Up

[Andrew Berman <rexxe98@xxxxxxxxx>]

Hi Jeff,

Here is the full process list at the time it stopped working (I have changed the actual username, db and IP for security).  Would the idle in transaction process be the culprit?

postgres  5639  0.1 24.3 3428264 2970236 ?     Ss   Aug14   1:54 postgres: startup process   recovering 000000010000053D0000003F waiting

postgres  5642  0.0 21.4 3428356 2613252 ?     Ss   Aug14   0:30 postgres: writer process

postgres  5659  0.0  0.0 177524   788 ?        Ss   Aug14   0:03 postgres: stats collector process

postgres  7159  1.2  0.1 3451360 18352 ?       Ss   Aug14  17:31 postgres: wal receiver process   streaming 549/216B3730

postgres 10403  0.0  0.2 3430372 25920 ?       Ss   Aug14   0:31 postgres: user db x.x.x.x(61656) idle in transaction

postgres 19933  0.0  0.4 3426604 49564 ?       S    Aug05   0:06 /usr/pgsql-9.1/bin/postmaster -p 5432 -D /var/lib/pgsql/9.1/data

postgres 19935  0.0  0.0 175288   396 ?        Ss   Aug05   0:13 postgres: logger process

postgres 21133  0.0  0.2 3443600 30680 ?       Ss   09:28   0:00 postgres: user db x.x.x.x(64430) idle

postgres 21134  0.4  0.2 3430160 27656 ?       Ss   09:28   0:16 postgres: user db x.x.x.x(64431) idle

root     21529  0.0  0.0 103240   844 pts/0    S+   10:33   0:00 grep --color postgres

 

Thanks,

Andrew

On Thu, Aug 15, 2013 at 1:20 PM, Jeff Janes <jeff.janes@xxxxxxxxx> wrote:

On Thu, Aug 15, 2013 at 11:07 AM, Andrew Berman <rexxe98@xxxxxxxxx> wrote:

> Hello,
>
> I'm having an issue where streaming replication just randomly stops working.
> I haven't been able to find anything in the logs which point to an issue,
> but the Postgres process shows a "waiting" status on the slave:
>
> postgres  5639  0.1 24.3 3428264 2970236 ?     Ss   Aug14   1:54 postgres:
> startup process   recovering 000000010000053D0000003F waiting

There is a recovery conflict which it is waiting to go away.  In other
words, you have a long-running (or long-idle) transaction on the slave
which is blocking recovery.

> max_standby_archive_delay = -1
> max_standby_streaming_delay = -1

...and you are willing to wait forever.

Cheers,

Jeff