linuxpostgresqldebianlibpqxx

SQL query to add message to PostgreSQL server log file

For the HelpCovid GPLv3+ web server application (git commit dff3ab1521ed579cbf2 at end of March 2020, probably in function hcv_initialize_database of our file hcv_database.cc) in C++ for Linux (work in progress) using libpqxx with PostgreSQL 11 on Debian/Buster, we would like at startup time to issue one single SQL query which adds a message to the PostGreSQL log file (e.g. /var/log/postgresql/postgresql-11-main.log in our test environment).

The intuition would be that just after creating the tables we would issue some SQL like

--- wrong fictional PostgreSQL query

ADD TO POSTGRESQL LOG FILE ("start of helpcovid pid 1234 commit dff3ab1521ed+ on localhost");

with the hope that something similar to

2020-03-31 17:00:01.656 CEST [13262] helpcovid_usr@helpcovid_db start of helpcovid pid 1234 commit dff3ab1521ed+ on localhost

would be added to /var/log/postgresql/postgresql-11-main.log ...

This mostly to facilitate debugging and paging that postgresql-11-main.log file (or syslogs, if PostgreSQL logs there).

Is there a quick and dirty way to achieve this?

Perhaps RAISE NOTICE, but how ?

Solution

  • Try using a DO statement:

    DO $$BEGIN RAISE LOG 'whatever'; END;$$;

    Messages of level LOG get written to the log file.