4. Components¶

4.1.1. Default Configuration¶

Beside the postgresql-common default configuration the elephant-shed-postgresql package adds additional parameters for the cluster creation process. Some of these parameters are required by the Elephant Shed components. E.g. pgBadger requires some special log_line_prefix. You can find this configuration under /etc/postgresql-common/createcluster.d/elephant-shed.conf. Be careful when changing any of these values.

4.1.2. Cluster Administration¶

To create a new cluster issue the command pg_createcluster <version> <name>. Installed clusters and their status can be listed via pg_lsclusters.

postgres@stretch:~$ pg_lsclusters
Ver Cluster  Port Status          Owner    Data directory  Log file
9.6 main     5432 online          postgres /9.6/main       /log/9.6-main.log
9.6 standby1 5433 online,recovery postgres /9.6/standby1   /log/9.6-standby1.log
9.6 standby2 5434 online,recovery postgres /9.6/standby2   /log/9.6-standby2.log
9.6 test     5435 online          postgres /9.6/test       /log/9.6-test.log

To delete a cluster use pg_dropcluster <version> <name> (be careful, this removes all data in the cluster as well!).

To start, stop, restart or reload use pg_ctlcluster <action> <version> <name> with the following commands as action:

• start
• stop
• restart
• reload

Alternatively, you can use systemctl (systemctl <action> postgresql@<version>-<name>) or the Cockpit web interface.

Note: Future versions of The Elephant Shed will include cluster management in the portal.

4.2.1. PostgreSQL Cluster¶

This section presents an overview of the existing PostgreSQL clusters and their status. For each cluster, a set of switches shows the current status. By clicking on a cluster an additional menu with buttons opens. Currently all buttons link to the corresponding components where a confirmation is required so no actions are triggered directly, but this may change in the future.

4.2.2. systemd - Service¶

This links to the configuration of this PostgreSQL cluster in Cockpit. Here it is possible to configure the corresponding service to be enabled or disabled on system start and also trigger actions like start, stop and reload.

4.2.3. systemd - Log¶

Links to the corresponding log entries in Cockpit if syslog is enabled for this cluster (which is the default for clusters created by Elephant Shed).

4.2.4. Report - Run¶

By default pgBadger reports for all clusters are generated once every night. With this service it is possible to generate a report for a specific cluster on demand.

4.2.5. Report - Show¶

Links to the corresponding pgBadger report overview. See pgBadger for more information.

4.8.5. Backup¶

This sections provides several functions for backups using pgBackRest. For more information about the backup tool pgBackRest visit pgBackRest.

4.2.7. Switches¶

4.4.1. Services¶

In this setup the Prometheus stack consists of different components controlled by systemd. The following units are deployed.

4.12.1. Additional Resources¶

• https://prometheus.io/docs/introduction/overview/
• https://github.com/prometheus/prometheus

4.5.1. PostgreSQL Server Overview¶

This dashboard starts with a summary section with simple gauges to provide a overview of the whole system. These gauges may indicate current problems or give a hint on problems that might occur in the future.

After the gauges in-depth metrics are shown as graphs.

4.5.2. Server metrics¶

Server metrics include e.g.: CPU usage (by type / by core), disk usage, disk utilization, network throughput, and much more. The following template filter are configured:

• Disk: filter one or more disks
• Interface: filter on or more interface
• Filesystem: filter on or more filesystem / mountpoint

4.5.3. Cluster metrics¶

The next section contains PostgreSQL Cluster wide metrics like connections (by type / by database), number of transactions, database growth and more. Only one cluster is shown by a time. To switch the current shown cluster use the template filter PostgreSQL Cluster.

4.5.4. Database metrics¶

Database level metrics are shown at the end of PostgreSQL Overview dashboard. By default metrics for all databases of the current selected PostgreSQL cluster are shown. To filter one or more databases the template filter Database could be used.

4.12.1. Additional Resources¶

• http://docs.grafana.org/
• https://github.com/grafana/grafana

4.12.1. Additional Resources¶

• https://omnidb.org/

4.7.1. Setup¶

The PoWA web interface maintains a list of known clusters in the system in /etc/powa-web.conf. To update that list, use:

update-powa-web-config

To enable PoWA monitoring for a cluster, run es_ctlcluster:

es_ctlcluster 14 main enable-powa

In case of problems, the web interface will throw “Auth failed” errors for a wide range of possible errors. Consulting the PostgreSQL server log in /var/log/postgresql/postgresql-NN-main.log is often a good place to start debugging.

4.8.1. Full Backup¶

Full backups represent an complete backup of the database at a given point in time. A backup consists of two parts, the backup itself, stored in backup and the WAL files which were written during the backup, stored in archive.

To ensure these WAL files are in the archive we automatically enable WAL archiving before the first backup is created.

Warning: If archiving is enabled all WAL files newer than the oldest stored backups are kept as well. This can consume a lot space in the backup location if backups are kept for a long time and archiving is not disabled. By deleting a backup the no longer needed WAL files are removed as well.

4.8.2. Incremental Backups¶

Incremental backups represent the changed data between a previous full backup and the current data at a given point. Incremental backups can be significant smaller than full backups but depend on a specific previous full backup. Without this full backup they can not be restored.

4.8.3. Retention¶

To clean up space old backups needs to be deleted. pgBackRest needs to know how many full backups to keep. If the number is reached all additional backups will be deleted starting with the oldest. If a full backup is deleted all incremental backups depending on it will be deleted as well. This is necessary because an incremental backup can not be restored without the matching full backup.

4.8.4. Configuration¶

The configuration file can be found in /etc/pgbackrest.conf.

[global]
repo-path=/var/lib/pgbackrest

[9.6-main]
db-path=/var/lib/postgresql/9.6/main
db-port=5432

[9.6-test]
db-port=5433
db-path=/var/lib/postgresql/9.6/test

The global part sets the default configuration for every existing and future database cluster. For each single cluster theses defaults can be changed. Some basic options will be explained here. Please see the documentation for a full overview.

If the server is setup using ansible, additionally the following [global] parameters are set:

[global]
retention-full=4
compress-level=6
spool-path=/mnt/backup/pgbackrest_spool
archive-async=y
archive-queue-max=1099511627776
repo-path=/mnt/backup/pgbackrest
...

4.8.5. Backup¶

For each cluster there is a systemd service which does a full or incremental backup.

• pgbackrest@<version>-<name>
• pgbackrest-incr@<version>-<name>

To create an ad-hoc backup the corresponding service can be started. systemctl start pgbackrest@9.6-main would create a full backup of the cluster 9.6-main.

If no previous full backup is available pgbackrest-incr@ will also create a full backup.

4.8.6. Automation¶

To automate the creation of backups and the retention policy enforcing there are two systemd timers per cluster.

• pgbackrest@-.timer
• pgbackrest-incr@-.timer

pgbackrest@<version>-<name>.timer triggers full backups and pgbackrest-incr@<version>-<name>.timer triggers incremental backups.

These timers are created for every cluster and are initialized with a default timing. The timers can be enabled independently for every database cluster either via systemd or the web portal. To fully enable a timed backup the timer must be started and enabled. If the timer is started but not enabled systemd will not start it after the next reboot.

Keep in mind that enabling only the incremental backup is only reasonable for shorter periods of time, special scenarios like not changing databases, or if the full backups are triggered in another way. To keep storage and restore time at an reasonable level periodic full backups are needed.

# /lib/systemd/system/pgbackrest@.timer
[Unit]
Description=Automated pgBackRest full backup of PostgreSQL cluster %i

[Timer]
OnCalendar=Sun *-*-* 01:00:00
RandomizedDelaySec=2h

[Install]
WantedBy=multi-user.target

# /lib/systemd/system/pgbackrest-incr@.timer
[Unit]
Description=Automated pgBackRest incremental backup of PostgreSQL cluster %i

[Timer]
OnCalendar=Tue,Thu *-*-* 01:00:00
RandomizedDelaySec=2h

[Install]
WantedBy=multi-user.target

4.8.7. Restore¶

Restore is an invasive action that can destroy data if not executed properly!

To restore a backup there are two main methods full and delta.

# 1. Stop the cluster
pg_ctlcluster <major version> <name> stop

# 2. Delete or move all remaining data
mv /var/lib/postgresql/<major version>/<name> /var/somewhere-save
mkdir /var/lib/postgresql/<major version>/<name>

# 3. Restore full content from backup
pgbackrest --stanza=<major version>-<name> restore

# 1. Stop the cluster
pg_ctlcluster <major version> <name> stop

# 2. Restore full content from backup
pgbackrest --stanza=<major version>-<name> --delta restore

pgbackrest --stanza=<major version>-<name> --type=time --target="<ISO timestamp>" restore

4.12.1. Additional Resources¶

• http://www.pgbackrest.org/user-guide.html
• http://www.pgbackrest.org/command.html
• http://www.pgbackrest.org/configuration.html

4.12.1. Additional Resources¶

• https://github.com/shellinabox/shellinabox

4.11.1. Usage¶

Start tmate (opens a new terminal)

tmate

Show the credentials which need to be given to a third party (securely)

tmate show-messages

For further usage see the following additional resources regarding tmux.

4.12.1. Additional Resources¶

• https://tmate.io/
• https://man.openbsd.org/OpenBSD-current/man1/tmux.1

4.12.1. Additional Resources¶

• https://etckeeper.branchable.com/
• https://www.thomas-krenn.com/de/wiki/Etc-Verzeichnis_mit_etckeeper_versionieren