Remote Backups And Restores Using SSH

As shown above, you can use the special filenames stdin and stdout to backup and restore a database to a separate database on the same server.However, you can also use the same tools, over an SSH connection to a remote server, and pass the backup of one database directly to a restoration of a separate one.

The first example copies a local database to a remote server where Firebird is running and the firebird user has its environment set up so that the gbak tool is on $PATH by default, on login.

When the above is executed, you will be prompted for a password for the remote firebird user on server tuxrep, assuming that you don’t have a proper SSH key-pair already set up and active.The command will replace the local database according to the alias name emptest, but you can, if required, supply full path names for the databases.The following shows an example of the above being executed.

As you can see, there’s not much in the way of output, but you can connect remotely and check:

The next example, shows a remote database being backed up to a local one, in a similar manner.

You can see that a new tuxrep_emptest.fdb database has been created.Does it work?Checking with isql shows that it does.

The final example shows how to backup a remote database on one server, to a remote database on another.

Gbak and nbackup are the best tools to use when backing up and/or restoring Firebird databases.They have been extensively tested and know the internals of the database and how it works, so the chances of these tools corrupting your valuable data are very slim.However, some DBAs still like to use external tools (those not supplied with Firebird) to make backups for whatever reason.

Because it is not possible for external tools to know where a database is to be found, given the alias name, the scriptwriter and/or DBA must explicitly find out the correct location of the database file(s) and supply these to the external tool.To make this simpler for scriptwriters, my own installation uses a standard in my aliases.conf file as follows:

The last rule applies to my installation only and means that the following simple backup script will work.If multiple file databases were used, more coding would be required to take a backup using external tools.

The following shows the use of the gzip utility on a Linux server to take and compress a backup of a running database.The following is run as the root user due to the requirement to run gfix to shut down the database.

The restore process for this database would be the reverse of the above.Again, the following runs as root.

This example takes a backup, then immediately overwrites the original database using the new backup.This is not normally a good idea as the first action of the -⁠recreate overwrite is to wipe out the database.

It is possible to use gbak to recreate an empty database containing only the various domains, tables, indices and so on, of the original database but none of the data.This can be useful when you have finished testing your application in a test environment and wish to migrate the system to a production environment, for example, but starting afresh with none of your test data.

When the above backup file is restored on the production server, only the metadata will be present.

There is another way to create a database with no data and only the metadata.Simply restore from an existing backup which contains the data and supply the [gbak-cmdline-meta-data] switch to the restore command line.The database will be restored but none of the original data will be present.

The [gbak-cmdline-meta-data] switch can be used on either a backup or a restore to facilitate the creation of a clone database (or overwrite an existing one) with no actual data.

The gsplit filter application, documented in its own manual, doesn’t actually work anymore.This filter was supplied with old versions of InterBase and Firebird to allow large database backups to be split over a number of files so that file system limits could be met.Such limits could be the size of a CD, the 2GB limit on individual file sizes on a DVD, where some Unix file systems have a 2 GB limit and so on.

Gbak allows the backup files to be split into various sizes (with a minimum of 2048 bytes) and will only create files it needs.

The sizes after each filename indicate how large that particular file is allowed to be.The default size is bytes, but you can specify a suffix of k, m or g to use units of kilo, mega or gigabytes.

If the backup completes before writing to some files, then those files are not created.A backup file is only ever created when it must be.

The size of the final backup file will be quietly ignored if the database has grown too large to allow a truncated backup to complete.If, in the example above, the backup needs a total of 1500M, then the last file would be written to a final size of 900m rather than the 600m specified.

To restore such a multi-file backup requires that you specify all filenames in the backup and in the correct order.The following example shows the employee database above being restored from the two files backed up above:

Normally the ODS used is the one in force by the version of Firebird used to restore the database.So, the examples above will actually change the ODS when the database is restored.The backup should be taken using the gbak utility supplied by the old ODS version of Firebird.The restore should be carried out using gbak from the newer version of Firebird.

After the above, the old 2.0 Firebird database will have been recreated — wiping out the old database — as a Firebird 2.1 database with the corresponding upgrade to the ODS from 11.0 to 11.1.

The script setenv_firebird is not supplied with Firebird and simply sets PATH, etc., to use the correct version of Firebird as per the supplied parameter.

The default database cache (page buffer) is set when the database is created, or subsequently by using gfix -⁠b[uffers] <number-of-pages>.Gbak can restore a database and set the default cache size as well.The process is as follows:

The default cache size is used when the number of buffers is zero, as in the first example above.Gbak allows this to be changed if desired.Gbak, however, cannot set the cache size back to zero.You must use gfix to do this.

Similar to the example above to change the default database cache size, the database page size can also be changed using gbak.

Sometimes you do not want your reporting staff running intensive queries against your production database.To this end, you can quite easily create a clone of your production database on a daily basis, and make it read-only.This allows the reporting team to run as many intensive reports as they wish with no ill effects on the production database, and it prevents them from inadvertently making changes.

The following example shows the production employee database running on Linux server tux, being cloned to the reporting team’s Linux server named tuxrep.First on the production tux server:

Then on the reporting team’s tuxrep server:

You may use gbak to create a clone of a database, on the same server, without needing to create a potentially large backup file.To do this, you pipe the output of a gbak backup directly to the input of a gbak restore, as follows.

You will notice that the output filename for the backup is stdout and the input filename for the restore is stdin.This ability to pipe standard output of one process to the standard input of another, is how you can avoid creating an intermediate backup file.The commands above assume that there are suitable alias names set up for both emptest and emptest_2.If not, you will need to supply the full path to the two databases rather than the alias.

The -⁠r o option on the restore process will overwrite the database name specified — as an alias or as a full path — if it exists and will create it anew if it doesn’t.

If you don’t want to overwrite any existing databases, use [gbak-cmdline-create-database] which will only create a database if it doesn’t already exist, and will exit with an error if it does.In POSIX compatible systems, the error code in $? is 1 in this case.

Further examples of backing up and restoring remote databases over ssh, using the stdin and stdout filenames, can be seen below.

If you do not specify a mode switch such as -⁠b[ackup] or -⁠c[reate] etc, then gbak will perform a backup as if the -⁠b[ackup] switch had been specified — provided that the other switches specified are correct for a backup.

Only a SYSDBA, a user with the RDB$ADMIN role, the owner of a database, or a user with the USE_GBAK_UTILITY system privilege can take a backup of the database.However, any authenticated user can restore a database backup using the -⁠c[reate] switch (in Firebird 3.0 and higher, this user will need the CREATE DATABASE DDL privilege).This means that you must make sure you prevent your backup files from falling into the wrong hands because there is nothing then to stop unauthorised people from seeing your data by the simple process of restoring your backups onto their server.

The database restore will fail, of course, if the user carrying it out is not the database owner and a database with the same filename already exists.

The -⁠y suppress_output switch is supposed to cause all output to be suppressed.Similar in fact to running with -⁠v[erify] not specified.However, all it seems to do is cause the output (according to the -⁠v[erify] switch setting) to be written to a file called suppress_output, however this only works once because the next run of gbak with -⁠y suppress_output will fail because the file, suppress_output, already exists.

It is possible that this problem was introduced at version 2 for Firebird, because both 2.0 and 2.1 versions actually use the -⁠y suppress switch rather then -⁠y suppress_output.Using this (shorter) option does work as intended and the output is indeed suppressed.

If you specify a log filename with the -⁠y <log file> switch, and the file already exists, then even though the firebird user owns the file, and has write permissions to it, gbak cannot overwrite it.You must always specify the name of a log file that doesn’t exist.On Linux systems, the following might help:

The above is quite useful in as much as it prevents you from overwriting previous backups that may be required.The downside is that you now need to introduce a housekeeping system to tidy away old, unwanted backups to prevent your backup area filling up.

Gbak recognizes the literal strings stdin and stdout as source or destination filenames.In POSIX systems, when the standard input and/or standard output channels are used, it is not permitted to execute seek operations on these channels.Using stdin or stdout as filenames with gbak will force gbak to use processing that will not seek on the input or output channels, making them suitable for use in pipes — as per the examples in the recipes section above.

These filenames, while they appear to be POSIX names, are definitely not synonyms for /dev/stdin or /dev/stdout, they are simply literals that gbak checks for while processing its parameters.Do not attempt to use names /dev/stdin or /dev/stdout in a piped process as it will most likely fail.

If you wish to create a backup file actually named either stdin or stdout, then you should specify the filename as a full, or relative, path name such as ./stdin or ./stdout, which causes gbak to treat them as a literal filename rather than a special filename that causes different to normal processing during the backup or restore process.