Re: Big UPDATE breaking replication

Kouber Saparev wrote:
> We are using the 9.1 built-in streaming replication.
>
> Recently our slave nodes fell behind because of an UPDATE statement. It
> took about 3 minutes to execute, but it affected half a million records,
> hence the replication broke with the "requested WAL segment ... has
> already been removed" series of error messages.
>
> The WAL settings we have are:
>
> max_wal_senders = 6
> wal_keep_segments = 60
> max_standby_archive_delay = 300s
>
>
> I guess increasing the wal_keep_segments value would prevent it from
> happening in the future, but increase it with how much? What value would
> be high enough?

That depends on how much WAL your UPDATEs generate.
In other words, there is no safe value that will guarantee that
you still have all the WAL files you need.

To be on the safe side, you should additionally configure log shipping
as described in http://www.postgresql.org/docs/current/static/warm-standby.html
That way your standby will always be able to catch up.

> Also we noticed some strange error message appearing shortly before and
> after this same statement: "LOG: out of file descriptors: Too many open
> files; release and retry".
>
> Could it be related somehow and what does it mean exactly?

It means that PostgreSQL tries to open more files than the operating
system allows. Try to increase that parameter.

I don't know if there is a connection to the standby falling behind,
but that can always happen if there is enough activity on the primary.

Yours,
Laurenz Albe