Add logs to Postgres
by Paulo Gonzalez
2022-05-23 | postgres logs introspection
Following up on the concept of introspection, I thought it would be interesting to see Postgres' runtime logs. They are turned off by default (a lot happens in Postgres :) ) but we can turn them on and it is beneficial to see more info about what is going on under the hood. There was a good article that showed the steps to set it up. Here are the steps I did to get the logs going:
In order to enable logs, we should figure out where the config file is located as well as where the logs will be output to.
# Get the location for the config file
postgres=# show config_file;
config_file
-----------------------------------------
/etc/postgresql/14/main/postgresql.conf
(1 row)
# Now, let's see where the data folder is located:
postgres=# show data_directory;
data_directory
-----------------------------
/var/lib/postgresql/14/main
(1 row)
So let's open up the config file and make some changes:
# I made these changes:
# sudo vim /etc/postgresql/14/main/postgresql.conf
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_min_messages = debug1
With these changes, we can restart the service and check some logs out:
sudo service postgresql restart
Now, let's make a typo and force a log (ERROR):
paulo@s76 ~ $ psql -h localhost -U postgres
Password for user postgres:
psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1+b1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=# select count(1) from does_not_exist;
ERROR: relation "does_not_exist" does not exist
LINE 1: select count(1) from does_not_exist;
^
postgres=#
Here is the log!
paulo@s76 ~ $ sudo tail -f /var/lib/postgresql/14/main/log/postgresql-2022-05-23_164455.csv | ag ERROR
2022-05-23 16:52:32.812 CDT,"postgres","postgres",293399,"127.0.0.1:39830",628c0213.47a17,1,"SELECT",2022-05-23 16:52:19 CDT,4/141,0,ERROR,42P01,"relation ""does_not_exist"" does not exist",,,,,,"select count(1) from does_not_exist;",22,,"psql","client backend",,0
If you want to see ALL the logs, you can set the below to `all` and it will log all queries. Just don't do it in a prod server.
log_statement = all
Thanks for reading!