gp toolkit schema views to view Greenplum database server log files

Post date: Jan 20, 2014 9:44:20 PM

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=#