Re: pg_dump's results have quite different size

After I enlarged the max_standby_streaming_delay, it works well for some
days (I have to increase the max_standby_streaming_delay when data is
bigger).

Thank you all!

A suggestion might be: pg_dump from the standby is frequently used in
backup tasks; it'll be much better if we have some options to ensure the
pg_dump to finish; for example, an option to temporary stop the
replication? Or do we already have some approach?

On Sat, Dec 17, 2016 at 6:30 AM, Mark Kirkwood <
mark(dot)kirkwood(at)catalyst(dot)net(dot)nz> wrote:

> On 17/12/16 09:47, Jeff Janes wrote:
>
> On Thu, Dec 15, 2016 at 11:18 PM, Mark Kirkwood <
>> mark(dot)kirkwood(at)catalyst(dot)net(dot)nz <mailto:mark(dot)kirkwood(at)catalyst(dot)net(dot)nz>>
>> wrote:
>>
>> On 14/12/16 22:09, Oleksandr Shulgin wrote:
>>
>> On Wed, Dec 14, 2016 at 9:29 AM, Kaijiang Chen
>> <chenkaijiang(at)gmail(dot)com <mailto:chenkaijiang(at)gmail(dot)com>
>> <mailto:chenkaijiang(at)gmail(dot)com
>> <mailto:chenkaijiang(at)gmail(dot)com>>> wrote:
>>
>> Yes. The pg_dump quits with the message:
>>
>> pg_dump: Dumping the contents of table "data_histories"
>> failed:
>> PQgetResult() failed.
>> pg_dump: Error message from server: ERROR: canceling statement
>> due to conflict with recovery
>> DETAIL: User query might have needed to see row versions that
>> must be removed.
>> pg_dump: The command was: COPY public.data_histories (id,
>> user_id,
>> user_name, type, type_id, old_data, action, new_data,
>> created_at,
>> updated_at) TO stdout;
>>
>>
>> Ah, then it's just that your backup script is broken: it
>> should have reported the error.
>>
>> Please do not top-post.
>>
>>
>> Oleksandr - how about a helpful response? E.g suggesting that
>> maybe increasing max_standby_streaming_delay might help? Goddamn!
>> folk asking for help deserve better than just being told 'it is
>> broken dickhead'...
>>
>>
>> The problem *is* that his backups are broken without him knowing it.
>> Maybe increasing max_standby_streaming_delay is an answer, but maybe he
>> would rather have occasional broken backups *which he knows about* then
>> suffer the consequences of an increased max_standby_streaming_delay. Maybe
>> hot_standby_feedback would be a better option, or maybe
>> vacuum_defer_cleanup_age (but that is less likely).
>>
>> The only thing we actually know with reasonable certainty is that his
>> backup script is broken, and that this is bad. Randomly changing settings
>> so that the brokenness is still there but just less obvious is more
>> dangerous than helpful.
>>
>>
> It seems we know quite a lot more, evidenced by the error above. It also
> seems clear that he wants his backups to work, not just report errors
> surely? That the script should check return codes better (or at all) sure,
> that seems to have been emphasized quite sufficiently.
>
> regards
>
> Mark
>