PostgreSQL Tutorial: Check long running transactions on standby

Summary: In this tutorial, you will learn how to check long running transactions on standby in PostgreSQL.

Table of Contents

Standby with hot_standby_feedback and VACUUM

Typically, PostgreSQL can clean up a row version as soon as it isn’t visible to any transaction. If you’re running PostgreSQL on a primary with a standby node, it’s possible for a vacuum to clean up a row version on the primary which is needed by a query on the standby. This situation is called a “replication conflict” — and when it’s detected, the query on the standby node will be cancelled.

To prevent queries on the standby node from being cancelled due to replication conflicts, you can set hot_standby_feedback to on, which will make the standby inform the primary about the oldest transaction running on it. As a result, the primary can avoid cleaning up rows which are still being used by transactions on the standby.

However, setting hot_standby_feedback to on also means that long running queries on the standby have the capability to block rows from getting cleaned up on the primary.

List long running transactions on standby

To find out the xmin of all standby servers, you can run the following query on the primary server:

SELECT pid, application_name, client_addr, state, backend_xmin
FROM pg_stat_replication
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

Once the standby is identified, you can find the query holding back the xmin horizon and its connection’s pid in that standby by running the following command:

SELECT pid, datname, usename, state, backend_xmin, backend_xid
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL OR backend_xid IS NOT NULL
ORDER BY greatest(age(backend_xmin), age(backend_xid)) DESC;

To avoid having excessive bloat on the primary due to long-running transactions on the standby, you can take one of the following approaches:

• Continue dealing with replication conflicts and set hot_standby_feedback to off.
• Set vacuum_defer_cleanup_age to a higher value—in order to defer cleaning up rows on the primary until vacuum_defer_cleanup_age transactions have passed, giving more time to standby queries to complete without running into replication conflicts.
• Lastly, you can also track and terminate long running queries on the standby like we discussed for the primary in the long running transactions tutorial.

See more

PostgreSQL Monitoring