Highlighted
Micro Focus Expert
Micro Focus Expert
248 views

(SP/Propel) Support Tip: how to capture PostgreSQL's query in SMSP and Propel

Service Manager SP and Propel have PostgreSQL as embedded database. In Service Manager, we can catch SQL queries by parameter, sqldebug, however, there is no such a parameter in SMSP and Propel because it is composed of multiple microsevices.

This document explains how to capture PostgreSQL's query in SMSP and Propel.
In SMSP and Propel, we catch SQL queries directly from PostgreSQL DB.

1. backup & modify postgres configuration
% cp /var/lib/pgsql/9.4/data/postgresql.conf /var/lib/pgsql/9.4/data/postgresql.conf.bk1
% vi /var/lib/pgsql/9.4/data/postgresql.conf
# Update
log_statement = 'all' # none, ddl, mod, all
log_line_prefix = '%m %c %l%d %a %u ' # Author's suggestion based on testing
log_min_duration_statement = 3000 # unit: milisec, 3000 for queries(>3 sec) , 0 for all queries
log_min_messages = info # for gather query and binding values
// detail of paramters (from OOB files)
log_statement = 'none' # none, ddl, mod, all
log_line_prefix = '< %m >' # special values:
# %a = application name
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = process ID
# %t = timestamp without milliseconds
# %m = timestamp with milliseconds
# %i = command tag
# %e = SQL state
# %c = session ID
# %l = session line number
# %s = session start timestamp
# %v = virtual transaction ID
# %x = transaction ID (0 if none)
# %q = stop here in non-session
# processes
# %% = '%'
# e.g. '<%u%%%d> '
#log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements
# and their durations, > 0 logs only
# statements running at least this number
#log_min_messages = warning # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic

2. reload postgres configuration
% service postgresql-9.4 reload

3. tail log & do any actions in SMSP and Propel
- goto postgres' log folder
ex) /var/lib/pgsql/9.4/data/pg_log
- recognize current logfile's name
ex) postgresql-Wed.log
- start to catch log
% tail -f postgresql-Wed.log 2>&1 | tee capture-query.log

- do any action in SMSP and Propel for capuring queries
- stop to catch log ( clicking CTRL-C )
-> stop execution of "tail -f postgresql-Wed.log 2>&1 | tee capture-query.log"

4. restore & reload postgres configuration
% cp /var/lib/pgsql/9.4/data/postgresql.conf /var/lib/pgsql/9.4/data/postgresql.conf.test1
% cp /var/lib/pgsql/9.4/data/postgresql.conf.bk1 /var/lib/pgsql/9.4/data/postgresql.conf
% service postgresql-9.4 reload

5. review queries in captured log, capture-query.log
# in order to check index information per each tables, use '\d {table_name}' in psql,

6. sample log captured 
- acutal query executed: select sum(numbackends) from pg_stat_database;
- postgres's option : log_line_prefix = '%a %u %d %r %h %p %t %m %i %e %c %l %s %v %x %q'
- captured postgres log
psql postgres postgres [local] [local] 15293 2016-09-28 23:16:06 MDT 2016-09-28 23:16:06.101 MDT idle 00000 57ec9f86.3bbd 5 2016-09-28 22:58:46 MDT 2/1365 0 LOG: statement: select sum(numbackends) from pg_stat_database;
psql postgres postgres [local] [local] 15293 2016-09-28 23:16:06 MDT 2016-09-28 23:16:06.101 MDT SELECT 00000 57ec9f86.3bbd 6 2016-09-28 22:58:46 MDT 2/0 0 LOG: duration: 0.425 ms

Labels (1)
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.