pg_dump Conflict with recovery

From: Israel Brewster <israel(at)ravnalaska(dot)net>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: pg_dump Conflict with recovery
Date: 2017-01-23 18:24:06
Message-ID: 6A99621F-F35E-4335-97EA-C4449B030179@ravnalaska.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a backup strategy that in part consists of doing pg_dump s on my various databases. In order to hopefully reduce/prevent operational slow-down as a result of the backup, I do the dumps from my secondary server, configured as a hot standby with streaming replication.

In general this works fine, but one of my databases has now grown to the point that often as not I get the following when trying to dump the database:

ERROR: canceling statement due to conflict with recovery
DETAIL: User was holding a relation lock for too long.

As I understand it, this is due to the pg_dump taking longer than the max_standby_streaming_delay of 180s, and as such could be easily fixed by upping that value in the config. But is that the "right" fix? Or is there a "better" way?
-----------------------------------------------
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
-----------------------------------------------

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jerry Sievers 2017-01-23 19:00:33 Re: Why does this hot standy archive_command work
Previous Message btober@computer.org 2017-01-23 18:06:01 Re: Why does this hot standy archive_command work