Log Insights: Install on Self-Managed Server
This guide helps you set up the pganalyze Log Insights feature for a self-hosted system, i.e. one that is managed and configured by yourself, instead of one that is provided as a service by a cloud provider.
Note that your account needs to be on the Scale plan or higher to be able to use Log Insights, or you need to be in your initial 14-day trial period.
Installation steps
1. Update pganalyze collector (if needed)
Make sure you are running an up-to-date pganalyze-collector version, by running yum update pganalyze-collector
or apt-get upgrade pganalyze-collector
.
Also make sure the collector is running on your database server directly, and not on another system.
2. Locate your PostgreSQL log file / directory
Log Insights works by continuously tailing your local Postgres log files, and classifying log lines, and submitting log data and statistics to pganalyze.
In order to do that, you need to configure the db_log_location
in the pganalyze-collector.conf
file, which should be either the Postgres log file, or the directory in which log files are located.
We provide a helper for discovering the log directory, which you can run like this as root:
pganalyze-collector --discover-log-location
This will then produce output like this:
2018/05/20 19:51:30 I [server1] Found log location, add this to your pganalyze-collector.conf in the [server1] section:
db_log_location = /var/log/postgresql/postgresql-9.3-main.log
In some cases where discovery doesn't work you might have to review in more detail how your system is set up. Please reach out to pganalyze support for help if needed.
Note that both individual files and directories are supported for db_log_location
.
3. Adjust configuration and run test
We can now configure the log directory for the collector, by adding the db_log_location
setting, so the configuration file looks similar to this:
[pganalyze]
api_key: XXX
[server1]
db_name: postgres, *
db_username: pganalyze
db_password: mypassword
db_host: 127.0.0.1
db_port: 5432
db_log_location: /var/log/postgresql/postgresql-9.3-main.log
Now we can use the --test
option of the collector to verify that log collection and parsing works:
pganalyze-collector --test
In the successful case, the log output will look like this:
2018/05/08 08:40:06 I [server1] Testing statistics collection...
2018/05/08 08:40:07 I [server1] Test submission successful (15.8 KB received)
2018/05/08 08:40:07 I [server1] Testing local log tailing...
2018/05/08 08:40:13 I [server1] Log test successful
2018/05/08 08:40:13 I Re-running log test with reduced privileges of "pganalyze" user (uid = 107, gid = 113)
2018/05/08 08:40:13 I [server1] Testing local log tailing...
2018/05/08 08:40:19 I [server1] Log test successful
There are a few error cases, in particular permission related ones for the "log test with reduced privileges", which you will likely encounter. They are documented at the end of this page.
4. Reload your collector
Once the log test is successful you need to reload the collector for the new configuration to take effect:
pganalyze-collector --reload
After this you should see data showing up in the "Log Insights" tab in pganalyze in less than a minute:
Troubleshooting
Collector test error: "log_line_prefix not supported"
When you see output like this:
2018/05/20 19:56:18 I [server1] Testing statistics collection...
2018/05/20 19:56:20 I [server1] Test submission successful (15.9 KB received)
2018/05/20 19:56:20 I [server1] Testing local log tailing...
2018/05/20 19:56:20 E [server1] ERROR - Could not tail logs for server: Unsupported log_line_prefix setting: '%t '
It means that the currently configured log_line_prefix
is not supported by the collector. You need to change your PostgreSQL configuration, reload the Postgres server, and then re-run pganalyze-collector --test
.
Currently we support the following log_line_prefix settings when using Postgres built-in logging (log_destination = stderr
):
log_line_prefix = '%m [%p] %q[user=%u,db=%d,app=%a] '
(recommended)log_line_prefix = '%m [%p] %q[user=%u,db=%d,app=%a,host=%h] '
log_line_prefix = '%t:%r:%u@%d:[%p]:'
log_line_prefix = '%t [%p-%l] %q%u@%d '
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d - PG-%e '
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_line_prefix = '%t [%p]: [%l-1] [trx_id=%x] user=%u,db=%d '
The following are supported but not recommended, since they do not include the user name and database name:
log_line_prefix = '%m %r %u %a [%c] [%p] '
log_line_prefix = '%m [%p][%v] : [%l-1] %q[app=%a] '
log_line_prefix = '%m [%p] '
We also support the parsing of rsyslogd
log lines that look like the following default template, with an empty log_line_prefix:
Feb 2 09:04:39 ip-172-31-14-41 postgres[7395]: [3-1] LOG: database system is ready to accept connections
If you have a log_line_prefix config thats not covered, please reach out to us, as it is easy for us to add additional parsing support.
Collector test error: "permission denied"
When you see output like this:
2018/05/05 21:32:02 I [server1] Testing statistics collection...
2018/05/05 21:32:03 I [server1] Test submission successful (15.9 KB received)
2018/05/05 21:32:03 I [server1] Testing local log tailing...
2018/05/05 21:32:09 I [server1] Log test successful
2018/05/05 21:32:09 I Re-running log test with reduced privileges of "pganalyze" user (uid = 107, gid = 113)
2018/05/05 21:32:09 I [server1] Testing local log tailing...
2018/05/05 21:32:09 E [server1] ERROR - open /var/log/postgresql/postgresql-9.3-main.log: permission denied
2018/05/05 21:32:19 E [server1] ERROR - Could not tail logs for server: Timeout
It means that the log test was able to run successfully as a root user, but could not be completed when testing with the "pganalyze" user that the collector background process runs as.
See "Allowing access when using Postgres built-in logging" or "Allowing access when using rsyslogd built-in logging" below for details on how to fix this.
Special setups
Allowing access when using Postgres built-in logging
In the case of built-in Postgres logging you will likely run into the issue that log files are by default written in a way that prevents users other than the postgres user from accessing them.
We need to grant the pganalyze user access to them, by doing the following:
First choose a location for log files thats outside of the Postgres data directory, and then set it up like this:
mkdir /my/log/directory
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:
usermod -a -G postgres pganalyze
Adjust the Postgres configuration to log everything to this new directory by doing the following:
log_directory = '/my/log/directory'
log_file_mode = 0640
Reload Postgres afterwards, as well as restart the collector background process, and then re-run the permission test as described in Step 3.
Allowing access when using rsyslogd built-in logging
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 your the log files are owned by the Postgres user, you first need to add the pganalyze user to the postgres group:
usermod -a -G postgres pganalyze
And then make sure that new files are written with chmod 640, in your /etc/rsyslog.conf
:
# 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
As well as making sure the current log file has the correct permissions:
chmod 640 /var/log/postgres/postgres.log
Then reload rsyslog and re-run pganalyze-collector --test
.
Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →