Step 1: Determine Log Location

Note that both individual files and directories are supported for db_log_location.

If the db_log_location was discovered by the collector, you only need to confirm that the pganalyze user has access to the log files. Check this with ls:

ls -l [discovered_log_location]

This will show output like

-rw-r----- 1 postgres adm 0 Feb 17 00:00 /var/log/postgresql/postgresql-13-main.log

The fourth field here (adm in this case) is the group that owns the log files. If this is not adm on your system, you should add the pganalyze user to this group. E.g., if the owning group is postgres, run the following:

sudo usermod -a -G postgres pganalyze

Then, update the file permissions of newly created log files to ensure group read permissions:

ALTER SYSTEM SET log_file_mode = '0640';
SELECT pg_reload_conf();

Important: If you changed the group membership of the pganalyze user you will need to restart the pganalyze collector for the change to take effect. You can do so after updating the configuration in the next step.

If the Postgres log_directory is configured to be inside the data_directory, log files are written out in a way that prevents users other than the postgres user from accessing them by default. It’s difficult to set up permissions that both conform to security best practices and allow the pganalyze user access to the logs in this configuration, so we recommend moving the log_directory out of the data directory.

First, choose a location for log files that is outside of the Postgres data directory, and then set it up like this:

sudo mkdir /my/log/directory
sudo chown postgres:postgres /my/log/directory

Also make sure that the pganalyze user can access this directory, by making it a member of the postgres group:

sudo usermod -a -G postgres pganalyze

Then, adjust the Postgres configuration to log everything to this new directory by running the following commands as a Postgres superuser:

ALTER SYSTEM SET log_directory = '/my/log/directory';
ALTER SYSTEM SET log_file_mode = '0640';
SELECT pg_reload_conf();

Now, re-run

sudo pganalyze-collector --discover-log-location

and you should get the expected output from above with the log_directory you just configured.

Important: After changing group membership of the pganalyze user you will need to restart the pganalyze collector for the change to take effect. You can do so after updating the configuration in the next step.

For rsyslogd and other syslog daemons, the issue can often be that new log files are created with permissions that don’t allow the pganalyze user access.

Assuming that the log files are owned by the Postgres user, you first need to add the pganalyze user to the postgres group:

sudo usermod -a -G postgres pganalyze

And then make sure that new files are written with chmod 640. For rsyslog, adjust your /etc/rsyslog.conf as follows:

# Place this at the beginning of the rsyslog.conf file:
$umask 0000
$FileCreateMode 0600

 
# Place this where the postgres log is being output
$FileCreateMode 0640
local0.* /var/log/postgres/postgres.log
$FileCreateMode 0600

For syslog-ng and other syslog implementations, the configuration changes may be different; consult your syslog documentation.

After this, make sure the current log file has the correct permissions:

sudo chmod 640 /var/log/postgres/postgres.log