Fetching latest commit…
Cannot retrieve the latest commit at this time.
|Failed to load latest commit information.|
Collecting executed queries =========================== This PostgreSQL extension allows you to collect all executed queries into a file. So that you can analyze or replay them later. Most of the code that interacts directly with the executor comes from the auto_explain and pg_stat_statements extensions (hooks, shared memory management etc). Install ------- Installing the extension is quite simple, especially if you're on 9.1. In that case all you need to do is this: $ make install and the (after connecting to the database) db=# CREATE EXTENSION query_recorder; If you're on pre-9.1 version, you'll have to do the second part manually by running the SQL script (query_recorder--x.y.sql) in the database. If needed, replace MODULE_PATHNAME by $libdir. Config ------ Now the extension is installed, but you still need to load the shared module. This needs to be done from postgresql.conf, as the module needs to allocate space in the shared memory segment. So add this to the config file (or update the current values) # libraries to load shared_preload_libraries = 'query_recorder' # known GUC prefixes custom_variable_classes = 'query_recorder' # config of the query histogram query_recorder.filename = '/tmp/queries.log' query_recorder.max_files = 100 query_recorder.size_limit = 1GB query_recorder.buffer_size = 8MB query_recorder.enabled = false query_recorder.normalize = false The meaning of those config options is this: (a) query_recorder.filename - filename where to store the recorded queries (the file number will be appended to the filename) (b) query_recorder.max_files - number of files to rotate (the number will be appended to the filename) (c) query_recorder.size_limit - size limit for each file (d) query_recorder.buffer_size - size of the buffer used to store the queries before writing them to the file (e) query_recorder.enabled - is the recording enabled or disabled (f) query_recorder.normalize - replace EOL with a space? By default, the recording is disabled - you have to enable it, either in postgresql.conf or by setting it later SET query_recorder.enabled = true and later disable it by SET query_recorder.enabled = false So you may enable/disable the query recording as needed. The queries will be written to the file, and every time the file reaches the size limit, a new file (with an incremented sequence number) will be created. So for example if you set the filename like this query_recorder.filename = '/tmp/queries.log' then the first file will be '/tmp/queries.log.000', when it reaches the size limit a file '/tmp/queries.log.001' will be created etc. up to '/tmp/queries.log.999' (or whatever limit you've set using max_files). When the last file is filled, the first one (.000) is recycled etc. Reading the file ---------------- The file is quite simple to read and process. There are five fields - timestamp (with microseconds) - ID of the backend - duration of the query - length of the query (number of characters) - query (may include EOL, use the previous field to parse it) Don't expect the queries to be exactly in the order as executed, i.e. sorted by the first field. Right now this is true, but in the future this may change due to performance optimizations.