PGAdmin 4 – server status / view log file

pgadmin

I recently upgraded to Postgresql 9.6 and PgAdmin 4.

Now, one handy feature of PgAdmin 3 was the "server status" window, which I used to look at the log file.

I can't find that in PgAdmin 4. How can I view the server log in PgAdmin 4? Or is there a nice alternative?

Best Answer

I have followed the guide here to use a foreign data wrapper so I can access the log file through SQL.

First, create the extension:

create extension file_fdw;

Next, set up postgres.conf to support csv logging. Essentially, these settings:

log_destination='csvlog'

logging_collector='on'

log_filename='postgresql.log'

And also the following, from the documentation:

Set log_filename and log_rotation_age to provide a consistent, predictable naming scheme for your log files. This lets you predict what the file name will be and know when an individual log file is complete and therefore ready to be imported.

Set log_rotation_size to 0 to disable size-based log rotation, as it makes the log file name difficult to predict.

Set log_truncate_on_rotation to on so that old log data isn't mixed with the new in the same file.

Create, the server and the table, and the log can be queried!

create server logserver FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE postgres_log
(
  log_time timestamp(3) with time zone,
  user_name text,
  database_name text,
  process_id integer,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp with time zone,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos integer,
  context text,
  query text,
  query_pos integer,
  location text,
  application_name text)
SERVER logserver OPTIONS (filename 'pg_log/postgresql.csv', format 'csv');

select message from postgres_log;
Related Question