Search code examples
postgresqlerror-handlingcompiler-errorserror-loggingerror-log

to log and store Postgres errors?

I have a requirement to capture all the database errors arrived while calling a procedure/function, inserting a record in a table or deleting. Its something like whenever any errors occurs in Postgres database, that needs to be captured. Can any one suggest me some like or any small example stating that.

Till now I was using: Raise notice or Raise exception to raise it on console log. But I want to capture these and store in a table(some error log table).

I have set below parameters in postgresql.conf:

log_destination = 'stderr','csvlog','syslog','eventlog'
logging_collector = on
log_filename = 'tst_log_err-%a.log'
client_min_messages = debug5
log_min_messages = debug5
log_min_error_statement = debug5
log_min_duration_statement = 300ms
log_checkpoints = on
log_line_prefix =  '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_lock_waits = on
log_statement = 'all'

and then created table "postgres_log" with all the specified details.

And then tried stopping and restarting local postgresql server but I got failed to restart

Kindly suggest.

Solution

  • https://www.postgresql.org/docs/current/static/runtime-config-logging.html

    set logging_collector to on, log_destination to stderr,csvlog, log_min_error_statement to NOTICE (if you want higher then NOTICE to be saved in logs). Eg log_min_error_statement = NOTICE this will capture all you rase NOTICE, raise WARNING and so on; log_min_error_statement = WARNING this will capture all you rase WARNING, raise error and so on.

    then, follow https://www.postgresql.org/docs/current/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG

    you can setup cron to load it periodically...