Self-Hosted Firebird

sudo apt update && sudo apt upgrade -y

sudo apt install firebird4.0-server firebird4.0-utils -y

sudo apt install firebird3.0-server firebird3.0-utils -y

sudo dpkg-reconfigure firebird4.0-server

# Check service status
sudo systemctl status firebird4.0

# Check Firebird version
isql-fb -z

sudo dnf install wget tar libicu libncurses libtommath -y

cd /tmp
wget https://github.com/FirebirdSQL/firebird/releases/download/v4.0.4/Firebird-4.0.4.3010-0.amd64.tar.gz
tar -xzf Firebird-4.0.4.3010-0.amd64.tar.gz
cd Firebird-4.0.4.3010-0.amd64

sudo ./install.sh

sudo systemctl enable firebird-superserver
sudo systemctl start firebird-superserver

# Check service status
sudo systemctl status firebird-superserver

# Check Firebird version
isql-fb -z

sudo nano /etc/firebird/4.0/firebird.conf

# Bind to specific IP (use 0.0.0.0 for all interfaces)
RemoteBindAddress = 127.0.0.1

# Default port (change if needed)
RemoteServicePort = 3050

# Enable wire compression for remote connections
WireCompression = true

# Database cache pages (increase for larger datasets)
DefaultDbCachePages = 2048

# Temporary file directory
TempDirectories = /tmp

# Sort memory limit
TempCacheLimit = 67108864

# Authentication method (Srp256 is most secure)
AuthServer = Srp256

# User manager plugin
UserManager = Srp

# Wire encryption (Required, Enabled, or Disabled)
WireCrypt = Required

sudo systemctl restart firebird4.0

sudo nano /etc/firebird/4.0/databases.conf

# Format: alias = /path/to/database.fdb
production = /var/lib/firebird/4.0/data/production.fdb
staging = /var/lib/firebird/4.0/data/staging.fdb

# With additional options
myapp {
  filename = /var/lib/firebird/4.0/data/myapp.fdb
  DefaultDbCachePages = 4096
}

# Allow from specific IP
sudo ufw allow from 10.0.0.100 to any port 3050

# Allow from subnet
sudo ufw allow from 10.0.0.0/24 to any port 3050

# Enable firewall
sudo ufw enable

# Create rich rule for specific IP
sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="10.0.0.100" port port="3050" protocol="tcp" accept'

# Reload firewall
sudo firewall-cmd --reload

# Connect to security database
isql-fb -user SYSDBA -password your_password

# Create new user
CREATE USER app_user PASSWORD 'strong_password';

# Grant permissions on specific database (connect to database first)
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE customers TO app_user;
GRANT EXECUTE ON PROCEDURE process_order TO app_user;

COMMIT;

isql-fb -user SYSDBA -password your_password

CREATE DATABASE '/var/lib/firebird/4.0/data/myapp.fdb'
PAGE_SIZE 16384
DEFAULT CHARACTER SET UTF8;

sudo chown firebird:firebird /var/lib/firebird/4.0/data/myapp.fdb
sudo chmod 660 /var/lib/firebird/4.0/data/myapp.fdb

# Using file path
isql-fb -user SYSDBA -password your_password /var/lib/firebird/4.0/data/myapp.fdb

# Using database alias
isql-fb -user SYSDBA -password your_password localhost:myapp

# Remote connection
isql-fb -user SYSDBA -password your_password server.example.com:myapp

-- Create a table
CREATE TABLE customers (
  id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create generator (sequence)
CREATE GENERATOR gen_customer_id;

-- Create trigger for auto-increment
SET TERM ^ ;
CREATE TRIGGER tr_customer_bi FOR customers
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.id IS NULL) THEN
    NEW.id = GEN_ID(gen_customer_id, 1);
END^
SET TERM ; ^

-- Insert data
INSERT INTO customers (name, email) VALUES ('John Doe', 'john@example.com');

-- Query data
SELECT * FROM customers;

COMMIT;

# Full backup
gbak -b -v -user SYSDBA -password your_password \
  /var/lib/firebird/4.0/data/myapp.fdb \
  /backup/myapp_$(date +%Y%m%d).fbk

# Backup with compression
gbak -b -v -user SYSDBA -password your_password \
  /var/lib/firebird/4.0/data/myapp.fdb stdout | \
  gzip > /backup/myapp_$(date +%Y%m%d).fbk.gz

# Restore to new database
gbak -c -v -user SYSDBA -password your_password \
  /backup/myapp_20240115.fbk \
  /var/lib/firebird/4.0/data/myapp_restored.fdb

# Restore with page size change
gbak -c -v -page_size 16384 -user SYSDBA -password your_password \
  /backup/myapp_20240115.fbk \
  /var/lib/firebird/4.0/data/myapp_restored.fdb

#!/bin/bash
# /usr/local/bin/firebird-backup.sh

BACKUP_DIR="/backup/firebird"
DB_PATH="/var/lib/firebird/4.0/data"
DATABASES=("production" "staging")
RETENTION_DAYS=30
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p $BACKUP_DIR

for DB in "${DATABASES[@]}"; do
  gbak -b -g -user SYSDBA -password "$FB_PASSWORD" \
    ${DB_PATH}/${DB}.fdb \
    ${BACKUP_DIR}/${DB}_${DATE}.fbk

  gzip ${BACKUP_DIR}/${DB}_${DATE}.fbk
done

# Remove old backups
find $BACKUP_DIR -name "*.fbk.gz" -mtime +$RETENTION_DAYS -delete

# Make executable
chmod +x /usr/local/bin/firebird-backup.sh

# Add to crontab (daily at 2 AM)
0 2 * * * FB_PASSWORD='your_password' /usr/local/bin/firebird-backup.sh

DefaultDbCachePages = 4096

production {
  filename = /var/lib/firebird/4.0/data/production.fdb
  DefaultDbCachePages = 8192
}

# Sweep database (remove old record versions)
gfix -sweep -user SYSDBA -password your_password database.fdb

# Check and repair database
gfix -validate -full -user SYSDBA -password your_password database.fdb

# Get database statistics
gstat -h database.fdb  # Header info
gstat -r database.fdb  # Record statistics

# Backup-restore cycle (most thorough maintenance)
gbak -b database.fdb backup.fbk
gbak -c backup.fbk database_new.fdb

-- Create index
CREATE INDEX idx_customer_email ON customers (email);

-- Analyze query plan
SET PLAN ON;
SELECT * FROM customers WHERE email = 'test@example.com';

-- Recompute selectivity statistics
SET STATISTICS INDEX idx_customer_email;

SELECT
  MON$ATTACHMENT_ID,
  MON$USER,
  MON$REMOTE_ADDRESS,
  MON$STATE,
  MON$TIMESTAMP
FROM MON$ATTACHMENTS
WHERE MON$ATTACHMENT_ID <> CURRENT_CONNECTION;

# Database header information
gstat -h /var/lib/firebird/4.0/data/myapp.fdb

# Full statistics report
gstat -a /var/lib/firebird/4.0/data/myapp.fdb > stats_report.txt