Re: Replication failed after stalling

On Mon, Dec 30, 2013 at 10:27 AM, Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> wrote:

> On Mon, Dec 30, 2013 at 12:02 AM, Joe Van Dyk <joe(at)tanga(dot)com> wrote:
> > On Sun, Dec 29, 2013 at 10:52 PM, Sergey Konoplev <gray(dot)ru(at)gmail(dot)com>
> wrote:
> >> On Sun, Dec 29, 2013 at 9:56 PM, Joe Van Dyk <joe(at)tanga(dot)com> wrote:
> >> > On Wed, Dec 18, 2013 at 3:39 PM, Sergey Konoplev <gray(dot)ru(at)gmail(dot)com>
> >> > wrote:
> >> > If I run "COPY (select * from complicate_view) to stdout" on the
> >> > standby,
> >> > I've noticed that sometimes halts replication updates to the slave.
> >> >
> >> > For example, that's happening right now and "now() -
> >> > pg_last_xact_replay_timestamp()" is 22 minutes. There's many
> >> > transactions
> >> > per second being committed on the master. Once that query is canceled,
> >> > the
> >> > slave catches up immediately.
> >>
> >> And what
> >>
> >> \x
> >> select * from pg_stat_repication;
> >>
> >> shows?
> >
> > on the master, right?
>
> Yes.
>
> And it would be very useful to take a look at your checkpoints and
> replication configuration parameters on both master and replica.
>

master and replica have same settings.

checkpoint_completion_target: 0.9
checkpoint_segments: 16
checkpoint_timeout: 5m
checkpoint_warning: 30s
hot_standby: on
hot_standby_feedback: on

pid | 10736
usesysid | 10
usename | postgres
application_name | walreceiver
client_addr | <the ip>
client_hostname |
client_port | 47124
backend_start | 2013-12-30 12:08:42.967868-08
state | streaming
sent_location | 410/BC152000
write_location | 410/BC152000
flush_location | 410/BC152000
replay_location | 410/A758B7D0
sync_priority | 0
sync_state | async