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.

Figure 1: All screens in the dashboard have a navigation bar at the top, which you can use to jump between sections.

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).

$ sudo pg_createcluster 11 secondary -D /mnt/pgdata/11/secondary -P 5433

$ sudo pg_ctlcluster 11 secondary start

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.

Figure 2: Each cluster displays enough information to help you identify it among its peers.

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.

Figure 3: Use the date and severity pull-downs to filter the logs and weed out irrelevant entries.

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.