Re: Streaming Replication Randomly Locking Up

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(dot)janes(at)gmail(dot)com> wrote:

> On Thu, Aug 15, 2013 at 11:07 AM, Andrew Berman <rexxe98(at)gmail(dot)com> 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
>