Re: Replication failed after stalling

[Joe Van Dyk <joe@xxxxxxxxx>]

On Mon, Dec 30, 2013 at 10:27 AM, Sergey Konoplev <gray.ru@xxxxxxxxx> wrote:

On Mon, Dec 30, 2013 at 12:02 AM, Joe Van Dyk <joe@xxxxxxxxx> wrote:
> On Sun, Dec 29, 2013 at 10:52 PM, Sergey Konoplev <gray.ru@xxxxxxxxx> wrote:
>> On Sun, Dec 29, 2013 at 9:56 PM, Joe Van Dyk <joe@xxxxxxxxx> wrote:
>> > On Wed, Dec 18, 2013 at 3:39 PM, Sergey Konoplev <gray.ru@xxxxxxxxx>
>> > 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