How to Fix PostgreSQL Error: Recovery Conflict

Recovery conflicts occur on hot standby replicas when replay of WAL records conflicts with currently running queries. PostgreSQL must choose between query consistency and replication lag.

Impact

Causes query cancellations on standby servers, affecting read replica availability and potentially increasing replication lag.

Common Causes

1. Long-running queries on standby
2. Vacuum on primary conflicting with queries
3. Table drops/truncates on primary
4. Hot standby feedback disabled
5. max_standby_streaming_delay too low

Troubleshooting and Resolution Steps

1. Enable hot_standby_feedback:

   -- On standby server
   ALTER SYSTEM SET hot_standby_feedback = on;
   SELECT pg_reload_conf();
   

2. Increase delay tolerance:

   -- Allow more delay before canceling queries
   ALTER SYSTEM SET max_standby_streaming_delay = '300s';
   SELECT pg_reload_conf();
   

3. Monitor conflicts:

   SELECT * FROM pg_stat_database_conflicts;
   

Additional Information

• Balance between replication lag and query availability
• hot_standby_feedback prevents some conflicts
• Consider dedicated reporting replicas
• Route long queries to specific replicas

Frequently Asked Questions

Q: Will hot_standby_feedback cause bloat?
A: Can delay vacuum on primary. Monitor and balance against conflict frequency.

Q: Can I prevent all conflicts?
A: No, but can minimize with hot_standby_feedback and appropriate delay settings.