Example Periodic Reporting via Email Using systemd
Prev | E. Periodic Execution | Next

### Example Periodic Reporting via Email Using systemd

The files shown below deliver an Isok report, if there is something to report, by email every Tuesday morning.

The system on which they are installed must have a mail transfer agent installed, like `Postfix`, to begin the email delivery process. The system also must have GNU `mailutils` installed, or an equivalent mail command, like BSD mailx, to send the email.

Most operating systems will have packages available to install these services, and a way to configure simple defaults. However, it is non-trivial to reliably deliver email from your system directly to the rest of the Internet. The recommended approach is to send the email from your local system to a mail relay provided by your local IT professionals. (Or, your Internet Service Provider. Or, if you are hosted in the cloud, your hosting company.) These professionals will usually be able to supply you with what you need to know to have mail sent from your system to a system able to send email to the Internet at-large. If not, there are companies that provide this service for a nominal fee.

The service you would ask for is usually called an email relay service.

It is usually a good idea to ask your local IT professionals to help with the selection of a mail transfer agent.

This example is expected to run, as is, on most systems that have the default PostgreSQL install.

The example connects to the database and runs as the `postgres` role, the bootstrap superuser. It assumes that `pg_hba.conf` contains:

`local all postgres peer`

This line is typically present, but this is not guaranteed.

| Note
When cutting and pasting from the examples, don't forget to remove the “callout” numbers -- the numbers that call attention to particular lines and have annotations below. Leaving them in can result in errors that are difficult to debug.

Example E.1. Sample `/etc/systemd/system/isok_report.service` File

    # This file is: /etc/systemd/system/isok_report.service
    [Unit]
    Description=Run pg_isok's run_isok_queries() function and email when there's \
    a result
    ConditionACPower=true

    [Service]
    #
    # Configuration is done here (and in /etc/aliases, see pg_isok_report)
    #

    # The postgres connection string (or other arguments to psql)
    # Putting passwords in here is a bad idea, change pg_hba.conf instead?
    Environment="CONNECTION_STRING=mydatabase"

    # Put a connection string variable assignment containing secrets in this file:
    # (man 5 systemd.exec)
    #EnvironmentFile=/etc/pg_isok_secrets

    # The schema in which pg_isok is installed
    Environment="ISOK_SCHEMA=isok"

    # End of configuration

    # The Uni*x user running the db engine
    # (Expected to be the same as the PostgreSQL bootstrap superuser)
    User=postgres
    Type=oneshot
    KillMode=process

    PassEnvironment=CONNECTION_STRING ISOK_SCHEMA
    ExecStart=/usr/local/bin/pg_isok_report

| After installation, or change in the content, don't forget to run: systemctl daemon-reload
| Configuration settings
| Do not forget to set appropriate permissions on the secrets file.
| This is the Un*x `postgres` user, which is usually has the same name as the PostgreSQL bootstrap superuser. So the supplied connection parameters don't mention the username because the default is to use a role with the same name as the connecting Un*x user.

Example E.2. Sample `/etc/systemd/system/isok_report.timer` File

    # This file is: /etc/systemd/system/isok_report.timer
    [Unit]
    Description=Tuesday report from pg_isok

    [Timer]
    # See: man 7 systemd.time
    OnCalendar=tuesday *-*-* 3:00
    RandomizedDelaySec=60m
    Persistent=true

    [Install]
    WantedBy=timers.target

| After installation, or change in the content, don't forget to run: systemctl daemon-reload systemctl enable pg_isok_report.timer systemctl start pg_isok_report.timer
| Configuration settings

Example E.3. Sample `/usr/local/bin/pg_isok_report` File

    #!/usr/bin/bash
    # This file is: /usr/local/bin/pg_isok_report
    #
    # Run pg_isok, and mail (with GNU mailutils) if it produces anything.
    #
    # Expected environment variables:
    # CONNECTION_STRING
    #    The postgres connection string (or other arguments)
    #    Putting passwords in here is a bad idea, change pg_hba.conf instead?
    # ISOK_SCHEMA
    #    The schema in which pg_isok is installed

    # The recommendation is to _not_ change this.  Instead, make an
    # alias for "pg_isok_report" in /etc/aliases.
    MAIL_RECIPIENT=pg_isok_report

    EMPTY_FILE=$(/usr/bin/mktemp --tmpdir pg_isok_empty.XXXXXXXXXX)
    OUTPUT=$(/usr/bin/mktemp --tmpdir pg_isok_output.XXXXXXXXXX)

    PSQL="/usr/bin/psql ${CONNECTION_STRING}"

    cleanup () {
      /usr/bin/rm -rf ${EMPTY_FILE} ${OUTPUT}
    }
    trap cleanup EXIT

    PAGER= ${PSQL} --command="
                  SELECT irid, iqname, first_seen, last_seen, last_role
                       , last_schemas, deferred_to, type, keep_until
                       , qr_id, qr_message, qr_extra, notes
                    FROM ${ISOK_SCHEMA}.isok_results
                    LIMIT 0;
      " \
      > ${EMPTY_FILE} 2>&1

    PAGER= ${PSQL} --command="SELECT * FROM ${ISOK_SCHEMA}.run_isok_queries();" \
      > ${OUTPUT} 2>&1

    cmp --quiet ${EMPTY_FILE} ${OUTPUT} \
      || { /usr/bin/mail -s 'Isok output' ${MAIL_RECIPIENT} \
             < ${OUTPUT} ; }

| After installation, don't forget to run: chmod a+x /usr/local/bin/pg_isok_report

Prev | Up | Next
E. Periodic Execution | Home | F. Techniques For Making Local Extensions to Isok

Page generated: 2025-11-03T16:00:04-06:00.