Puppet, Zabbix, PostgreSQL and pgpool-II Together - pg_monz Module

In this article I'll show how you can use fatdragon/pg_monz puppet module. Before I start, I must say that I'm a huge Puppet and Zabbix fan. The third product on the list - PostgreSQL - also deserves full respect, and it is my database of choice when it comes to good old RDBMS, but lately I'm more interested in NoSQL world. Finally, I must admit that I'm not nearly so excited about pgpool-II; although being there for years, the project is still very immature, especially when it comes to documentation. But let's jump to the subject.

fatdragon/pg_monz puppet module is an open source project, available under Apache 2.0 license, and managed at Github. Contributors are welcome!

Just Give Me Up-And-Running Please!

If you are kind of a person who wants to see a working setup without too many explanations, here's one such setup (assuming that Zabbix is already installed on server and client by using puppet/zabbix module). Server:

class { 'pg_monz::server': }

Note that if you're not using exported resources (described below) you'll have to manually import templates from /tmp directory by using Zabbix web interface.

Client (assuming that both PostgreSQL and pgpool-II instances should be tracked):

class { 'pg_monz':
  pgrolepassword => 'my_postgres_password',
  pgpoolconf     => '/etc/pgpool2/3.5.2/pgpool.conf',
  pglogfile      => 'postgresql-9.4-main.log',
}

The last thing to do is to link monitored hosts to appropriate templates in Zabbix as usual. That's it - you should be able to monitor your PostgreSQL / pgpool-II infrastructure.

pg_monz Zabbix Template

pg_monz is another open source project, and it is actually Zabbix template that enables detailed monitoring of PostgreSQL and pgpool-II. Actually, it provides much more details then you'll probably ever need (i.e. table-level monitoring). It is useful no matter if you'll monitor a standalone PostgreSQL instance, or a large high-availability cluster. Current version of fatdragon/pg_monz puppet module installs pg_monz 2.0.1 (the latest pg_monz version at the moment of this writing).

Compatibility

The module is created for Ubuntu, tested on Ubuntu, and Ubuntu is officially supported. But since the module is quite simple (mostly copying files and assigning permissions, with only one one-liner shell script), the truth is that it will actually run on any Linux distribution, FreeBSD, and probably even OS X. The only reason for me to officially limit it to Ubuntu is that I've never tested it on other OS. If you decide to test it on another OS - the only thing you need to check is that directory paths are set appropriately. (All paths are customizable through parameters.)

Prerequisites

fatdragon/pg_monz puppet module relies on puppet/zabbix module, so its presence is required (zabbix class on server, and zabbix::agent class on client). To avoid causing any conflicts with your existing puppet/zabbix settings, fatdragon/pg_monz module does not manage puppet/zabbix internally, so it's your responsibility to configure puppet/zabbix before installing fatdragon/pg_monz.

Installation

The installation procedure is the same as for any other puppet module from puppet forge:

$ puppet module install fatdragon-pg_monz

Server Side

The only thing that the module does at the server side is preparing and installing following Zabbix templates:

• Template App PostgreSQL
• Template App PostgreSQL SR
• Template App PostgreSQL SR Cluster
• Template App pgpool-II
• Template App pgpool-II watchdog

For this purpose class pg_moz::server should be used in the following way:

class { 'pg_monz::server': }

Often you won't have to provide any parameter, but it's better to understand the meaning of the parameters anyway.

Template Installation Mechanism

For installing the templates fatdragon/pg_monz module uses zabbix::template class from puppet/zabbix module, and as you can see in the original documentation, this class requires configuring exported resources. In very few words it means that you need to configure puppet/zabbix module not only to take care of installation and configuration of Zabbix server, but also of managing Zabbix resources (automatically creating Zabbix templates, assigning templates to monitored hosts, etc.) In my personal opinion this is a thing that puppet should not do - it's Zabbix' responsibility. Mixing concerns in this way will cause a headache sooner or later. Another reason for not using this feature is explained in the original documentation.

But it also means that without setting this feature, pg_monz::server class won't be able to install the templates. Although it is true, the class is still useful because it will prepare the templates for you so that you can import them manually through Zabbix web interface (it is one-time task anyway). In this case you'll find prepared templates in /tmp directory on the server (after puppet agent run, of course). Another location where template files will be stored is defined by zabbix_template_dir parameter of zabbix::web class (on Ubuntu defaults to /etc/zabbix/imported_templates).

Again, if you already have this feature enabled in your environment, there's no reason for not using it in fatdragon/pg_monz module. If this is the case you should know that fatdragon/pg_monz module will not perform installation at every run, but only during the first run. It means that once installed, the templates are not under puppet's control anymore - you'll be able change them in Zabbix interface, or in other way, without risk that they'll be overridden by puppet again. Actually, fatdragon/pg_monz module will perform installation only if the appropriate template file is not found in the directory defined by mentioned zabbix_template_dir parameter.

pg_monz::server Class' Parameters

Talking about parameters, although the class accepts a few, only two of those are introduced by the module; all other are actually representing corresponding Zabbix macros. Let's start with these new parameters:

The reason for me to implement install_templates parameter was that without it the class will always ensure presence of template files in /tmp directory, although the installation won't be performed at every run (as described above). This won't cause any harm, but can be annoying, especially knowing that Ubuntu will delete all the files from /tmp directory at reboot. Basically it will cause configuration changed logs and statuses although actually nothing has been changed.

The second group of parameters are the parameters which are representing corresponding pg_monz macros. You can find the complete list of macros in pg_monz documentation, but not all of them are implemented in fatdragon/pg_monz module. I've decided to implement only those that are important for configuring file and directory paths, to ensure that Zabbix can start using pg_monz template out-of-the-box. All other templates can be adjusted later, from Zabbix web interface. Actually, as already mentioned above, you can later change all macros through Zabbix web interface, no matter if they are set by puppet or not. Here's the list:

Default values of the parameters are actually default values of corresponding pg_monz macros, except for pglogdir in which case default value is changed from /usr/local/pgsql/data/pg_log to /var/log/postgresql.

Obviously, with all these attributes you should ensure that they are containing correct path on the client(s). If all of your clients don't have the same corresponding paths you'll have to override default macro values at host level in Zabbix.

Client Side

Although fatdragon/pg_monz module actually does much more on the client, it is easier to explain. Let's start from example usage:

class { 'pg_monz':
  pgrolepassword => 'my_postgres_password',
  pgpoolconf     => '/etc/pgpool2/3.5.2/pgpool.conf',
  pglogfile      => 'postgresql-9.4-main.log',
}

pg_monz Class' Parameters

Again, although the class has quite a few parameters, a lot of them are simply derived from pg_monz. Let's start with the new ones:

Parameters that correspond to pgsql_funcs.conf file keys:

Parameters that correspond to pgpool_funcs.conf file keys:

Known Issues

One known issue is that pgpoolrolepassword value is ignored if pgrole is the same as pgpoolrole, and pgrolepassword value is used instead. Although it may sound like obvious choice (the same user should not have two passwords), it causes the following example not to work as expected:

class { 'pg_monz':
  pgpoolrolepassword => 'my_postgres_password',
}

It will not work because pgrole and pgpoolrole are the same (default 'postgres'), so pgrolepassword is used instead (default ''). The issue will be resolved in some of the future versions, and meanwhile you should ensure that pgrolepassword always contains the correct value.