gp toolkit schema views to view Greenplum database server log files

posted Apr 28, 2017, 3:43 PM by Sachchida Ojha
There are 4 views in the gp_toolkit schema that can help you to view the greenplum database server log files.

1. gp_log_command_timings
2. gp_log_database
3. gp_log_master_concise
4. gp_log_system

Each component of a Greenplum Database system (master, standby master, primary segments, and mirror segments) keeps its own server log files. The gp_log_* family of views allows you to issue SQL queries against the server log files to find particular entries of interest. The use of these views require superuser permissions.

1. gp_log_command_timings

gpadmin=# \d gp_toolkit.gp_log_command_timings
      View "gp_toolkit.gp_log_command_timings"
   Column    |           Type           | Modifiers 
-------------+--------------------------+-----------
 logsession  | text                     | 
 logcmdcount | text                     | 
 logdatabase | text                     | 
 loguser     | text                     | 
 logpid      | text                     | 
 logtimemin  | timestamp with time zone | 
 logtimemax  | timestamp with time zone | 
 logduration | interval                 | 
View definition:
 SELECT __gp_log_master_ext.logsession, __gp_log_master_ext.logcmdcount, __gp_log_master_ext.logdatabase, __gp_log_master_ext.loguser, __gp_log_master_ext.logpid, min(__gp_log_master_ext.logtime) AS logtimemin, max(__gp_log_master_ext.logtime) AS logtimemax, max(__gp_log_master_ext.logtime) - min(__gp_log_master_ext.logtime) AS logduration
   FROM ONLY gp_toolkit.__gp_log_master_ext
  WHERE __gp_log_master_ext.logsession IS NOT NULL AND __gp_log_master_ext.logcmdcount IS NOT NULL AND __gp_log_master_ext.logdatabase IS NOT NULL
  GROUP BY __gp_log_master_ext.logsession, __gp_log_master_ext.logcmdcount, __gp_log_master_ext.logdatabase, __gp_log_master_ext.loguser, __gp_log_master_ext.logpid;

logsession=>The session identifier (prefixed with "con").
logcmdcount=>The command number within a session (prefixed with "cmd").
logdatabase=>The name of the database.
loguser=>The name of the database user.
logpid=>The process id (prefixed with "p").
logtimemin=>The time of the first log message for this command.
logtimemax=>The time of the last log message for this command.
logduration=>Statement duration from start to end time.

This view uses an external table to read the log files on the master and report the execution time of SQL commands executed in a database session. The use of this view requires superuser permissions.

gpadmin=# select * from gp_toolkit.gp_log_command_timings;
logsession | logcmdcount | logdatabase | loguser | logpid | logtimemin | logtimemax | logduration 
------------+-------------+-------------+---------+--------+-------------------------------+-------------------------------+-----------------
con7 | cmd2702 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:44.045136-05 | 2014-01-18 02:16:44.045136-05 | 00:00:00
con7 | cmd989 | sachi | gpadmin | p10699 | 2013-12-12 12:17:59.834766-05 | 2013-12-12 12:17:59.834766-05 | 00:00:00
con7 | cmd413 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:42.856334-05 | 2014-01-18 02:16:42.856334-05 | 00:00:00
con7 | cmd2734 | gpadmin | gpadmin | p30158 | 2014-01-18 02:23:55.498894-05 | 2014-01-18 02:23:55.498894-05 | 00:00:00
con7 | cmd1546 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.459633-05 | 2014-01-18 02:16:43.459633-05 | 00:00:00
con12 | cmd31 | sachi | gpadmin | p10110 | 2013-11-29 08:40:27.041448-05 | 2013-11-29 08:40:27.07172-05 | 00:00:00.030272
con7 | cmd2742 | gpadmin | gpadmin | p30158 | 2014-01-19 19:32:48.948112-05 | 2014-01-19 19:32:48.948112-05 | 00:00:00
con7 | cmd1817 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.603791-05 | 2014-01-18 02:16:43.603791-05 | 00:00:00
con7 | cmd1136 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.249678-05 | 2014-01-18 02:16:43.249678-05 | 00:00:00
con7 | cmd985 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.179408-05 | 2014-01-18 02:16:43.179408-05 | 00:00:00
con7 | cmd3900 | gpadmin | gpadmin | p30158 | 2014-01-20 14:55:51.318356-05 | 2014-01-20 14:55:51.318356-05 | 00:00:00
con7 | cmd4539 | gpadmin | gpadmin | p30158 | 2014-01-20 14:58:13.253164-05 | 2014-01-20 14:58:13.253164-05 | 00:00:00
con9 | cmd99 | postgres | gpadmin | p18553 | 2013-11-27 12:54:23.82547-05 | 2013-11-27 12:54:23.82547-05 | 00:00:00
con7 | cmd4353 | gpadmin | gpadmin | p30158 | 2014-01-20 14:56:42.753756-05 | 2014-01-20 14:56:42.753756-05 | 00:00:00
con7 | cmd4104 | gpadmin | gpadmin | p30158 | 2014-01-20 14:56:14.832069-05 | 2014-01-20 14:56:14.832069-05 | 00:00:00
con7 | cmd469 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:42.88348-05 | 2014-01-18 02:16:42.88348-05 | 00:00:00
con8 | cmd4 | template1 | gpadmin | p18422 | 2013-11-27 12:54:22.644063-05 | 2013-11-27 12:54:22.644063-05 | 00:00:00
con7 | cmd2615 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.990813-05 | 2014-01-18 02:16:43.990813-05 | 00:00:00
con7 | cmd3716 | gpadmin | gpadmin | p30158 | 2014-01-20 14:55:49.594669-05 | 2014-01-20 14:55:49.594669-05 | 00:00:00
con7 | cmd182 | sachi | gpadmin | p10699 | 2013-12-12 11:32:29.719597-05 | 2013-12-12 11:32:29.719597-05 | 00:00:00
con7 | cmd4161 | gpadmin | gpadmin | p30158 | 2014-01-20 14:56:14.884239-05 | 2014-01-20 14:56:14.884239-05 | 00:00:00
con7 | cmd4108 | gpadmin | gpadmin | p30158 | 2014-01-20 14:56:14.839272-05 | 2014-01-20 14:56:14.839272-05 | 00:00:00
con7 | cmd3306 | gpadmin | gpadmin | p30158 | 2014-01-20 14:55:03.666139-05 | 2014-01-20 14:55:03.666139-05 | 00:00:00
con7 | cmd2040 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.702362-05 | 2014-01-18 02:16:43.702362-05 | 00:00:00
con7 | cmd665 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.003611-05 | 2014-01-18 02:16:43.003611-05 | 00:00:00
con11 | cmd2 | template1 | gpadmin | p3977 | 2013-11-28 11:19:39.516423-05 | 2013-11-28 11:19:39.865829-05 | 00:00:00.349406
con7 | cmd762 | sachi | gpadmin | p10699 | 2013-12-12 12:17:28.520531-05 | 2013-12-12 12:17:28.520531-05 | 00:00:00
con7 | cmd2563 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.963477-05 | 2014-01-18 02:16:43.963477-05 | 00:00:00
con7 | cmd1146 | sachi | gpadmin | p10699 | 2013-12-12 12:17:59.926756-05 | 2013-12-12 12:17:59.926756-05 | 00:00:00
con7 | cmd322 | sachi | gpadmin | p10699 | 2013-12-12 12:16:36.266479-05 | 2013-12-12 12:16:36.266479-05 | 00:00:00
con7 | cmd67 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:42.649847-05 | 2014-01-18 02:16:42.649847-05 | 00:00:00
con7 | cmd66 | sachi | gpadmin | p10699 | 2013-12-12 11:32:29.641545-05 | 2013-12-12 11:32:29.641545-05 | 00:00:00
con7 | cmd372 | sachi | gpadmin | p10699 | 2013-12-12 12:16:36.286566-05 | 2013-12-12 12:16:36.286566-05 | 00:00:00
con7 | cmd639 | sachi | gpadmin | p10699 | 2013-12-12 12:17:25.565363-05 | 2013-12-12 12:17:25.565363-05 | 00:00:00
con7 | cmd729 | sachi | gpadmin | p10699 | 2013-12-12 12:17:28.504424-05 | 2013-12-12 12:17:28.504424-05 | 00:00:00
con7 | cmd344 | sachi | gpadmin | p10699 | 2013-12-12 12:16:36.274324-05 | 2013-12-12 12:16:36.274324-05 | 00:00:00
con7 | cmd3269 | gpadmin | gpadmin | p30158 | 2014-01-20 14:55:03.65272-05 | 2014-01-20 14:55:03.65272-05 | 00:00:00
con7 | cmd5 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:42.534074-05 | 2014-01-18 02:16:42.534074-05 | 00:00:00
con7 | cmd775 | sachi | gpadmin | p10699 | 2013-12-12 12:17:28.524123-05 | 2013-12-12 12:17:28.524123-05 | 00:00:00
con11 | cmd20 | template1 | gpadmin | p3977 | 2013-11-28 11:19:40.355456-05 | 2013-11-28 11:19:40.384454-05 | 00:00:00.028998
con7 | cmd1741 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.56654-05 | 2014-01-18 02:16:43.56654-05 | 00:00:00
con7 | cmd707 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.022648-05 | 2014-01-18 02:16:43.022648-05 | 00:00:00
con7 | cmd4448 | gpadmin | gpadmin | p30158 | 2014-01-20 14:56:42.789864-05 | 2014-01-20 14:56:42.789864-05 | 00:00:00
con12 | cmd1 | sachi | gpadmin | p10110 | 2013-11-29 08:40:26.132038-05 | 2013-11-29 08:40:26.132038-05 | 00:00:00
con7 | cmd1228 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.294588-05 | 2014-01-18 02:16:43.294588-05 | 00:00:00
....
con7 | cmd711 | sachi | gpadmin | p10699 | 2013-12-12 12:17:28.49396-05 | 2013-12-12 12:17:28.49396-05 | 00:00:00
con7 | cmd322 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:42.814124-05 | 2014-01-18 02:16:42.814124-05 | 00:00:00
con7 | cmd3627 | gpadmin | gpadmin | p30158 | 2014-01-20 14:55:49.538092-05 | 2014-01-20 14:55:49.538092-05 | 00:00:00
con7 | cmd3895 | gpadmin | gpadmin | p30158 | 2014-01-20 14:55:51.315797-05 | 2014-01-20 14:55:51.315797-05 | 00:00:00
con7 | cmd1026 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.195032-05 | 2014-01-18 02:16:43.195032-05 | 00:00:00
con7 | cmd4012 | gpadmin | gpadmin | p30158 | 2014-01-20 14:55:51.353741-05 | 2014-01-20 14:55:51.353741-05 | 00:00:00
con7 | cmd875 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.111861-05 | 2014-01-18 02:16:43.111861-05 | 00:00:00
con7 | cmd1961 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.665132-05 | 2014-01-18 02:16:43.665132-05 | 00:00:00
con7 | cmd348 | sachi | gpadmin | p10699 | 2013-12-12 12:16:36.275108-05 | 2013-12-12 12:16:36.275108-05 | 00:00:00
con7 | cmd446 | sachi | gpadmin | p10699 | 2013-12-12 12:16:36.314844-05 | 2013-12-12 12:16:36.314844-05 | 00:00:00
con7 | cmd2611 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.99029-05 | 2014-01-18 02:16:43.99029-05 | 00:00:00
con7 | cmd267 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:42.790117-05 | 2014-01-18 02:16:42.790117-05 | 00:00:00
con7 | cmd3564 | gpadmin | gpadmin | p30158 | 2014-01-20 14:55:07.075346-05 | 2014-01-20 14:55:07.075346-05 | 00:00:00
con9 | cmd41 | postgres | gpadmin | p18553 | 2013-11-27 12:54:23.616744-05 | 2013-11-27 12:54:23.616744-05 | 00:00:00
con7 | cmd2842 | gpadmin | gpadmin | p30158 | 2014-01-19 20:37:23.957804-05 | 2014-01-19 20:37:23.957804-05 | 00:00:00
con7 | cmd561 | sachi | gpadmin | p10699 | 2013-12-12 12:17:25.533968-05 | 2013-12-12 12:17:25.533968-05 | 00:00:00
(3232 rows)


gpadmin=# 


2. gp_log_database

gpadmin=# \d gp_toolkit.gp_log_database
View "gp_toolkit.gp_log_database"
Column | Type | Modifiers 
----------------+--------------------------+-----------
logtime | timestamp with time zone | 
loguser | text | 
logdatabase | text | 
logpid | text | 
logthread | text | 
loghost | text | 
logport | text | 
logsessiontime | timestamp with time zone | 
logtransaction | integer | 
logsession | text | 
logcmdcount | text | 
logsegment | text | 
logslice | text | 
logdistxact | text | 
loglocalxact | text | 
logsubxact | text | 
logseverity | text | 
logstate | text | 
logmessage | text | 
logdetail | text | 
loghint | text | 
logquery | text | 
logquerypos | integer | 
logcontext | text | 
logdebug | text | 
logcursorpos | integer | 
logfunction | text | 
logfile | text | 
logline | integer | 
logstack | text | 
View definition:
SELECT gp_log_system.logtime, gp_log_system.loguser, gp_log_system.logdatabase, gp_log_system.logpid, gp_log_system.logthread, gp_log_system.loghost, gp_log
_system.logport, gp_log_system.logsessiontime, gp_log_system.logtransaction, gp_log_system.logsession, gp_log_system.logcmdcount, gp_log_system.logsegment, g
p_log_system.logslice, gp_log_system.logdistxact, gp_log_system.loglocalxact, gp_log_system.logsubxact, gp_log_system.logseverity, gp_log_system.logstate, gp
_log_system.logmessage, gp_log_system.logdetail, gp_log_system.loghint, gp_log_system.logquery, gp_log_system.logquerypos, gp_log_system.logcontext, gp_log_s
ystem.logdebug, gp_log_system.logcursorpos, gp_log_system.logfunction, gp_log_system.logfile, gp_log_system.logline, gp_log_system.logstack
FROM gp_toolkit.gp_log_system
WHERE gp_log_system.logdatabase = current_database()::text

logtime=>The timestamp of the log message.
loguser=>The name of the database user.
logdatabase=>The name of the database.
logpid=>The associated process id (prefixed with "p").
logthread=>The associated thread count (prefixed with "th").
loghost=>The segment or master host name.
logport=>The segment or master port.
logsessiontime=>Time session connection was opened.
logtransaction=>Global transaction id.
logsession=>The session identifier (prefixed with "con").
logcmdcount=>The command number within a session (prefixed with "cmd").
logsegment=>The segment content identifier (prefixed with "seg" for primary or "mir" for mirror. The master always has a content id of -1).
logslice=>The slice id (portion of the query plan being executed).
logdistxact=>Distributed transaction id.
loglocalxact=>Local transaction id.
logsubxact=>Subtransaction id.
logseverity=>LOG, ERROR, FATAL, PANIC, DEBUG1 or DEBUG2.
logstate=>SQL state code associated with the log message.
logmessage=>Log or error message text.
logdetail=>Detail message text associated with an error message.
loghint=>Hint message text associated with an error message.
logquery=>The internally-generated query text.
logquerypos=>The cursor index into the internally-generated query text.
logcontext=>The context in which this message gets generated.
logdebug=>Query string with full detail for debugging.
logcursorpos=>The cursor index into the query string.
logfunction=>The function in which this message is generated.
logfile=>The log file in which this message is generated.
logline=>The line in the log file in which this message is generated.
logstack=>Full text of the stack trace associated with this message.

This view uses an external table to read the server log files of the entire Greenplum system (master, segments, and mirrors) and lists log entries associated with the current database. Associated log entries can be identified by the session id (logsession) and command id (logcmdcount). 
The use of this view requires superuser permissions.

gpadmin=# select * from gp_toolkit.gp_log_database limit 10;
logtime | loguser | logdatabase | logpid | logthread | loghost | logport | logsessiontime | logtransaction | logsessi
on | logcmdcount | logsegment | logslice | logdistxact | loglocalxact | logsubxact | logseverity | logstate | 
logmessage | logdetail | loghint | logquery | logquerypos | logcontext | 
logdebug | logcursorpos | logfunction | logfile | logline | logstack 
-------------------------------+---------+-------------+--------+---------------+--------------+---------+------------------------+----------------+---------
---+-------------+------------+----------+-------------+--------------+------------+-------------+----------+------------------------------------------------
----------------------------------------------------------------------------+-----------+---------+----------+-------------+------------+--------------------
---------------------------------------------------------------------------------------------+--------------+-------------+------------+---------+----------
2013-11-27 12:55:25.721427-05 | gpadmin | gpadmin | p19653 | th1344132864 | [local] | | 2013-11-27 12:55:25-05 | 0 | con11 
| | seg-1 | | | | | FATAL | 3D000 | database "gpadmin" does not exist 
| | | | | | 
| 0 | | postinit.c | 404 | 
2013-11-27 14:20:29.534951-05 | gpadmin | gpadmin | p26167 | th1318504192 | [local] | | 2013-11-27 14:20:19-05 | 1111 | con8 
| cmd1 | seg-1 | | dx3 | x1111 | sx1 | LOG | 00000 | statement: alter role sachi login; 
| | | | | | alter role sachi lo
gin; | 0 | | postgres.c | 1542 | 
2013-11-27 14:20:29.542075-05 | gpadmin | gpadmin | p26171 | th-1635629312 | 192.168.1.13 | 59892 | 2013-11-27 14:20:29-05 | 975 | con8 
| | seg0 | | | x975 | sx1 | LOG | 00000 | binding to 192.168.1.13 only 
| | | | | | 
| 0 | | ic_tcp.c | 230 | 
2013-11-27 14:20:29.549326-05 | gpadmin | gpadmin | p26173 | th-460380416 | 192.168.1.13 | 43424 | 2013-11-27 14:20:29-05 | 975 | con8 
| | seg1 | | | x975 | sx1 | LOG | 00000 | binding to 192.168.1.13 only 
| | | | | | 
| 0 | | ic_tcp.c | 230 | 
2013-11-27 14:21:12.910452-05 | gpadmin | gpadmin | p26167 | th1318504192 | [local] | | 2013-11-27 14:20:19-05 | 1113 | con8 
| cmd2 | seg-1 | | dx4 | x1113 | sx1 | LOG | 00000 | statement: CREATE RESOURCE QUEUE perf_test ACTI
VE THRESHOLD 2 COST THRESHOLD 100000000 IGNORE THRESHOLD 100000 OVERCOMMIT; | | | | | | CREATE RESOURCE QUE
UE perf_test ACTIVE THRESHOLD 2 COST THRESHOLD 100000000 IGNORE THRESHOLD 100000 OVERCOMMIT; | 0 | | postgres.c | 1542 | 
2013-11-27 14:21:12.914221-05 | gpadmin | gpadmin | p26208 | th-1635629312 | 192.168.1.13 | 59897 | 2013-11-27 14:21:12-05 | 978 | con8 
| | seg0 | | | x978 | sx1 | LOG | 00000 | binding to 192.168.1.13 only 
| | | | | | 
| 0 | | ic_tcp.c | 230 | 
2013-11-27 14:21:12.91816-05 | gpadmin | gpadmin | p26210 | th-460380416 | 192.168.1.13 | 43429 | 2013-11-27 14:21:12-05 | 978 | con8 
| | seg1 | | | x978 | sx1 | LOG | 00000 | binding to 192.168.1.13 only 
| | | | | | 
| 0 | | ic_tcp.c | 230 | 
2013-11-28 08:41:19.141128-05 | gpadmin | gpadmin | p29778 | th1318504192 | [local] | | 2013-11-28 08:41:06-05 | 1115 | con10 
| cmd1 | seg-1 | | dx5 | x1115 | sx1 | LOG | 00000 | statement: SELECT n.nspname AS "Name", 
| | | | | | SELECT n.nspname AS
"Name", | 0 | | postgres.c | 1542 | 

: pg_catalog.pg_get_userbyid(n.nspowner) AS "Ow
ner" : pg_catalog.pg_get
_userbyid(n.nspowner) AS "Owner" 

: FROM pg_catalog.pg_namespace n 
: FROM pg_catalog.pg_
namespace n 

: WHERE (n.nspname !~ '^pg_temp_' OR 
: WHERE (n.nspname 
!~ '^pg_temp_' OR 

: n.nspname = (pg_catalog.curren
t_schemas(true))[1]) : n.
nspname = (pg_catalog.current_schemas(true))[1]) 

: ORDER BY 1; 
: ORDER BY 1; 

2013-11-29 08:42:14.328884-05 | gpadmin | gpadmin | p10441 | th1318504192 | [local] | | 2013-11-29 08:42:06-05 | 1188 | con14 
| cmd1 | seg-1 | | dx74 | x1188 | sx1 | LOG | 00000 | statement: select version(); 
| | | | | | select version(); 
| 0 | | postgres.c | 1542 | 
2014-01-18 02:16:42.3651-05 | gpadmin | gpadmin | p30158 | th39933696 | [local] | | 2014-01-18 02:16:25-05 | 1379 | con7 
| cmd1 | seg-1 | | dx3 | x1379 | sx1 | LOG | 00000 | statement: SELECT c.oid, 
| | | | | | SELECT c.oid, 
| 0 | | postgres.c | 1542 | 

: n.nspname, 
: n.nspname, 


: c.relname 
: c.relname 


: FROM pg_catalog.pg_class c 
: FROM pg_catalog.pg_
class c 

: LEFT JOIN pg_catalog.pg_namespace n ON n.o
id = c.relnamespace : LEFT JOIN pg_c
atalog.pg_namespace n ON n.oid = c.relnamespace 

: WHERE n.nspname ~ '^(pg_catalog)#39; 
: WHERE n.nspname ~ '
^(pg_catalog)#39; 

: ORDER BY 2, 3; 
: ORDER BY 2, 3; 

(10 rows)

gpadmin=# 

3. gp_log_master_concise

gpadmin=# \d gp_toolkit.gp_log_master_concise
View "gp_toolkit.gp_log_master_concise"
Column | Type | Modifiers 
-------------+--------------------------+-----------
logtime | timestamp with time zone | 
logdatabase | text | 
logsession | text | 
logcmdcount | text | 
logseverity | text | 
logmessage | text | 
View definition:
SELECT __gp_log_master_ext.logtime, __gp_log_master_ext.logdatabase, __gp_log_master_ext.logsession, __gp_log_master_ext.logcmdcount, __gp_log_master_ext.logseverity, __gp_log_master_ext.logmessage
FROM ONLY gp_toolkit.__gp_log_master_ext;

logtime=>The timestamp of the log message.
logdatabase=>The name of the database.
logsession=>The session identifier (prefixed with "con").
logcmdcount=>The command number within a session (prefixed with "cmd").
logmessage=>Log or error message text.

This view uses an external table to read a subset of the log fields from the master log file. 
The use of this view requires superuser permissions.

gpadmin=# select * from gp_toolkit.gp_log_master_concise limit 10;
logtime | logdatabase | logsession | logcmdcount | logseverity | 
logmessage 
-------------------------------+-------------+------------+-------------+-------------+----------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
2013-11-27 12:51:33.08036-05 | | | | LOG | database system was shut down at 2013-11-27 12:51:32 EST
2013-11-27 12:51:33.080475-05 | | | | LOG | checkpoint record is at 0/8ABA78
2013-11-27 12:51:33.080507-05 | | | | LOG | redo record is at 0/8ABA78; undo record is at 0/0; shutdown TRUE
2013-11-27 12:51:33.08053-05 | | | | LOG | next transaction ID: 0/902; next OID: 10901
2013-11-27 12:51:33.080551-05 | | | | LOG | next MultiXactId: 1; next MultiXactOffset: 0
2013-11-27 12:51:33.08059-05 | | | | LOG | end of transaction log location is 0/8ABAF0
2013-11-27 12:51:33.081056-05 | | | | LOG | Oldest active transaction from prepared transactions 902
2013-11-27 12:51:33.346109-05 | | | | LOG | database system is ready
2013-11-27 12:51:33.346179-05 | | | | LOG | PostgreSQL 8.2.15 (Greenplum Database 4.2.2.4 build 1 Community Editi
on) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Oct 17 2012 11:48:43
2013-11-27 12:51:33.432896-05 | | | | LOG | Finished normal startup for clean shutdown case
(10 rows)

gpadmin=#

4. gp_log_system

gpadmin=# \d gp_toolkit.gp_log_system
View "gp_toolkit.gp_log_system"
Column | Type | Modifiers 
----------------+--------------------------+-----------
logtime | timestamp with time zone | 
loguser | text | 
logdatabase | text | 
logpid | text | 
logthread | text | 
loghost | text | 
logport | text | 
logsessiontime | timestamp with time zone | 
logtransaction | integer | 
logsession | text | 
logcmdcount | text | 
logsegment | text | 
logslice | text | 
logdistxact | text | 
loglocalxact | text | 
logsubxact | text | 
logseverity | text | 
logstate | text | 
logmessage | text | 
logdetail | text | 
loghint | text | 
logquery | text | 
logquerypos | integer | 
logcontext | text | 
logdebug | text | 
logcursorpos | integer | 
logfunction | text | 
logfile | text | 
logline | integer | 
logstack | text | 
View definition:
SELECT __gp_log_segment_ext.logtime, __gp_log_segment_ext.loguser, __gp_log_segment_ext.logdatabase, __gp_log_segment_ext.logpid, __gp_log_segment_ext.logthread, __gp_log_segment_ext.loghost, __gp_log_segment_ext.logport, __gp_log_segment_ext.logsessiontime, __gp_log_segment_ext.logtransaction, __gp_log_segment_ext.logsession, __gp_log_segment_ext.logcmdcount, __gp_log_segment_ext.logsegment, __gp_log_segment_ext.logslice, __gp_log_segment_ext.logdistxact, __gp_log_segment_ext.loglocalxact, __gp_log_segment_ext.logsubxact, __gp_log_segment_ext.logseverity, __gp_log_segment_ext.logstate, __gp_log_segment_ext.logmessage, 
__gp_log_segment_ext.logdetail, __gp_log_segment_ext.loghint, __gp_log_segment_ext.logquery, __gp_log_segment_ext.logquerypos, __gp_log_segment_ext.logcontext, __gp_log_segment_ext.logdebug, __gp_log_segment_ext.logcursorpos, __gp_log_segment_ext.logfunction, __gp_log_segment_ext.logfile, __gp_log_segment_ext.logline, __gp_log_segment_ext.logstack
FROM ONLY gp_toolkit.__gp_log_segment_ext
UNION ALL 
SELECT __gp_log_master_ext.logtime, __gp_log_master_ext.loguser, __gp_log_master_ext.logdatabase, __gp_log_master_ext.logpid, __gp_log_master_ext.logthread,__gp_log_master_ext.loghost, __gp_log_master_ext.logport, __gp_log_master_ext.logsessiontime, __gp_log_master_ext.logtransaction, __gp_log_master_ext.logsession, __gp_log_master_ext.logcmdcount, __gp_log_master_ext.logsegment, __gp_log_master_ext.logslice, __gp_log_master_ext.logdistxact, __gp_log_master_ext.loglocalxact, __gp_log_master_ext.logsubxact, __gp_log_master_ext.logseverity, __gp_log_master_ext.logstate, __gp_log_master_ext.logmessage, __gp_log_master_ext.logdetail, __gp_log_master_ext.loghint, __gp_log_master_ext.logquery, __gp_log_master_ext.logquerypos, __gp_log_master_ext.logcontext, __gp_log_master_ext.logdebug, __gp_log_master_ext.logcursorpos, __gp_log_master_ext.logfunction, __gp_log_master_ext.logfile, __gp_log_master_ext.logline, __gp_log_master_ext.logstack
FROM ONLY gp_toolkit.__gp_log_master_ext
ORDER BY 1;

logtime=>The timestamp of the log message.
loguser=>The name of the database user.
logdatabase=>The name of the database.
logpid=>The associated process id (prefixed with "p").
logthread=>The associated thread count (prefixed with "th").
loghost=>The segment or master host name.
logport=>The segment or master port.
logsessiontime=>Time session connection was opened.
logtransaction=>Global transaction id.
logsession=>The session identifier (prefixed with "con").
logcmdcount=>The command number within a session (prefixed with "cmd").
logsegment=>The segment content identifier (prefixed with "seg" for primary or "mir" for mirror. The master always has a content id of -1).
logslice=>The slice id (portion of the query plan being executed).
logdistxact=>Distributed transaction id.
loglocalxact=>Local transaction id.
logsubxact=>Subtransaction id.
logseverity=>LOG, ERROR, FATAL, PANIC, DEBUG1 or DEBUG2.
logstate=>SQL state code associated with the log message.
logmessage=>Log or error message text.
logdetail=>Detail message text associated with an error message.
loghint=>Hint message text associated with an error message.
logquery=>The internally-generated query text.
logquerypos=>The cursor index into the internally-generated query text.
logcontext=>The context in which this message gets generated.
logdebug=>Query string with full detail for debugging.
logcursorpos=>The cursor index into the query string.
logfunction=>The function in which this message is generated.
logfile=>The log file in which this message is generated.
logline=>The line in the log file in which this message is generated.
logstack=>Full text of the stack trace associated with this message.

This view uses an external table to read the server log files of the entire Greenplum system (master, segments, and mirrors) and lists all log entries. Associated log entries can be identified by the session id (logsession) and command id (logcmdcount). The use of this view requires superuser permissions.

gpadmin=# select * from gp_toolkit.gp_log_system limit 10;
logtime | loguser | logdatabase | logpid | logthread | loghost | logport | logsessiontime | logtransaction | logsession | logcmdcou
nt | logsegment | logslice | logdistxact | loglocalxact | logsubxact | logseverity | logstate | 
logmessage | logdetail | loghint | logquery | logquerypos | lo
gcontext | logdebug | logcursorpos | logfunction | logfile | logline | logstack 
-------------------------------+---------+-------------+--------+--------------+---------+---------+----------------+----------------+------------+----------
---+------------+----------+-------------+--------------+------------+-------------+----------+--------------------------------------------------------------
----------------------------------------------------------------------------------------------------------+-----------+---------+----------+-------------+---
---------+----------+--------------+-------------+---------+---------+----------
2013-11-27 12:51:33.08036-05 | | | p10227 | th-509053184 | | | | 0 | | 
| seg-1 | | | | | LOG | 00000 | database system was shut down at 2013-11-27 12:51:32 EST 
| | | | | 
| | 0 | | xlog.c | 6394 | 
2013-11-27 12:51:33.080475-05 | | | p10227 | th-509053184 | | | | 0 | | 
| seg-1 | | | | | LOG | 00000 | checkpoint record is at 0/8ABA78 
| | | | | 
| | 0 | | xlog.c | 6493 | 
2013-11-27 12:51:33.080507-05 | | | p10227 | th-509053184 | | | | 0 | | 
| seg-1 | | | | | LOG | 00000 | redo record is at 0/8ABA78; undo record is at 0/0; shutdown T
RUE | | | | | 
| | 0 | | xlog.c | 6602 | 
2013-11-27 12:51:33.08053-05 | | | p10227 | th-509053184 | | | | 0 | | 
| seg-1 | | | | | LOG | 00000 | next transaction ID: 0/902; next OID: 10901 
| | | | | 
| | 0 | | xlog.c | 6606 | 
2013-11-27 12:51:33.080551-05 | | | p10227 | th-509053184 | | | | 0 | | 
| seg-1 | | | | | LOG | 00000 | next MultiXactId: 1; next MultiXactOffset: 0 
| | | | | 
| | 0 | | xlog.c | 6609 | 
2013-11-27 12:51:33.08059-05 | | | p10227 | th-509053184 | | | | 0 | | 
| seg-1 | | | | | LOG | 00000 | end of transaction log location is 0/8ABAF0 
| | | | | 
| | 0 | | xlog.c | 6840 | 
2013-11-27 12:51:33.081056-05 | | | p10227 | th-509053184 | | | | 0 | | 
| seg-1 | | | | | LOG | 00000 | Oldest active transaction from prepared transactions 902 
| | | | | 
| | 0 | | xlog.c | 6202 | 
2013-11-27 12:51:33.346109-05 | | | p10227 | th-509053184 | | | | 0 | | 
| seg-1 | | | | | LOG | 00000 | database system is ready 
| | | | | 
| | 0 | | xlog.c | 6231 | 
2013-11-27 12:51:33.346179-05 | | | p10227 | th-509053184 | | | | 0 | | 
| seg-1 | | | | | LOG | 00000 | PostgreSQL 8.2.15 (Greenplum Database 4.2.2.4 build 1 Communi
ty Edition) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Oct 17 2012 11:48:43 | | | | | 
| | 0 | | xlog.c | 6241 | 
2013-11-27 12:51:33.432896-05 | | | p10227 | th-509053184 | | | | 0 | | 
| seg-1 | | | | | LOG | 00000 | Finished normal startup for clean shutdown case 
| | | | | 
| | 0 | | xlog.c | 7071 | 
(10 rows)


gpadmin=# 
Comments