Postgres Pro Standard : Documentation: 14: F.5. auto

F.5. auto_dump
Prev	Up	Appendix F. Additional Supplied Modules and Extensions Shipped in `postgrespro-std-14-contrib`	Home	Next

F.5. auto_dump

F.5.1. Installation and Configuration
F.5.2. Configuration Parameters
F.5.3. Limitations and Considerations

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.

F.5.1. Installation and Configuration

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.

F.5.2. Configuration Parameters

The auto_dump extension provides the following configuration parameters for managing automatic query dumping parameters.

F.5.2.1. General Configuration 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.

F.5.2.2. Trigger Condition Control Parameters

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 or idle_session_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

F.5.2.3. Dump Content Control Parameters

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.

F.5.3. Limitations and Considerations

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.

Prev	Up	Next
F.4. auth_delay	Home	F.6. auto_explain

epub pdf