While running a SELECT query (bit large query ~1GB of data output which took ~15minutes to execute) in hot standby replication host, its halts the wal archiving process. Once the Query done with the execution, the replication takes place and started replicating. Is that normal behaviour of postgres or need to tune system runtime configuration?

I have tried to change work_mem=64MB to work_mem=96MB, but still I couldn't solve the delay.

(using PG 9.5)

  • Are you running the query on Master or slave? If its a slave, i think the replication stops because incoming committed data is in the same table from where data is being fetched by the query. Try running in master and see if there is any difference. – Lohit Gupta Aug 10 '17 at 6:40
  • Does the SELECT do any writes via some functions etc? If so, yes, that's expected if hot_standby_feedback is enabled on the replica. – Craig Ringer Aug 10 '17 at 6:56
  • @Lohit, Its running on slave. And I can't execute the query in master since its having frequent updates / inserts. – Spike Aug 10 '17 at 10:25
  • @Craig, No. Its running as direct query (directly via psql and output written to a file). And yes, the parameter is on. pass upstream make any difference if I disable it? – Spike Aug 10 '17 at 10:25
  • @Spike if that is the case, then it is expected behavior, as the frequent updates/inserts cannot be written to slave table when it is locked by the select query/vice versa. I would suggest you look at the patterns when inserts/updates are coming in master, and schedule the select query in slave when the dmls are not coming in master. – Lohit Gupta Aug 10 '17 at 12:48

Replication can be delayed if a query on the standby needs data the master has removed. The WAL from the master with the resource removal cannot be replayed until the standby query finishes, or the standby query must be cancelled.

You can:

  • Let the standby cancel the query after a time limit - see the max_standby_archive_delay and max_standby_stremaing_delay parameters; or

  • Enable hot_standby_feedback on the standby so the master won't remove resources the standby needs while its queries are running. This isn't perfect, conflicts that delay replication or cause query cancels can still happen when the standby just recently started up. But it helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.