Tometzky :
Running queries on hot-standby server is somewhat tricky — it can fail, because during querying some needed rows might be updated or deleted on primary. As a primary does not know that a query is started on secondary it thinks it can clean up (vacuum) old versions of its rows. Then secondary has to replay this cleanup, and has to forcibly cancel all queries which can use these rows.\n\nLonger queries will be canceled more often.\n\nYou can work around this by starting a repeatable read transaction on primary which does a dummy query and then sits idle while a real query is run on secondary. Its presence will prevent vacuuming of old row versions on primary.\n\nMore on this subject and other workarounds are explained in Hot Standby — Handling Query Conflicts section in documentation.",
2013-01-29T23:51:54
eradman :
There's no need to start idle transactions on the master. In postgresql-9.1 the\nmost direct way to solve this problem is by setting\n\nhot_standby_feedback = on\n\n\nThis will make the master aware of long-running queries. From the docs:\n\n\n The first option is to set the parameter hot_standby_feedback, which prevents\n VACUUM from removing recently-dead rows and so cleanup conflicts do not occur.\n\n\nWhy isn't this the default? This parameter was added after the initial\nimplementation and it's the only way that a standby can affect a master.",
2014-02-10T19:34:59
Gilles Quenot :
As stated here about hot_standby_feedback = on :\n\n\n Well, the disadvantage of it is that the standby can bloat the master,\n which might be surprising to some people, too\n\n\nAnd here: \n\n\n With what setting of max_standby_streaming_delay? I would rather\n default that to -1 than default hot_standby_feedback on. That way what\n you do on the standby only affects the standby\n\n\n\n\nSo I added \n\nmax_standby_streaming_delay = -1\n\n\nAnd no more pg_dump error for us, nor master bloat :)\n\nFor AWS RDS instance, check http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html",
2015-10-22T13:58:34
David Jaspers :
The table data on the hot standby slave server is modified while a long running query is running. A solution (PostgreSQL 9.1+) to make sure the table data is not modified is to suspend the replication and resume after the query:\n\nselect pg_xlog_replay_pause(); -- suspend\nselect * from foo; -- your query\nselect pg_xlog_replay_resume(); --resume\n",
2017-10-05T08:56:02
Artif3x :
I'm going to add some updated info and references to @max-malysh's excellent answer above.\n\nIn short, if you do something on the master, it needs to be replicated on the slave. Postgres uses WAL records for this, which are sent after every logged action on the master to the slave. The slave then executes the action and the two are again in sync. In one of several scenarios, you can be in conflict on the slave with what's coming in from the master in a WAL action. In most of them, there's a transaction happening on the slave which conflicts with what the WAL action wants to change. In that case, you have two options:\n\n\nDelay the application of the WAL action for a bit, allowing the slave to finish its conflicting transaction, then apply the action.\nCancel the conflicting query on the slave.\n\n\nWe're concerned with #1, and two values:\n\n\nmax_standby_archive_delay - this is the delay used after a long disconnection between the master and slave, when the data is being read from a WAL archive, which is not current data.\nmax_standby_streaming_delay - delay used for cancelling queries when WAL entries are received via streaming replication.\n\n\nGenerally, if your server is meant for high availability replication, you want to keep these numbers short. The default setting of 30000 (milliseconds if no units given) is sufficient for this. If, however, you want to set up something like an archive, reporting- or read-replica that might have very long-running queries, then you'll want to set this to something higher to avoid cancelled queries. The recommended 900s setting above seems like a good starting point. I disagree with the official docs on setting an infinite value -1 as being a good idea--that could mask some buggy code and cause lots of issues.\n\nThe one caveat about long-running queries and setting these values higher is that other queries running on the slave in parallel with the long-running one which is causing the WAL action to be delayed will see old data until the long query has completed. Developers will need to understand this and serialize queries which shouldn't run simultaneously.\n\nFor the full explanation of how max_standby_archive_delay and max_standby_streaming_delay work and why, go here.",
2020-02-19T16:18:06
Tushar.k :
It might be too late for the answer but we face the same kind of issue on the production.\nEarlier we have only one RDS and as the number of users increases on the app side, we decided to add Read Replica for it. Read replica works properly on the staging but once we moved to the production we start getting the same error.\nSo we solve this by enabling hot_standby_feedback property in the Postgres properties.\nWe referred the following link\nhttps://aws.amazon.com/blogs/database/best-practices-for-amazon-rds-postgresql-replication/\nI hope it will help.",
2020-02-16T05:14:50
Max Malysh :
No need to touch hot_standby_feedback. As others have mentioned, setting it to on can bloat master. Imagine opening transaction on a slave and not closing it.\n\nInstead, set max_standby_archive_delay and max_standby_streaming_delay to some sane value:\n\n# /etc/postgresql/10/main/postgresql.conf on a slave\nmax_standby_archive_delay = 900s\nmax_standby_streaming_delay = 900s\n\n\nThis way queries on slaves with a duration less than 900 seconds won't be cancelled. If your workload requires longer queries, just set these options to a higher value.",
2017-12-05T19:33:42