How do I synchronize the subscription when the server terminated unexpectedly

Question

I have a publisher and a subscriber. Every so often I get:

ERROR:  could not receive data from WAL stream: server closed the connection unexpectedly
This probably means the server terminated abnormally before or while processing the request.

I can guess why it terminates abnormally, one of the computers turn off. However, when the two computers are connected again, it doesn't restart automatically.

The only thing that works is to truncate all the tables in the subscription, delete the subscription and publication and create the subscription and publication again.

I tried looking at the WAL, they're very nice. Not sure what to do otherwise.

Here are some pictures:

sub table stuff pub table stuff sub log pub log

Laurenz Albe · Answer 1 · 2019-05-16T14:53:13.833

0

It should not be necessary to re-initialize logical replication just because there was a connection problem. The logical replication slot on the primary will make sure that all required information is retained on the server so that replication can be resumed later on.

Reading you primary log, it looks like you are just hitting a timeout because there is nothing to replicate. That shouldn't be a problem, but you can set wal_sender_timeout = 0 on the primary to disable the timeout.

edited May 16 '19 at 14:53

answered May 16 '19 at 06:26

Laurenz Albe

209,280
17
206
263

So how do I resume it? I think it's automatically trying to resume, but if fails. – Henrietta Martingale May 16 '19 at 13:57
It keeps failing with that message? Then either there must be a message in the primary's log, or there is a timeout because there is nothing to replicate. – Laurenz Albe May 16 '19 at 14:46
Reading your log, it seems you are hitting a timeout. See my updated answer. – Laurenz Albe May 16 '19 at 14:53
When this happens, tables aren't getting updated in the sub. It suggests there are things to replicate. Also the wal that the pub is on with sent_lsn.pg_stat_replication and the one on the sub with received_lsn.pg_stat_subscription are different – Henrietta Martingale May 17 '19 at 17:43
So what are your settings for `wal_sender_timeout` on the primary and `wal_receiver_status_interval` on the standby? Are you sure there are things to replicate? – Laurenz Albe May 17 '19 at 21:26
wal_receiver_status_interval was commented out so I assume defaults to 10s, wal_sender_timeout also commented out so I assume default of 60s – Henrietta Martingale May 23 '19 at 16:34
I added more info. wal_sender_timeout is commented out so I think 10s and wal_receiver_status_interfal also commented out so I think 60s. Maybe vice versa. Also, please see new picts – Henrietta Martingale May 23 '19 at 16:56
Images don't help. Use the `SHOW` SQL statement to get the current value of the parameters. – Laurenz Albe May 23 '19 at 17:02
wal_sender_timeout 60s on pub, wal_receiver_status_interval 10s on sub. The picts were for the log and table stuff. – Henrietta Martingale May 23 '19 at 21:53
Does the problem vanish if you try the suggestion from my answer? – Laurenz Albe May 24 '19 at 05:22
They don't and the tables in the offending subscription are missing rows in the subscriber – Henrietta Martingale May 27 '19 at 14:03

How do I synchronize the subscription when the server terminated unexpectedly

1 Answers1