<< Security of backups | Firebird Backup & Restore Utility | gbak caveats >>

Backup & restore recipes

The following recipies show examples of backup and restore tasks using gbak. These are probably the commonest cases that you will encounter as a DBA. All the examples use the employee database supplied with Firebird and the actual location is correctly configured in aliases.conf. Each of the following recipies is run with the assumption that the environment variables ISC_USER and ISC_PASSWORD have been given suitable values.

Backup & restore prerequisites

If you replace an open and running database, there is a good chance that you will corrupt it. For best results and minimal chance of corrupting a database, you should close it before replacing it. To close a database, use as follows:

 tux> gfix -shut -tran 60 employee

The example above prevents any new transaction from being started which prevents new queries being executed or new sessions connecting to the database. It will wait for up to 60 seconds for everyone to logout and for all current transactions to complete before shutting down the database. If any long running transactions have not completed by the end of 60 seconds, the shutdown will timeout and the database will remain open.

Note: After the restore of the database has completed, the database will automatically be opened again for use.

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 a restore 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 -replace overwrite /backups/employee.fbk employee

back to top of page

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 dump 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 dump which contains the data and supply the -m[eta_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 -m[eta_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.

back to top of page

Splitting the backup

The gsplit? filter application, documented in its own manual, doesn't actually work any more. 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 dump files to be split into various sizes (with a minimum of 2048 bytes) and will only create files it needs.

 tux> # Backup the database to multiple files.
 tux> gbak -backup employee /backups/emp.a.fbk 600m /backups/emp.b.fbk 600m

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 dump completes before writing to some files, then those files are not created. A dump file is only ever created when it must be.

The size of the final dump 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 of the filenames in the dump and in the correct order. The following example shows the employee database above being restored from the two files dumped above:

 tux> # Restore the database from multiple files.
 tux> gfix -shut -tran 60 employee
 tux> gbak -replace /backups/employee.a.fbk /backups/employee.b.fbk employee

back to top of page

Change the ODS

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 InterBase^® or Firebird. The restore should be carried out using gbak from the newer version of Firebird.

 tux> setenv_firebird 2.0
 Firebird environment set for version 2.0.

 tux> # Check current ODS version (as root user!)
 tux> gstat -h employee|grep ODS
         ODS version             11.0

 tux> # Backup the (old) database.
 tux> gbak -backup employee /backups/employee.2_0.fbk

 tux> setenv_firebird 2.1
 Firebird environment set for version 2.1.

 tux> # Recreate the database and upgrade the ODS.
 tux> gfix -shut -tran 60 employee
 tux> gbak -replace overwrite /backups/employee.2_0.fbk employee

 tux> # Check new ODS version (as root user!)
 tux> gstat -h employee|grep ODS
         ODS version             11.1

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.

back to top of page

Change the cache size

The default database cache is created when the database is created, or subsequently by using gfix. gbak can restore a database and reset the default cache size as well. The process is as follows:

 tux> # Check current cache size (as root user!)
 tux> gstat -h employee | grep -i buffer
         Page buffers            0

 tux> # Restore the database & change the cache size.
 tux> gfix -shut -tran 60 employee
 tux> gbak -replace overwrite -buffer 200 /backups/employee.fbk employee

 tux> # Check the new cache size (as root user!)
 tux> gstat -h employee | grep -i buffer
         Page buffers            200

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.

Change the page size

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

 tux> # Check current page size (as root user!)
 tux> gstat -h employee | grep -i "page size"
         Page size               4096

 tux> # Restore the database & change the page size.
 tux> gfix -shut -tran 60 employee
 tux> gbak -replace overwrite -page_size 8192 /backups/employee.fbk employee

 tux> # Check the new page size (as root user!)
 tux> gstat -h employee | grep -i "page size"
         Page size               8192

back to top of page

Create a read-only database clone

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:

 tux> # Backup the production database.

 tux> gbak -backup employee /backups/employee.fbk

Then on the reporting team's tuxrep server:

 tuxrep> # Scp the dump file from tux.
 tuxrep> scp fbuser@tux:/backups/employee.fbk ./
 Using keyboard-interactive authentication.
 Password:
 employee.fbk              |         19 kB |  19.3 kB/s | ETA: 00:00:00 | 100%

 tuxrep> # Restore the employee database as read-only.
 tuxrep> gfix -shut -tran 60 employee
 tuxrep> gbak -replace overwrite -mode read_only employee.fbk employee

 tuxrep> # Check database mode (as root user)
 tuxrep> gstat -h employee|grep -i attributes
         Attributes              no reserve, read only

back to top of page

Create a database clone without a dump file

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

 tux> # Clone a test database to the same server, without requiring a dump file.
 tux> gbak -backup emptest stdout | gbak -replace stdin emptest_2

You will notice that the output file name for the backup is stdout and the input file name 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 dump 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 -replace 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. You may also use the -recreate overwrite option as an alternative. Both have the same result.

If you don't want to overwrite any existing databases, use -create 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 file names, can be seen below.

back to top of page

Backup & restore with & without shadow files

Databases can have shadow files attached in normal use. gbak happily backs up and restores those as well and in normal use, shadow files will be recreated. Should you wish to restore the database only and ignore the shadows, gbak can do that for you as the following example shows.

 tux> # Check current shadows, use isql as gstat is broken.
 tux> isql employee

 Database:  employee
 SQL> show database;
 Database: employee
         Owner: SYSDBA
  Shadow 1: "/opt/firebird/shadows/employee.shd1" manual
  Shadow 2: "/opt/firebird/shadows/employee.shd2" manual
 ...

 SQL> quit;

 tux> # Restore the database preserving shadow files.
 tux> gfix -shut -tran 60 employee
 tux> gbak -replace overwrite /backups/employee.fbk employee

 tux> # Check shadows again, use isql as gstat is broken.
 tux> isql employee

 Database: employee
 SQL> show database;
 Database: employee
         Owner: SYSDBA
  Shadow 1: "/opt/firebird/shadows/employee.shd1" manual
  Shadow 2: "/opt/firebird/shadows/employee.shd2" manual
 ...

 SQL> quit;

 tux> # Restore the database killing shadow files.
 tux> gfix -shut -tran 60 employee
 tux> gbak -replace overwrite -kill /backups/employee.fbk employee

 tux> # Check shadows again, use isql as gstat is broken.
 tux> isql employee

 Database: employee
 SQL> show database;
 Database: employee
         Owner: SYSDBA
 ...

 SQL> quit;

Note: I use isql in the above examples as gstat -h seems to get confused about how many shadows there are on a database. It reports zero when there are two, eventually it catches up and reports that there are two, then, if you kill a shadow, it reports that there are now three!

back to top of page

Remote backups & restores

Firebird's gbak utility can make backups of a remote database. To do this, you need to connect to the service manager running on the database, this is normally called service_mgr. The following example shows the Firebird employee database on server tuxrep being backed up from the server tux. The backup will be written to the remote server, in other words, the backup file will be created on the tuxrep server and not on the tux one. The network protocol in use is TCP.

 tux> # Backup the reporting database on remote server tuxrep.
 tux> gbak -backup -service tuxrep:service_mgr employee /backups/remote_backup.fbk

The backup file will have the same owner and group as the Firebird database server - on Unix systems at least.

It is also possible to restore a remote database in this manner as well, and gbak allows this.

 tux> # Restore the read-only reporting database on remote server tuxrep.
 tux> gbak -replace -mode read_only -service tuxrep:service_mgr              /backups/remote_backup.fbk employee

Note: The above example uses the handy Unix ability to split a long line over many shorter ones using a back slash as the final character on the line.

As ever, you are advised to beware of replacing a database in case there are problems during the restore. The example above recreates the existing database in read-only mode but this need not always be the case.

A remote backup can also be run on the database server itself! On Windows, this makes no difference, but on Unix systems, this local-remote method of backups and restores reduces network traffic. The 'remote' server, in this case, is not actually remote it is just the method of running the backup - connecting to the service manager - that implies remoteness.

 tux> # Backup the employee database on this server, but pseudo-remotely!
 tux> gbak -backup -service tux:service_mgr employee /backups/remote_backup.fbk

And corresponding restores can also be run 'remotely':

 tux> # Restore the employee database on this server, but pseudo-remotely!
 tux> gbak -replace -service tux:service_mgr /backups/remote_backup.fbk employee

The format of the parameter used for the -service switch is different according to the nature of the network protocol in use:

• TCP
  

• Named pipes
  

back to top of page

Remote backups and restores using SSH

As shown above, you can use the special file names 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 dump file.
 tux> gbak -backup employee stdout |  ssh firebird@tuxrep "gbak {...} -replace 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 dump file.
 tux> gbak -backup employee stdout |  ssh firebird@tuxrep "gbak {...} -replace 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

back to top of page

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 script writer 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 script writers, 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

back to top of page
<< Security of backups | Firebird Backup & Restore Utility | gbak caveats >>