How to load Greenplum pg_log into a table.

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article introduces how to load pg log into a database table for further analysis.

Environment

Product Version: Other

Resolution

See instructions below or watch a video of the procedure here:



1. Create an external table. The input file saved at the master host, '/tmp/load1.csv'.

CREATE EXTERNAL web TABLE ext_t1
( logtime timestamp with time zone,
loguser text,
logdatabase text,
logpid text,
logthread text,
loghost text,
logport text,
logsessiontime timestamp with time zone,
logtransaction int, 
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 int, 
logcontext text,
logdebug text,
logcursorpos int, 
logfunction text,
logfile text,
logline int, 
logstack text 
)
execute E'cat /tmp/load1.csv' on master
FORMAT 'csv' (delimiter ',' null '' escape '"' quote '"')
ENCODING 'UTF8'; 

2. Create a heap table to save this table in the database. We can also read the external table directly if the log file is not big.

create table log1 as select * from ext_t1 distributed randomly;

select * from ext_t1 limit 1;

When we need to analyze big pg_log files, load the files into a database table can help a lot.

The gp_toolkit view can be used to review the master log directly. Those views will read all log files before generate the result, so it could take a long time to run. Those views requires superuser permission. 

Additional Information

The gp_toolkit view can be used to review the master log directly. Those views will read all log files before generate the result, so it could take a long time to run. Those views requires superuser permission.
 

gp_toolkit.gp_log_command_timings	report the SQL execution time.
gp_toolkit.gp_log_database	lists log entries associated with the current database.
gp_toolkit.gp_log_master_concise	list partial messages from all log files (logtime, logdatabase, logsession, logcmdcount, logmessage)
gp_toolkit.gp_log_system	lists all log entries