FirebirdSQL logo

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.

Note

In each of the following examples, the -⁠user sysdba and -⁠password whatever parameters on the command lines have been replaced by {...}.When executing these commands, any remote gbak commands will require to have them specified unless the firebird user on the remote database(s) has ISC_USER and ISC_PASSWORD defined in the .profile or .bashrc (or equivalent) login files.However, that is a seriously bad idea and incredibly insecure.

tux> # Clone a test database to a different server, without requiring a backup file.
tux> gbak -backup employee stdout | \
ssh firebird@tuxrep "gbak {...} -r o stdin emptest"

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.

tux> # Clone a test database to a different server, without requiring a backup file.
tux> gbak -backup employee stdout | \
ssh firebird@tuxrep "gbak {...} -r o stdin emptest"

firebird@tuxrep's password:

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

tux> isql {...} tuxrep:emptest

Database:  tuxrep:emptest

SQL> show database;

Database: tuxrep:emptest
        Owner: SYSDBA
PAGE_SIZE 4096
...

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

tux> ssh firebird@tuxrep "gbak -backup {...} emptest stdout" | \
gbak -create stdin data/tuxrep_emptest.fdb

firebird@tuxrep's password:

tux> ls data

employee.fdb  tuxrep_emptest.fdb

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

tux> isql data/tuxrep_emptest.fdb

Database:  data/tuxrep_emptest.fdb

SQL> quit;

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

tux> ssh firebird@tuxrep "gbak -backup {...} emptest stdout" |  \
ssh firebird@tuxqa "gbak -create {...} stdin data/tuxrep_empqa.fdb"

firebird@tuxrep's password:
firebird@tuxqa's password

tux> ssh firebird@tuxqa "ls data"

employee.fdb  tuxrep_empqa.fdb

Using External Tools

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 database alias must start in column one.

  • There must be a single space before the equals sign (=).

  • There must be a single space after the equals sign (=).

  • Double quotes around the database filename is not permitted — it doesn’t work for the Firebird utilities either.

  • Databases are all single file databases.

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.

tux> cat /opt/firebird/aliases.conf
# ---------------------------------------------------------
# WARNING: Backup Standards require that:
#          The database name starts in column 1.
#          There is a single space before the equals sign.
#          There is a single space after the equals sign.
#          The path has no double quotes (they don't work!)
# ----------------------------------------------------------
employee = /opt/firebird/examples/empbuild/employee.fdb

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.

tux> # Backup the production employee database using gzip.
tux> gfix -shut -tran 60 employee
tux> DBFILE=`grep -i "^employee =" /opt/firebird/aliases.conf | cut -d" " -f3`
tux> gzip -9 --stdout $DBFILE > /backups/employee.fdb.gz

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

tux> # Restore the production employee database from a gzip backup.
tux> gfix -shut -tran 60 employee
tux> DBFILE=`grep -i "^employee =" /opt/firebird/aliases.conf | cut -d" " -f3`
tux> gunzip --stdout /backups/employee.fdb.gz > $DBFILE

tux> # Make sure firebird can see the file.
tux> chown firebird:firebird $DBFILE

A Simple Backup & Restore

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.

tux> # Backup the database.
tux> gbak -backup employee /backups/employee.fbk

tux> # Restore the database.
tux> gfix -shut -tran 60 employee
tux> gbak -recreate overwrite /backups/employee.fbk employee

Metadata Only

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.

tux> #Backup only the database metadata.
tux> gfix -shut -tran 60 employee
tux> gbak -backup -meta_data employee employee.meta.fbk

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.

tux> #Restore only the database metadata.
tux> gbak -create employee.fbk mytest.fdb -meta_data

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.