We have a scenario, where the application wants to run some read-only query on standby server. We have configured a master-slave streaming replication. The application team is requesting for a read-only access on slave. My concern is that primary receives a lot of transaction and is busy for 24x7, so will fetching data from standby might cause a conflict on primary.
If so, is there any alternate solution to avoid conflict on primary as well as query termination on secondary!
Please suggest some open source tools / method to over come with it.
If your streaming replication is asynchronous. Then, in that case, your application read-only queries on the slave cluster won't hamper any performance on the master server.
Hope this answers your query.
Thank you, Dhananjay
Sorry for the delay, One quick question - will the standdby queries get hamper due to any conflict with respect to master?
Thanks in advance
Yes, There could be a conflict scenario.
In that case, you can get error messages as below indicates that the queries on standby server are canceled as they were conflicting with the recovery.
OperationalError: canceling statement due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.
These are dependent on the two parameters max_standby_archive_delay and max_standby_streaming_delay.
Queries which are conflicting for the set value will get canceled.
So, You can set maximum time a query takes for the execution on replica to avoid these issue, it requires only reload of the database.