Maintenance

In this chapter we describe maintenance tasks on the IT side. Maintenance tasks for tutors (purging old results, users, removing images, etc.) are described in the chapters dedicated to the tutor user interface.

As the administrator of a PiAnoS instance, you are in charge of:

  • managing the database (backing up, restoring)

  • Upgrading the software to a new version, which is described in the next chapter

General considerations

PiAnoS stores all data (users, exercises, images, results, etc.) in the database. Except for The configuration file, nothing is stored on the filesystem directly. Therefore, backing up PiAnoS consists in two things:

  • back-up the configuration file (backend/settings-local.php)

  • dump the database

Since you may not be experimented with the administration of PostgreSQL database, this chapter covers the essential steps involved in making sure you have a usable backup of your data.

IMPORTANT

In this documentation, you may see something like “make sure you perform a backup first” or, even sneakier, “this is a dangerous operation”. In all those cases, do a backup before you do anything. In general, a backup takes a few minutes, and saves your life. So, launch the backup, get a cup of coffee and plan your intervention in the meantime.

EVEN MORE IMPORTANT

  • Even if you do nothing dangerous, take regular backups. Like one per week. Or one per day during intensive sessions.

  • Store your backups on a different medium. It does not matter if it’s a USB key or a backup server with tapes. This will cover the cases where the hardware screws up.

  • Keep backups on the production system as well. This will cover the cases where you screw up.

  • Test your backup. Of course, you are going to test the first one (yes, you really need to), when designing your backup policy (yes, you really need one). But after one or two weeks, pick a random backup and test it. When things break, users loose their data, and you are under a lot of pressure. You need to know how to restore a backup, and you need to be sure your backups are worth relying on. If you know how to do, you will stay calm and confident.

Backing up the database

Note

Backing up the configuration file is as simple as copying the file to a backup location. Therefore, in this section we only address the problem of backing up the database.

This section is part of a possible backup plan. Backup and restore is a complex topic, extensively covered in the relevant chapter of the PostgreSQL manual.

Using the command line

The utility that performs a backup is called pg_dump. It is part of PostgreSQL and is typically available as /usr/bin/pg_dump on Linux systems, and C:\Program Files (x86)\PostgreSQL\8.4\bin\pg_dump.exe on Windows. This utility connects to a database and dumps its content to the terminal - be sure to use the I/O redirection operator > to write to a file!

The output of pg_dump can be set to several formats (plain text, compressed or tar archive) That said, unless you want to be able to manually edit the SQL dump, use the -Fc option (see examples below) to produce “standard”, compressed PostgreSQL dumps.

Examples below assume that the database name is pianos, and that the database user is pianos as well. Both commands will require the password to be given if the connection is not set to trust in your pg_hba.conf file 1. They also use timestamps 2 to flag the backups, as this is a very common pattern.

Example 1: on Linux, running pg_dump on the same system as the database server:

$ pg_dump -Fc -h localhost -U pianos pianos > /path/to/backup/pianos-`date "+%Y-%m-%d-%H%M%S"`.backup
$ cd /path/to/backup
$ ls
pianos-2013-05-13-161101.backup

Example 2: on Windows, connecting to the remote server pianos.example.com :

"C:\Program Files (x86)\PostgreSQL\8.4\bin\pg_dump.exe" -Fc -h pianos.example.com -U pianos pianos > drive:\path\to\backup\pianos-%date%.backup
cd /D drive:\path\to\backup
dir

Volume in drive C is OS
Volume Serial Number is ABCD-1234

Directory of drive:\path\to\backup

       13.05.2013  16:13    <DIR>          .
       13.05.2013  16:13    <DIR>          ..
       13.05.2013  16:13          xxxxxxxx pianos-13.05.2013.backup

Using pgAdmin III

pgAdmin III is available on all platforms that have a graphical user interface (Windows, Ubuntu Linux, OSX, etc.) Use this method if you feel more comfortable with GUI-based applications.

  • Start pgAdmin III (from your start menu)

  • In the Object Browser, expand the “Databases” node

  • Right-click “pianos” and select “Backup…”

    • Choose an appropriate filename (see above)

    • Choose the “Custom” format

    • Leave the encoding blank (will use database default)

    • Choose the “pianos” rolename

    • Leave all other options to their defaults

  • Click “Done”

Restoring the database

Note

Restoring the configuration file is as simple as copying the file from a backup location. Therefore, in this section we only address the problem of restoring the database.

Using the command line

The utility that performs a restore is called pg_restore. It is part of PostgreSQL and is typically available as /usr/bin/pg_restore on Linux systems, and C:\Program Files (x86)\PostgreSQL\8.4\bin\pg_restore.exe on Windows. This utility does the opposite of pg_dump.

There are no specific options to pass, as pg_restore will figure out everything from the backup file itself (if it was created with the -Fc option). The big difference is that the database must be created beforehand, and it must be empty (otherwise the consequences are hard to predict).

First, you need to create a database, as you did previously in Step-by-step installation on Debian Linux - note that only the database needs to be created if you are restoring to the same cluster. Otherwise, you need to create the user as well.

So, assuming the user pianos already exists on the cluster:

$ sudo su postgres
$ createdb --owner=pianos --encoding=utf-8 pianos
$ createlang plpgsql -d pianos
$ exit

Now that the database is ready, launch pg_restore:

$ cd /path/to/backup
$ ls
pianos-2013-05-13-161101.backup
$ pg_restore -h localhost -U pianos -d pianos pianos-2013-05-13-161101.backup

Using pgAdmin III

We assume that restoration happens on the same cluster. If this is not the case, you need to create the pianos user as explained in Step-by-step installation on Windows. As in the command line case, we are going to create a new, empty database and restore the backup into it.

  • Start pgAdmin III (from your start menu)

  • In the Object Browser, right-click “Databases” and choose “New Database …”

  • Enter a name, select “pianos” for the owner, check that encoding is UTF8

  • Right-click the newly created database and select “Restore…”

  • Browse for the backup file

  • Click OK

Testing your backup plan

Note

Despite all the warnings above, you might not have the resources (in terms of equipment, storage, accesses, etc.) to have the ideal, automated backup plan. In this case, it might be sufficient for you to manually perform backups at some fixed (or random, it is your data after all) interval. However, you should still read this part.

As said earlier, you must make sure you backups are functionning properly - otherwise they are just a plain waste of time 3.

  • Make sure no users are connected, because if anything fails they will get angry

  • Choose a backup from your final storage area (or manually do one now)

  • Restore the backup in another database (e.g. name it pianos_backup_test)

  • Modify your settings-local.php to use pianos_backup_test instead of pianos

  • Connect to PiAnoS normally. If you see the slightest sign of a difference with the version that was backed up, you have a problem. Indeed, you also have a problem if you can’t connect at all, or see errors popping here and there.

  • Rollback your changes to settings-local.php, pointing it to the pianos database again

  • You are safe, for now

If you want to be safe forever, you have one last thing to do: verify that automation works, reliably. You can use cronjobs with email alerts, or dedicated backup software with an expensive monitoring solution, but at any rate, check this out.

Footnotes

1

The Step-by-step installation on Debian Linux shows how you can allow such connections. The complete reference can be found in the PostgreSQL documentation.

2

Although it is possible to have both the date and the time in the filename, Windows require (sigh) some tweaks because timestamps contain columns (:) that are not valid within a filename. Therefore on Windows only the date is added to the filename. Please refer e.g. to this discussion on StackOverflow for a workaround.

3

You might think I am overestimating the importance of all this. I may be. But this stuff is like a car insurance. You might be a good driver, but some day, you will smash your car, statistically. Do you really want to take the risk of driving without an insurance ?