Fix lỗi PostgreSQL 'canceling statement due to conflict with recovery' trên Standby Server

Lỗi Gì Đang Xảy Ra

Bạn chạy một truy vấn trên máy chủ standby PostgreSQL. Truy vấn chạy được một phút rồi bị hủy:

ERROR:  canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.

Lỗi này chỉ xảy ra trên standby — không bao giờ xảy ra trên primary. Nếu bạn đang gặp lỗi này trên read replica trong môi trường production, đây là nguyên nhân chính xác và cách khắc phục.

Nguyên Nhân Gốc Rễ

PostgreSQL streaming replication phát lại các bản ghi WAL (Write-Ahead Log) từ primary lên standby. Một bản ghi WAL đôi khi yêu cầu standby dọn dẹp các phiên bản hàng cũ — dead tuples — mà truy vấn của bạn vẫn cần đọc.

PostgreSQL phải đưa ra lựa chọn: trì hoãn việc phát lại WAL (tạo ra replication lag) hoặc hủy truy vấn của bạn. Mặc định nó chọn hủy truy vấn. Khoảng thời gian chờ trước khi hủy được kiểm soát bởi max_standby_streaming_delay, mặc định chỉ 30 giây.

Nguyên nhân thường gặp: các báo cáo chạy lâu, tác vụ analytics ban đêm, hoặc bất kỳ truy vấn OLAP nào quét hàng triệu hàng trên standby.

Cách Sửa 1: Tăng Thời Gian Chờ Xung Đột (Nhanh Nhất)

Cho PostgreSQL thêm thời gian trước khi hủy các truy vấn xung đột. Chỉnh sửa postgresql.conf trên máy chủ standby:

# postgresql.conf on standby
max_standby_streaming_delay = 300s   # default: 30s — bump to 5 minutes
max_standby_archive_delay = 300s     # also raise if you use archive recovery

Reload mà không cần khởi động lại:

sudo -u postgres psql -c "SELECT pg_reload_conf();"

Hoặc qua systemd:

sudo systemctl reload postgresql

Đặt giá trị này thành -1 để PostgreSQL chờ vô thời hạn — việc phát lại WAL tạm dừng cho đến khi truy vấn của bạn hoàn thành. Cách này ổn khi chấp nhận replication lag vài phút; nhưng không nên dùng nếu standby của bạn cũng phục vụ failover.

Cách Sửa 2: Bật hot_standby_feedback (Tốt Nhất Cho Standby Đọc Nhiều)

Cách này xử lý nguyên nhân gốc rễ thay vì triệu chứng. Khi bật hot_standby_feedback = on, standby liên tục thông báo cho primary biết transaction ID nào nó đang sử dụng. Primary sẽ trì hoãn việc vacuum các hàng đó cho đến khi standby xử lý xong.

Chỉnh sửa postgresql.conf trên máy chủ standby:

# postgresql.conf on standby
hot_standby_feedback = on

Reload config:

sudo -u postgres psql -c "SELECT pg_reload_conf();"

Lưu ý: primary sẽ trì hoãn autovacuum cho các hàng mà standby đang tham chiếu. Nếu standby của bạn chạy các truy vấn kéo dài 30+ phút, table bloat trên primary có thể tăng nhanh. Theo dõi pg_stat_user_tables.n_dead_tup trên primary sau khi bật tùy chọn này — số dead tuples tăng đột biến là dấu hiệu cảnh báo.

Cách Sửa 3: Đặt Timeout Ở Phía Ứng Dụng

Đôi khi xung đột xảy ra do transaction bị bỏ ngỏ — một kết nối đã bắt đầu transaction, chạy truy vấn, rồi bị treo vì đang chờ logic ứng dụng. Hãy đặt giới hạn trước khi chạy bất kỳ thứ gì tốn kém:

-- At the start of your session
SET idle_in_transaction_session_timeout = '5min';
SET statement_timeout = '10min';

-- Then run your query
SELECT * FROM large_analytics_table WHERE created_at > NOW() - INTERVAL '30 days';

Cách này không ngăn được xung đột WAL, nhưng ngăn các transaction bị bỏ rơi làm trầm trọng thêm vấn đề.

Cách Sửa 4: Retry Logic Trong Code Ứng Dụng

Đối với ứng dụng truy vấn trực tiếp vào standby, hãy bắt lỗi và thử lại. Hầu hết các lần hủy do xung đột đều mang tính tạm thời — chỉ cần dừng một chút là đủ:

import psycopg2
from psycopg2 import OperationalError
import time

def query_with_retry(conn, sql, retries=3, delay=2):
    for attempt in range(retries):
        try:
            cur = conn.cursor()
            cur.execute(sql)
            return cur.fetchall()
        except OperationalError as e:
            if 'canceling statement due to conflict with recovery' in str(e):
                conn.rollback()
                if attempt < retries - 1:
                    time.sleep(delay)
                    continue
            raise
    raise Exception("Max retries exceeded")

Cách Sửa 5: Chuyển Truy Vấn Nặng Ra Khỏi Standby

Đôi khi câu trả lời thực sự nằm ở topology. Hãy route các truy vấn analytics nặng sang một replica chuyên dụng nằm ngoài hot-standby pool — hoặc lên lịch chạy chúng trên primary trong giờ thấp điểm.

Với các workload báo cáo nặng (như aggregation kéo dài nhiều giờ), một logical replica hoặc database analytics chuyên dụng như Redshift hay BigQuery sẽ loại bỏ hoàn toàn vấn đề. Streaming standby không được thiết kế cho kiểu truy vấn như vậy.

Các Bước Kiểm Tra

Sau khi áp dụng cách sửa, hãy xác nhận thay đổi đã có hiệu lực.

Kiểm tra cấu hình hiện tại trên standby:

sudo -u postgres psql -c "SHOW max_standby_streaming_delay;"
sudo -u postgres psql -c "SHOW hot_standby_feedback;"

Theo dõi replication lag trên primary — đặc biệt sau khi bật hot_standby_feedback hoặc tăng delay. Bạn không muốn standby bị trễ 10 phút:

-- Run on PRIMARY
SELECT
  application_name,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  write_lag,
  flush_lag,
  replay_lag
FROM pg_stat_replication;

Chạy lại truy vấn đang bị lỗi. Nếu nó hoàn thành bình thường, bạn đã xong.

Xem log của standby để xác nhận không còn thông báo xung đột nào:

sudo tail -f /var/log/postgresql/postgresql-*.log | grep -i conflict

Nên Dùng Cách Nào?

Nhanh nhất, rủi ro thấp nhất: Tăng max_standby_streaming_delay lên 300–600s. Khoảng chờ 5 phút loại bỏ được hầu hết các lần hủy truy vấn.
Standby đọc nhiều: Bật hot_standby_feedback, sau đó theo dõi table bloat trên primary.
Kết hợp tốt nhất: hot_standby_feedback = on cộng với max_standby_streaming_delay = 300s làm lưới an toàn. Cách này xử lý các trường hợp ngoại lệ khi tín hiệu feedback đến quá muộn.
Analytics nặng: Dùng replica chuyên dụng hoặc database analytics riêng — đừng cố chống lại mô hình replication.

Với đa số cấu hình, bật hot_standby_feedback = on cùng với max_standby_streaming_delay = 300s trên standby sẽ giải quyết lỗi này vĩnh viễn.