Elephant in the Room

PostgreSQL is an extensible, object-oriented database that supports both SQL for relational and JSON for non-relational queries. Its extensive list of enterprise-class features makes it one of the most popular open source relational databases.

Running a database server successfully, however, requires more than just the database. Along with the PostgreSQL database, you also need tools to administer the data it houses and tools for the administration of the server itself. As any database administrator knows, that involves keeping an eye on the logs to look for inconsistencies and regularly checking the server statistics to weed out bottlenecks.

Elephant Shed [1] is an all-in-one appliance that combines several open source tools that a database administrator would need to run and manage a database server. Among other things, you can use it for fine-tuning, to monitor its vitals, and to design and execute a backup strategy. The appliance uses Prometheus [2] and Grafana [3] to collect and visualize all kinds of database metrics, pgBadger [4] to collect and analyze the log, pgBackRest [5] to define an effective backup strategy, pgAdmin4 [6] to administer the data housed within the server, and Cockpit [7] to take charge of the underlying server. In essence, Elephant Shed glues all these individual pieces together and makes them accessible through an intuitive browser-based interface.

Tame the Beast

Elephant Shed is released under the GPLv3 license and supports PostgreSQL versions 9.4 upward. You can install the tool atop Debian 9, CentOS 7, or Ubuntu 18.04. The developers suggest the appliance is suitable for use on physical hardware, as well as in virtualized environments.

The developers of Elephant Shed, credativ, have made it fairly easy to install the appliance by hosting binary packages for all supported distributions. Basically it involves adding the credativ repo and then using it to fetch the appliance; its dependencies can be fetched from the distribution's official repositories. If you are installing Elephant Shed on CentOS, you also have to disable SELinux, which interferes with a couple of the appliances' components (i.e., pgAdmin4 and Shell In a Box). You can follow the exact installation steps on the project's website [8] for your preferred distribution.

For testing purposes, however, you can install Elephant Shed with Vagrant. A previous article in Linux Pro Magazine described how to set up and configure Vagrant [9]. To begin, open a terminal and enter

$ mkdir elephant-shed
$ cd elephant-shed
$ vagrant init credativ/elephant-shed
$ vagrant up

to fetch all the components required to create a Debian virtual machine (VM) and install the Elephant Shed appliance inside it.

Rattle the Cage

Once you have installed Elephant Shed, fire up a browser and head to https://localhost:4433 to bring up the dashboard. You have to accept the self-signed certificate before logging in to the interface with the default credentials, which varies depending on how you installed the appliance. For instance, if you installed it with Vagrant, as shown above, the default username and password are both admin.

Now you are in the Elephant Shed dashboard (Figure 1). Before you proceed further, note that all the bundled components have been configured to use PAM authentication and accept the login details you have used for authenticating into the dashboard. However, pgAdmin4 uses its own user management system. If you've installed Elephant Shed from the binary packages in the repository, you would have been asked to create a username and password to access pgAdmin4. On the other hand, if you've installed the appliance through the Vagrant VM, you can use admin@localhost as the username and admin as the password.

The interface has a simple layout with reasonable button labels that depict their objectives. Furthermore, the various buttons use the icons of the apps they point to. For instance, DB Administration takes you to pgAdmin4 to help you administer the PostgreSQL database. Similarly, Monitoring takes you to Grafana, Metrics links to Prometheus, and Log Reports goes to pgBadger. The Backups icon points to pgBackRest, and Cockpit is available from the System Management icon.

If you need to use the command line, you can do so from within the web-based interface with the Shell icon, which launches a Shell In a Box session. However, you are asked for authentication information, which again depends on how you installed the appliance. If you used Vagrant, you can use admin as both the username and the password to log in.

One of the first things you should do, especially if you're using the Vagrant VM, is to change the default password of the admin user. Fire up the Shell In a Box session, authenticate with the default username and password, and then change the password for the admin user, just as you would on a Linux machine:

$ passwd admin

Similarly, you can add new users with:

$ sudo adduser bodhi

This command creates a new user named bodhi and prompts you for additional details about the account, most notably the password and full name. After creating the user, remember to add it to the elephant shed group with:

$ sudo usermod bodhi -G elephant-shed

You can now log in to the Elephant Shed dashboard with the authentication details of the new user (i.e., bodhi, in this case) instead of the default admin user.

Manage Clusters

At the bottom of the interface is a list of PostgreSQL clusters along with some details that help you identify them. On a new installation is one cluster running PostgreSQL v11 named main.

A database cluster in PostgreSQL parlance is a collection of databases managed by a PostgreSQL server. Elephant Shed can manage multiple clusters, and you can easily add additional ones (see the "Add More PostgreSQL Clusters" box).

Each cluster has a set of toggle switches – Archiving, Full Backup, and Incr Backup (incremental backup), that also show the current status of the respective service (Figure 2). When you click on a cluster, it unravels a menu with an additional list of buttons.

Note however that instead of taking action directly, all the toggles and buttons link to the corresponding components, where you have to confirm the action by triggering the corresponding service. For instance, the systemd section lists the Service and Log buttons. The Log button brings up the log entries for the cluster (Figure 3), and the Service button takes you to the Cockpit page, where you can stop or start the particular cluster.

Similarly, simply clicking the Run button under the Report section does not trigger pgBadger. Instead you are taken to Cockpit, where you have to trigger the process manually. Once a report has been generated, you can use the Show button to view it.

Enable Backups

One component I particularly like in the Elephant Shed appliance is pgBackRest, one of the most comprehensive backup solutions for PostgreSQL that bundles some of the most commonly used features, most notably the flexibility to take either full or incremental backups.

By default, the component is disabled, which is why clicking the Backup icon in the dashboard does not display a result on a fresh installation. To get started, you have to enable either the full or incremental backup toggle (i.e., Full Backup or Incr Backup). For the first backup, click the Full Backup button, which takes you to the service's page in Cockpit. Here, click the Start button to make the backup. Depending on the size of your database, the process might take some time to complete; you can monitor its progress under the Service Logs section (Figure 4).

When the backup has been made, the process becomes inactive again, and you can switch back to the main page of the interface with the navigation bar at the top. Notice that the Full Backup toggle has been enabled, along with the Archiving toggle (Figure 5), which signals that the files from write-ahead logging (WAL) are also being backed up. Ideally you should archive the WAL files before taking the backups to ensure you can do point-in-time recovery. A particularly good feature is that the appliance is configured to activate the archiving option automatically when a backup is triggered.

Once you have made a full backup, you can then use the Incr Backup button to back up only the files that have changed between the last full backup and the current data. pgBackRest has a lot of configurable parameters to give you better control over the backups, including defining an automation schedule and a retention policy. However these parameters aren't exposed in the dashboard and can only be tweaked by editing the configuration file. The relevant configuration file options are mentioned in the Elephant Shed documentation [10].

Conclusion

You can now roam around the intuitive interface and peek inside the components, most of which are pre-configured with the bare minimum settings to be of use straight out of the box. For instance, Grafana ships with some dashboards pre-deployed to give you an overview of the underlying PostgreSQL server (Figure 6). Note, however, that this dashboard is read-only and if you need to make any modifications to its configuration, you have to save it under a different name. Moreover, Grafana offers a ton of other dashboards that you can easily grab from its website. Although the process is fairly intuitive, you can follow the official Grafana documentation to help you through the process.

Although Elephant Shed doesn't have much documentation beyond the bare essentials you need to get the appliance up and running, all the bundled components in the appliance have extensive documentation infrastructures of their own. All the tools are fairly intuitive to operate, but if you haven't used them before, you can follow the documentation on their respective websites to familiarize yourself.