Backing Up and Restoring V5 Databases

Version Relevance: V5

Issue: In previous versions of Caliach Vision, we have arranged for our datafile segments to be backed up using standard operating system (OS) file backup procedures. What do we need to know about backing up data in V5?

Background: From V5 onwards, Caliach Vision data are no longer held in an Omnis Native Datafile (OND), but stored instead in a PostgreSQL or MySQL (including MariaDB) database, managed by the appropriate database engine, or, for single-user and demonstration systems, stored in a SQLite database file. This means you can no longer backup your data by simply copying the Caliach Vision datafile segments *.df1, *.df2 etc onto a backup medium, using the server OS file copying functions. A fuller discussion of the changes in data storage and implications for support can be found in the article on migrating data at: Migrating Data from V4.1 to V5.

Aug 27th, 2015 - Updated Aug 22nd 2017

Feedback: This article discusses the options for backing up and restoring Caliach Vision V5 data managed by the PostgreSQL and MySQL database engines, with simple examples for each. (For single-user installations using the SQLite database, the single *.db database file containing your data can simply be copied in the same manner as pre-V5 OND datafile segments).

For sites using Caliach Vision versions before V5, customer internal or external IT support is typically responsible for ensuring that OND datafile segments are regularly and safely backed-up, along with any other critical files maintained by the organisation. The same principle applies with V5, but instead of competence limited to server OS file copying and back-up programs, a working knowledge of the relevant database engine functions and/or related third-party tools is also required.

Backing Up the Database

There are three main modes of back-up typically employed for SQL databases (this is PostgreSQL terminology, but similar concepts apply for MySQL) - File System Level Backup; Continuous Archiving and Point-in-Time recovery (PITR); or the use of a “Dump” command-line application installed by default along with the rest of the database engine applications.

The first two options however typically only support full restoration of the entire database, not individual tables, and they both require the backup images to be from the same version of the database engine that you are currently restoring to. The first option also requires a full shut-down of the database engine server instance (or at least locking and flushing tables) during backup and restore.

An explanation for PostgreSQL can be found here: http://www.postgresql.org/docs/9.4/static/backup.html.

An explanation for MySQL can be found here: http://dev.mysql.com/doc/refman/5.6/en/backup-methods.html.

Use of the appropriate Dump program however (pg_dump for PostgreSQL, or mysqldump for MySQL), has the following advantages: -

  1. Backup can take place while the database engine is running and users connected.
  2. The backup is an internally consistent snapshot of the database taken when the backup starts.
  3. Backups can be restored to later (but not earlier) versions of the database engine.
  4. In some circumstances, it is possible to restore individual tables.

Note: While you can backup the database while other users are connected (1 above), it is best practice to backup at a known point when all users are disconnected, say at the end of a working day or shift, so that if a restore is necessary users will know what work to reproduce following a restore.

Documentation for pg_dump can be found here: http://www.postgresql.org/docs/9.4/static/app-pgdump.html.

Documentation for mysqldump can be found here: https://dev.mysql.com/doc/refman/5.6/en/mysqldump-sql-format.html.

As for pre-V5 OND datafiles, it is safer for SQL database backups to be carried out via scheduled batch file scripts (.bat files) or dedicated applications, avoiding accidental deletions in manual backups using admin tools, or missed events. There are a range of backup applications available for PostgreSQL and MySQL, which can be explored by browsing the web or discussing with your IT support. (Manual backing up can be useful for system administrators while setting up or testing, and will be explored later on in this article).

Note: Cutting and pasting script commands from applications such as word processors can result in '-' and '–' delimiters not being recognised by command-line tools. Make sure these are re-typed in the window itself, or within a simple text program such as Windows Notepad.

In the example scripts throughout this article, substitute the full path of the folder in which the backup file will be created or has been saved in place of {BackupFolderPath}, and the database name in place of {DatabaseName} (leaving no brackets).

Below is an example script for use in a Windows .bat file, in conjunction with Task Scheduler, to regularly backup a PostgreSQL database: -

FOR /F "skip=1 tokens=1-6" %%A IN ('WMIC Path Win32_LocalTime Get Day^,Hour^,Minute^,Month^,Second^,Year /Format:table') DO (
    if "%%B" NEQ "" (
        SET /A FDATE=%%F*10000+%%D*100+%%A
        SET /A FTIME=%%B*10000+%%C*100+%%E
    )
)
if %FTIME% LSS 100000 (SET FTIME=0%FTIME%)
set PGPASSWORD={password}
cd C:\Program Files\PostgreSQL\9.4\bin
pg_dump.exe -Fc -f  "{BackupFolderPath}\{DatabaseName}_%FDATE%_%FTIME%.backup" -U {pguser} -p 5432 -h localhost {DatabaseName}
FORFILES /p {BackupFolderPath} /d -{NN} /m * /c "cmd /c del @file"

where {password} is the plain-text password used by the PostgreSQL user {pguser} on host localhost, port 5432, who must be at least a super-user with full read-access. {DatabaseName} is the internal database name that is being backed up. The output of pg_dump will be to file, and the format will be custom. The backup date and time will be appended to the backup name. The FORFILES command will delete all backups older than {NN} days, so don't store your .bat file in the same folder the backups are stored!

NOTE: The above code should be able to normalise the date and time across all server date/time localization formats. E.G. give a resulting backup file with a name of {DatabaseName}_20170824_062414.backup

Here is a similar example, this time of a script for backing up a MySQL database: -

cd C:\Program Files\MySQL\MySQL Server 5.6\bin
mysqldump.exe --host=localhost --port=3306 --user=caliach --password=password {DatabaseName} > "{BackupFolderPath}\{DatabaseName}_%date:~-4,4%%date:~-7,2%%date:~-10,2%_%time:~0,2%%time:~3,2%.sql"
FORFILES /p {BackupFolderPath} /d -7 /m * /c "cmd /c del @file"

where user “caliach”, on the MySQL server at host localhost on port 3306, has a password of “password”, and the database to be backed up is {DatabaseName}. Once again the date and time will be appended to the backup file name, and FORFILES will delete any files in the back-up folder older than 7 days.

In both these examples, it is assumed that the script can only be accessed by the system administrator to keep the passwords secure. You must also make sure that the backup files are regularly backed up to another storage medium and/or the cloud, for extra security and to preserve older backups. It is possible in some environments to build an email notification of successful backup into the scripts, otherwise daily checks for successful backups should be made, or a third-party backup application that does support email notification used instead.

While Caliach Ltd cannot endorse or guarantee results from any specific applications, one application that is widely used for backing up MySQL databases and has successfully carried out scheduled backups on our TrainingDemo database is MySQLBackupFTP: http://mysqlbackupftp.com/ which depending on version allows backup to a variety of cloud services as well as folders, and includes email notification.

Restoring MySQL and PostgreSQL databases

Backups for both database platforms can be restored using command-line-driven applications installed by default along with the rest of the database engine applications, preferably in a batch file script; or by using the database administration tools also provided in the install set. In both cases restore should only take place with all users off the system, and after creating a verified copy of the live database using the Caliach Vision function: File--Advanced--Create New Database and Copy Data.

You will also need to either rename (which may not be possible with MySQL depending on the tools available) or if necessary Drop (delete) the original database using an appropriate database administration tool; and then create a new empty database with the same name using the administration tool, ready for the data to be restored into – see the next section for examples of these tasks.

For MySQL the program to use for restore is mysql: http://dev.mysql.com/doc/refman/5.6/en/mysql.html and a command-line restore or batch file script can be as simple as: -

cd C:\Program Files\MySQL\MySQL Server 5.6\bin\
mysql.exe -u root -ppassword {DatabaseName} < {BackupFolderPath}\{DatabaseName}_20152008_1453.sql

assuming the user “root” has a password of “password”. The SQL script contained in “{DatabaseName}_20152008_1453.sql” will be executed by mysql.exe, populating the database.

For PostgreSQL the program to use is a dedicated restore utility, called pg_restore: http://www.postgresql.org/docs/9.4/static/app-pgrestore.html where the command-line restore or batch file script may look like this: -

set PGPASSWORD=password
cd c:\Program Files\PostgreSQL\9.4\bin
pg_restore.exe –-username=caliach --dbname={DatabaseName} {BackupFolderPath}\{DatabaseName}_20150825_2230.backup

where the user is “caliach”, and the backup file is {DatabaseName}_20150820_2230.backup.

Using Database Administration Tools to Backup, Delete, Rename and Restore your Database

PostgreSQL

The database administrator program installed with PostgreSQL 9.4 is called pgAdmin III, and can be found in the Start menu folder tree for the database installation. These examples assume you or your IT support have run the program and set up a connection to the database engine already. When you open pgAdmin III and expand the Object browser tree view, you should see your server engine instance and databases: -

pgAdmin III

If you right-click on your database, you select the menu item 'Backup...' to backup the database, enter the filename and other options (defaults are usually adequate for simple backup) and click 'Backup'. This is useful for one-off administrator-only backup jobs.

To rename the database, select 'Properties...' from the menu, edit 'Name' and click OK; here we can see the last version of the database, renamed caliachofficelast, also in the list. As you can rename the old database, there is no need to delete or 'Drop' it.

Note: If you wish to be able to access a re-named datafile from Caliach Vision, you must also expand the Object browser entry for your database down to the Schemas level, right-click on the schema with the same name as the database, select 'Properties...' again, and edit 'Name' as before. This brings the schema name in line with the database name.

Before you can restore, you need to create a new, empty database by right-clicking on the 'Databases' folder icon in the Object browser, and selecting 'New Database...', entering the name of the database to be re-created in 'Name' and clicking 'OK'.

To restore the database from a backup file, right-click on the new database, and select 'Restore...', entering or browsing for your backup file in 'Filename', and clicking 'Restore'. You can edit the other options, but for a simple backup the defaults are adequate.

You can find more on the pgAdmin III tool here: http://www.pgadmin.org/docs/1.16/index.html.

MySQL

The database administrator program installed with MySQL 5.6 is called MySQL Workbench, which can be found in the Start menu folder tree for the database installation. These examples assume you or your IT support have run the program and set up a connection to the database engine already. When you open MySQL and connect to your server instance, you should see your databases (called 'SCHEMAS' here) in the Navigator: -

MySQL Workbench

To backup a database, select 'Data Export' from the 'Management' section of the Navigator, select the database to export and select 'Export to Self-Contained File', entering or browsing for your file path. When you click 'Start Export', the database will be exported to the file. This is useful for one-off administrator-only backup jobs.

You cannot safely rename a database using MySQL Workbench, so it is necessary to 'Drop' (delete) the database before creating a new one to populate with your backup file data. Make sure you have taken and verified a copy of the datafile using Caliach Vision as described above, before you do this. (Additionally, you could also do a fresh backup and restore to a new database with a different name created for the purpose, and verify that before continuing.) To drop the database, right-click on it in the Navigator and select 'Drop Schema'; confirming when asked.

With MySQL Workbench you can create a new database and restore in one operation. Select 'Data Import/Restore' from the 'Management' section of the Navigator, select 'Import from Self-Contained file', enter or browse for your file path, and under 'Default Schema to be Imported To' click 'New...' to enter the database (schema) name of your database. When you click 'Start Import' the database will be created and populated with the data from your backup file.

You can find more on the MySQL Workbench tool here: https://dev.mysql.com/doc/workbench/en/.

VACUUM ANALYZE and REINDEX for PostgreSQL databases

VACUUM ANALYZE for PostgreSQL is recommended periodically. With it's advanced MVCC system tables can become bloated with deleted or updated data rows and VACUUM is the mechanism to recover disk space and subsequently optimise performance. See PostgreSQL documentation for more details. For small databases you can rely on the standard default autovacuum settings to perform this when needed. But with larger more intensive databases you could well improve overall performance by running a database-wide VACUUM ANALYSE on a regular basis at a time of low usage, say weekly at night. To do that you can use the following script in a .bat file in conjunction with Task Scheduler.

cd "c:\Program Files\PostgreSQL\9.4\bin"  #full path of the PostgreSQL instance bin folder
set PGHOST=localhost                      #host address
set PGPORT=5432                           #port number
set PGUSER={username}                     #PostgreSQL username with high enough privileges
set PGPASSWORD={password}                 #make sure this batch file is in a secure location, otherwise use .pgpass
set PGDATABASE={databasename}             #name of the database
vacuumdb --analyze                        #runs vacuumdb.exe with parameter --analyze

REINDEX for PostgreSQL is recommended periodically. When tables have many inserts and deletes index pages can progressively become fragmented and this can slow index scans. You can improve overall performance of the system by running a database-wide REINDEX on a regular basis, say monthly for a very active site, at a time of low usage, say at night. To do that you can use the following script in a .bat file in conjunction with Task Scheduler, or add the final line below to the script for VACUUM above.

cd "c:\Program Files\PostgreSQL\9.4\bin"  #full path of the PostgreSQL instance bin folder
set PGHOST=localhost                      #host address
set PGPORT=5432                           #port number
set PGUSER={username}                     #PostgreSQL username with high enough privileges
set PGPASSWORD={password}                 #make sure this batch file is in a secure location, otherwise use .pgpass
set PGDATABASE={databasename}             #name of the database
reindexdb                                 #runs reindex.exe which takes environment variables as parameters

Chris Ross - Senior Cosultant