postgresql logs dumping in table

Question

I am dumping postgresql .csv file into a table but getting below error

COPY postgres_log FROM 'C:\Program Files\PostgreSQL\13\data\log\postgresql-2021-06-15_191640.csv' WITH csv;

ERROR:  extra data after last expected column
CONTEXT:  COPY postgres_log, line 1: "2021-06-15 19:16:40.261 IST,,,5532,,60c8af3f.159c,1,,2021-06-15 19:16:39 IST,,0,LOG,00000,"ending lo..."
SQL state: 22P04

I have followed below postgresql document https://www.postgresql.org/docs/10/runtime-config-logging.html Please suggest, how to dump in table

Answer 1

You're following instructions from Postgres 10, in which the CSV log format has one fewer column than Postgres 13, which is the version you're actually running. That's why you get that error message. To fix the problem, update your postgres_log table definition as per the PG 13 docs:

https://www.postgresql.org/docs/13/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG

CREATE 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,
  backend_type text,
  PRIMARY KEY (session_id, session_line_num)
);