Firebird Database Clustering Guide with isql and DbSchema

Firebird has no clustering. There is no statement that joins a server to a cluster and no cluster for it to join, and looking for one is how an afternoon disappears. What Firebird does document for keeping a second copy of the data current is replication, built into the engine since Firebird 4, and the shadow, a page-by-page copy of the database file. Both are set up in isql and in configuration files, and the version below is Firebird 5.0.

What Firebird has in place of a cluster

Firebird replication is uni-directional: one primary database, one or more replicas, and changes travel in that direction only. It is logical replication, meaning record-level rather than page-level, and it tracks inserted, updated and deleted records, sequence changes and DDL statements. Replication is transactional and commit order is preserved. Every table you replicate needs a primary key, or at least a unique key.

There are two modes, and the choice is about where you want to pay. In synchronous replication the primary stays connected to the replica and changes are replicated immediately, so the databases are in sync after every commit, at the cost of the extra network traffic and round trips that requires. In asynchronous replication changes are written to local journal files, transferred, and applied on the replica, which costs far less on the primary but leaves the replica behind by however long the transfer and the apply take. More than one synchronous replica can be configured.

The replica itself is either read-only, where only the replication process may modify it and everything else is limited to read-only transactions, or read-write, where any query runs and you resolve the conflicts that follow. The Firebird Replication chapter of the release notes describes both, and every replication error and warning, conflicts included, goes to replication.log in the Firebird log directory.

Turning replication on inside the database

Two lines of SHOW DATABASE in isql tell you where a database currently stands:

Replica mode: NONE
Publication: Disabled

Firebird 5.0 added SHOW PUBLICATIONS, which reports the same thing from the other side. The engine ships one system-defined publication and offers no way to create your own, so the output is the same on every database:

SQL> show publications;
There is no publications in this database

SQL> show system publications;
RDB$DEFAULT

SQL> show publication rdb$default;
RDB$DEFAULT: Disabled

Enabling the publication and choosing what goes into it is DDL on the primary database:

ALTER DATABASE ENABLE PUBLICATION;
ALTER DATABASE INCLUDE ALL TO PUBLICATION;

INCLUDE ALL covers tables created later as well. To pick tables instead, name them, and the matching EXCLUDE statements take them back out again:

ALTER DATABASE INCLUDE TABLE CUSTOMERS, ORDERS TO PUBLICATION;
ALTER DATABASE EXCLUDE TABLE ORDERS FROM PUBLICATION;

The replication.conf entries on each side

The database knows what to publish; replication.conf on each host decides how it travels. Settings apply to every database on the server unless you scope them to one, and a scoped block names the database by an absolute or relative path, since aliases and wildcards are not accepted there. This is the minimal primary side for asynchronous replication, where naming journal_directory is what switches it on:

database = /data/mydb.fdb
{
    journal_directory = /dblogs/mydb/
    journal_archive_directory = /shiplogs/mydb/
}

The Firebird server copies full journal segments from the first directory to the second. Delivering them to the replica host is yours to arrange: set journal_archive_command to any shell command, script or batch file, and Firebird expands $(pathname) and $(archivepathname) when it runs it. For synchronous replication instead, set sync_replica to a connection string for the replica, prefixed with a user name and password, once per replica.

The replica side reads the same file and needs the directory the segments arrive in, plus the source database it accepts them from:

database = /data/mydb.fdb
{
    journal_source_directory = /incominglogs/
    source_guid = "{6F9619FF-8B86-D011-B42D-00CF4FC964FF}"
}

The replica database starts as a physical copy of the primary, made in any of these ways:

• a file-level copy while the Firebird server is shut down
• ALTER DATABASE BEGIN BACKUP, a file-level copy, then ALTER DATABASE END BACKUP
• nbackup -l, a file-level copy, then nbackup -n
• nbackup -b 0, then nbackup -f -seq

Then mark the copy with gfix, which is what makes it a replica rather than another database:

gfix -replica read_only /data/mydb.fdb

Changed settings take effect at different moments on the two sides, and getting this wrong looks exactly like replication not working. On the primary, every user has to disconnect, or the database has to be shut down, before the new configuration is picked up. On the replica, the Firebird server must be restarted.

A shadow keeps a second copy of the database file

CREATE SHADOW 1 '/data/shop.shd';

A shadow is an exact, page-by-page copy of a database, and from the moment it is created every change to the database is reflected in it. If the primary database file becomes unavailable, the engine converts the shadow into a copy of the database and switches to it, after which the shadow is gone. No user can connect to a shadow directly, and its page size is fixed to the database page size.

What happens when a shadow becomes unavailable, whether through that switch or through someone deleting the file, depends on the mode. AUTO, the default, stops shadowing, deletes the references to it from the database header, and lets the database carry on. Add CONDITIONAL and the system tries to create a replacement shadow, which does not always succeed. MANUAL makes every connection and query fail until the shadow is available again or an administrator runs DROP SHADOW, which is the choice when continuous shadowing matters more than staying up. SHOW DATABASE in isql lists the names, sizes and locations of the shadow files, so it is also how you confirm the statement above did what you wanted. The SHADOW section of the Firebird 5.0 Language Reference has the full syntax, multi-file shadows included.

Comparing the replica with the primary in DbSchema

Firebird replicates DDL statements along with the rows, so the schemas on the two databases should match, and the moment to find out that they do not is before an application starts reading the replica. DbSchema answers that question by comparing them.

Connect DbSchema to the primary and it reverse-engineers the schema into a diagram; that diagram is the design model, saved as a .dbs file. Open a second connection to the replica, and on the Settings tab of the connection dialog tick Read Only Connection, which makes the database refuse every change sent through it. Then use Schema → Compare Model with Database against the replica: DbSchema lists every added, removed and modified table, column, index and foreign key between the model and that database, and for each difference you decide whether to update the model, push the change to the database, or skip it. Schema → Refresh Schema from Database goes the other way, pulling the current database state into the model. Both belong to schema synchronization, in the Pro edition.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Replication and shadows give you the two things people usually mean by clustering a Firebird database: a warm copy on another machine and a live copy of the file. Keeping them honest means watching the schema on both. Download DbSchema at https://dbschema.com/download.html, connect to the primary and to the replica, and compare the two with the Pro edition.