On Wed, Feb 18, 2015 at 4:57 PM, Kevin Grittner <kgri...@ymail.com> wrote:
> >> But max_standby_streaming_delay, max_standby_archive_delay and > >> hot_standby_feedback are among the most frequent triggers for > >> questions and complaints that I/we see. > >> > > Agreed. > > And a really bad one used to be vacuum_defer_cleanup_age, because > > of confusing units amongst other things. Which in terms seems > > fairly close to Kevins suggestions, unfortunately. > > Particularly my initial suggestion, which was to base snapshot > "age" it on the number of transaction IDs assigned. Does this look > any better to you if it is something that can be set to '20min' or > '1h'? Just to restate, that would not automatically cancel the > snapshots past that age; it would allow vacuum of any tuples which > became "dead" that long ago, and would cause a "snapshot too old" > message for any read of a page modified more than that long ago > using a snapshot which was older than that. > > I like this thought. One of the first things I do in a new Pg environment is setup a cronjob that watches pg_stat_activity and terminates most backends over N minutes in age (about 5x the length of normal work) with an exception for a handful of accounts doing backups and other maintenance operations. This prevents a stuck client from jamming up the database. Would pg_dump be able to opt-out of such a restriction? regards, Rod