The auto_dump module is a Postgres Pro extension that is designed to collect data on long-running and problematic queries and to further reproduce these problems for troubleshooting.
To simplify reproducing problematic queries, this extension generates a dump file containing the following entities:
CREATE TABLE statements for both temporary and
permanent tables referenced in the query.
INSERT/COPY statements to populate
the tables with data available during the original problematic query
execution.
The original problematic SQL query along with its execution plan,
which can be generated by EXPLAIN,
EXPLAIN ANALYZE, or both.
The auto_dump extension is included into Postgres Pro Standard. Once you have Postgres Pro Standard installed, complete the following steps to enable auto_dump:
Add auto_dump to the
shared_preload_libraries parameter in the
postgresql.conf file:
shared_preload_libraries = 'auto_dump'
As auto_dump is disabled by default, enable it:
auto_dump.enable = on
Before using the extension, set other required configuration parameters and restart the database server for the changes to take effect.
The auto_dump extension provides the following configuration parameters for managing automatic query dumping parameters.
auto_dump.enable (boolean) #Enables the extension. This parameter is disabled by default.
auto_dump.output_directory (string) #Specifies the file system path to the directory where table dump files are saved. This is a mandatory parameter. The operating system user the service run as must have read-write permissions for this directory.
The following configuration parameters control the conditions that trigger automatic query dumps.
auto_dump.dump_on_query_string (string) #
Specifies a case-insensitive fragment of an SQL query. If this
fragment appears anywhere in a query, the query is dumped. The
default is an empty string (''), which means that
no query is dumped.
auto_dump.dump_on_cancel (boolean) #
Controls whether a dump is triggered for queries that are canceled
by the DBMS, for example, by the pg_cancel_backend
function or due to a lock timeout.
Note that auto_dump.dump_on_cancel does not
trigger dumps for sessions terminated due to
idle_in_transaction_session_timeout,
idle_session_timeout, or
transaction_timeout.
This parameter is disabled by default.
auto_dump.dump_on_bad_plan (boolean) #Analyzes the expected and actual row counts for all queries. It uses two thresholds to identify problematic queries: auto_dump.bad_plan_count_threshold and auto_dump.bad_plan_percent_threshold. An automatic dump is triggered for a query only when the difference in row counts exceeds both thresholds simultaneously.
This parameter is disabled by default.
auto_dump.bad_plan_count_threshold (integer) #Defines a trigger condition for automatic dumps based on the absolute difference between the expected and actual row counts.
All nodes of the execution plan are analyzed. The condition is considered satisfied if the difference between the expected and actual row counts exceeds this parameter value.
If the value is set to 0, the condition
is always considered satisfied. The default value is
1000000.
auto_dump.bad_plan_percent_threshold (integer) #
Defines a trigger condition for automatic dumps based on the
percentage difference between the expected and actual row counts.
The possible value is an integer between 0 and
100 (default).
All nodes of the execution plan are analyzed. For each node, the percentage difference between expected and actual row counts is checked. The condition is considered satisfied if this difference exceeds this parameter value.
If this value is set to 0, the condition
is always considered satisfied.
auto_dump.dump_on_time (boolean) #Saves a dump for a query if its execution time exceeds the auto_dump.timeout value. This parameter is disabled by default.
auto_dump.timeout (integer) #
Specifies the timeout value for auto_dump.dump_on_time
in milliseconds. Possible values are integers greater than or equal
to 0 (default). If the value is set to
0, every query is dumped.
If multiple trigger condition control parameters are specified, they are processed in the following order:
auto_dump.dump_on_cancel
auto_dump.dump_on_query_string
auto_dump.dump_on_bad_plan
auto_dump.dump_on_time
The following configuration parameters control the scope and content of automatic dumps.
auto_dump.dump_temporary_tables (boolean) #Writes session temporary tables used by the current query to the dump. This parameter is disabled by default.
auto_dump.dump_persistent_tables (boolean) #Writes all persistent tables used by the current query to the dump. This parameter is disabled by default.
auto_dump.dump_all_temp_tables (boolean) #Writes all session temporary tables, including those used by the current query, to the dump. This parameter is disabled by default.
auto_dump.dump_data (boolean) #Writes the table content to the dump. When disabled, the SQL query only includes table creation commands without populating them with data. This parameter is disabled by default.
Enable this parameter together with auto_dump.dump_temporary_tables or auto_dump.dump_persistent_tables to create an SQL file with table contents.
auto_dump.dump_indexes (boolean) #Writes index creation commands for tables to the dump. This parameter is disabled by default.
auto_dump.dump_copy_data (boolean) #Specifies the method for dumping table data.
If enabled, dumps table data using the COPY TO
command. As a result, a separate TXT file for each table is created,
which is referenced by a COPY ... FROM statement
in the dump file.
If disabled, dumps table data as INSERT statements
in the dump file containing all table values.
This parameter is disabled by default.
auto_dump.dump_query (boolean) #Writes the SQL query for which the dump is created to the dump. This parameter is disabled by default.
auto_dump.dump_create (boolean) #Writes SQL commands for creating the tables being dumped to the dump. This parameter is disabled by default.
auto_dump.dump_plan (boolean) #Writes the execution plan of the current SQL query to the dump. This parameter is disabled by default.
Use auto_dump with caution. This extension is designed as a debugging tool and cannot be used for continuous monitoring. Thus, it is not recommended to configure auto_dump to dump every query.
auto_dump operation can cause conflicts, particularly when dumping temporary tables or data. Consider the following possible issues:
The auto_dump extension is incompatible
with the PREPARE TRANSACTION command when working
with temporary tables. A check for operations on temporary objects
occurs earlier than the check for enabled prepared transactions, so an
error occurs even if max_prepared_transactions is
correctly configured.
When auto_dump.dump_all_temp_tables is enabled,
auto_dump cannot access temporary
namespaces from autonomous transactions. A corresponding error message
is raised in this case.
The performance statistics can also be affected. When auto_dump.dump_data is enabled, the extension reads table data to generate dumps and thus artificially inflates the number of sequential scans, which leads to inaccurate statistics.