Configure Database Storage and Logging for Zabbix

Details on configuring PostgreSQL and Apache for Zabbix on OpenWrt, including the PHP configuration, on prepared storage.

Prerequisites#

For PostgreSQL, as well as for robust logging, storage which is persistent, large, and can be written often is required.

See Prepare storage for a way to set this up.

Move the database from volatile storage to persistent storage#

The default location for the PostgreSQL database on OpenWrt is /var/postgresql/data is on a RAM disk (tmpfs) and goes away on every reboot. We want to changet that.

1. service postgresql stop
2. Edit /etc/config/postgresql so that PGDATA points to /srv/postgresql/data.
3. mv /var/postgresql /srv/.
4. service postgresql start.
5. Verify postgresql is running (E.g. via ps w) and logread.

Configure system logging to /srv/log#

This is optional, but allows for persistent logs.

1. mkdir -p /srv/log/

2. Edit /etc/config/system

   1. Replace log_size with log_buffer_size - this configures the size of the in-memory log size without limiting the log file size.
   2. Add option log_file '/srv/log/syslog.

3. Update /etc/init.d/log with the following version. (This updated script is in the development branch, but has not been backported to stable).

   #!/bin/sh /etc/rc.common
   # Copyright (C) 2013 OpenWrt.org
   
   # start after and stop before networking
   START=12
   STOP=89
   PIDCOUNT=0
   
   USE_PROCD=1
   PROG=/sbin/logread
   
   validate_log_section()
   {
     uci_load_validate system system "$1" "$2" \
       'log_file:string' \
       'log_size:uinteger' \
       'log_hostname:string' \
       'log_ip:host' \
       'log_remote:bool:1' \
       'log_port:port:514' \
       'log_proto:or("tcp", "udp"):udp' \
       'log_trailer_null:bool:0' \
       'log_prefix:string'
   }
   
   validate_log_daemon()
   {
     uci_load_validate system system "$1" "$2" \
       'log_size:uinteger:0' \
       'log_buffer_size:uinteger:0'
   }
   
   start_service_daemon()
   {
     [ $log_buffer_size -eq 0 -a $log_size -gt 0 ] && log_buffer_size=$log_size
     [ $log_buffer_size -eq 0 ] && log_buffer_size=64
     procd_open_instance logd
     procd_set_param command "/sbin/logd"
     procd_append_param command -S "${log_buffer_size}"
     procd_set_param respawn 5 1 -1
     procd_close_instance
   }
   
   start_service_file()
   {
     PIDCOUNT="$(( ${PIDCOUNT} + 1))"
     local pid_file="/var/run/logread.${PIDCOUNT}.pid"
   
     [ "$2" = 0 ] || {
       echo "validation failed"
       return 1
     }
     [ -z "${log_file}" ] && return
   
     local mountpoint="$(procd_get_mountpoints "${log_file}")"
   
     [ "$_BOOT" = "1" ] &&
       [ "$mountpoint" ] &&
       ! grep -q ".* $mountpoint " /proc/mounts &&
       return 0
   
     mkdir -p "$(dirname "${log_file}")"
   
     procd_open_instance logfile
     procd_set_param command "$PROG" -f -F "$log_file" -p "$pid_file"
     [ -n "${log_size}" ] && procd_append_param command -S "$log_size"
     procd_close_instance
   }
   
   start_service_remote()
   {
     PIDCOUNT="$(( ${PIDCOUNT} + 1))"
     local pid_file="/var/run/logread.${PIDCOUNT}.pid"
   
     [ "$2" = 0 ] || {
       echo "validation failed"
       return 1
     }
     [ "${log_remote}" -ne 0 ] || return
     [ -z "${log_ip}" ] && return
     [ -z "${log_hostname}" ] && log_hostname=$(cat /proc/sys/kernel/hostname)
   
     procd_open_instance logremote
     procd_set_param command "$PROG" -f -h "$log_hostname" -r "$log_ip" "${log_port}" -p "$pid_file"
     case "${log_proto}" in
       "udp") procd_append_param command -u;;
       "tcp") [ "${log_trailer_null}" -eq 1 ] && procd_append_param command -0;;
     esac
     [ -z "${log_prefix}" ] || procd_append_param command -P "${log_prefix}"
     procd_close_instance
   }
   
   register_mount_trigger()
   {
     [ -n "${log_file}" ] && procd_add_action_mount_trigger start "${log_file}"
   }
   
   service_triggers()
   {
     config_load system
     procd_add_reload_trigger "system"
     procd_add_validation validate_log_section
     config_foreach validate_log_section system register_mount_trigger
   }
   
   start_service()
   {
     config_load system
     config_foreach validate_log_daemon system start_service_daemon
     config_foreach validate_log_section system start_service_file
     config_foreach validate_log_section system start_service_remote
   }
   
   boot() {
     _BOOT=1 start
   }

4. Add the line /etc/init.d/log to /etc/sysupgrade.conf (this preserves this log initscript update across system upgrades).

5. Add the following as /etc/logrotate.d/syslog

   /srv/log/syslog {
     rotate 8
     weekly
     nocompress
     missingok
     copytruncate
     minsize 100k
   }

6. Edit /etc/crontabs/root to have the following line:

   45 0 * * * /usr/sbin/logrotate /etc/logrotate.conf

   This enables weekly rotation of the system logs, when they exceed 100k in size, checked at quarter to 1 am every day.

Create the Zabbix database#

NOTE This is a simplified setup. For more secure configuration review the documentation at https://www.zabbix.com/documentation/7.0/en/manual/best_practices/security/access_control/postgresql.

1. Download the Zabbix source code for Zabbix 7.0
2. Extract the source code on your workstation
3. On the OpenWrt Zabbix server:

   1. createuser -U postgres -h /var/run/postgresql --pwprompt zabbix and enter a password for the Zabbix database user. You will need this later.

   2. Execute:

      createdb -U postgres -h /var/run/postgresql -O zabbix -E Unicode -T template0 zabbix

   3. On your workstation, in the Zabbix source code directory:

      cd database/postgresql
      scp -O schema.sql images.sql data.sql root@<your-openwrt-zabbix-server>:

   4. On your OpenWrt Zabbix server (for more details see https://www.zabbix.com/documentation/7.0/en/manual/appendix/install/db_scripts)

      cd ~
      cat schema.sql | psql -U zabbix -h /var/run/postgresql zabbix
      cat images.sql | psql -U zabbix -h /var/run/postgresql zabbix
      cat data.sql | psql -U zabbix -h /var/run/postgresql zabbix