Replicating In-Memory SQLite Database (astdb.sqlite3) in Asterisk/FreePBX Cluster

Understanding the Behavior of astdb.sqlite3 in an Asterisk/FreePBX Environment

Issue Overview
The core challenge revolves around replicating the astdb.sqlite3 database across two nodes (node1 and node2) in an Asterisk/FreePBX 15 cluster. This database is critical for storing real-time SIP extension statuses, Follow Me configurations, and other transient states. Unlike typical SQLite databases that reside on disk, astdb.sqlite3 is configured to operate in-memory (RAM), which introduces unique replication constraints.

Asterisk uses the astdb (Asterisk Database) to manage volatile data that must persist across reloads but does not require permanent storage. By default, FreePBX configures this database as an in-memory SQLite instance, meaning changes are not automatically flushed to disk or synchronized across nodes. Replication tools that rely on file synchronization (e.g., rsync, lsyncd, or cluster filesystems like GlusterFS) will fail to capture real-time updates because the database exists primarily in RAM. Even if the file is periodically written to disk, traditional replication methods risk copying corrupted or incomplete data due to:

1. Lack of write atomicity: In-memory databases do not guarantee that the on-disk snapshot reflects a consistent state unless explicitly flushed.
2. Concurrency conflicts: SIP status changes and Follow Me updates occur at millisecond intervals, making it difficult to synchronize without a transaction-aware mechanism.
3. File locking behavior: SQLite uses file-level locks to manage concurrency. If node1 is writing to astdb.sqlite3 while node2 attempts to read it, replication may fail or produce inconsistent results.

The absence of real-time replication for astdb.sqlite3 leads to discrepancies between nodes, such as stale extension statuses or mismatched Follow Me rules. This undermines the cluster’s reliability, as failover events may route calls based on outdated information.

Root Causes of Replication Failure for In-Memory SQLite Databases

Possible Causes

1. In-Memory Storage Configuration:
   If astdb.sqlite3 is configured as a pure in-memory database (using the :memory: URI), it never persists to disk, rendering file-based replication impossible. However, Asterisk/FreePBX often uses a hybrid approach: the database is stored in a temporary filesystem (e.g., /tmp) backed by RAM (tmpfs). While this allows the file to exist on disk, it remains volatile and subject to frequent overwrites.

2. Uncoordinated Flush Intervals:
   Asterisk may flush the in-memory database to disk at irregular intervals or only during graceful shutdowns. If replication tools poll for changes infrequently, they will miss interim updates. For example, a cron job running rsync every 5 minutes cannot replicate sub-second SIP status changes.

3. Transaction Isolation Mismatches:
   SQLite’s default journaling mode (DELETE, WAL) affects how transactions are committed. If node1 writes to astdb.sqlite3 using Write-Ahead Logging (WAL) and node2 attempts to read the database without the -wal and -shm files, replication will fail. This is exacerbated when nodes use different SQLite journaling configurations.

4. Cluster Filesystem Limitations:
   Deploying a shared filesystem (e.g., NFS, CephFS) for astdb.sqlite3 might seem viable, but SQLite is not designed for concurrent multi-writer access. Simultaneous writes from node1 and node2 will corrupt the database.

5. Missing Application-Level Replication Hooks:
   FreePBX does not natively support clustering for astdb.sqlite3. While third-party modules like “Asterisk Realtime Architecture” (ARA) exist, they require explicit configuration to propagate changes across nodes.

Strategies for Real-Time Replication of astdb.sqlite3

Troubleshooting Steps, Solutions & Fixes

1. Forced Disk Persistence with Synchronized Flushing

Modify Asterisk’s configuration to force astdb.sqlite3 to flush changes to disk immediately. This requires editing the astdb.conf or modules.conf file to specify a disk-backed database with synchronous writes:

; In /etc/asterisk/astdb.conf
[settings]
engine = sqlite3
synchronous = FULL
database = /var/lib/asterisk/astdb.sqlite3

Set synchronous = FULL to ensure SQLite waits for writes to complete before acknowledging transactions. Combine this with a replication tool like lsyncd or inotifywait to trigger synchronization whenever the file changes:

# Monitor for changes and trigger rsync
inotifywait -m -e close_write /var/lib/asterisk/astdb.sqlite3 |
while read; do
    rsync -az /var/lib/asterisk/astdb.sqlite3 node2:/var/lib/asterisk/
done

Tradeoffs: Increased I/O load due to frequent disk writes, potential latency from inotify event processing.

2. Database Mirroring via Custom Triggers

Use SQLite triggers to replicate changes at the transaction level. Create an AFTER INSERT/UPDATE/DELETE trigger that logs changes to a staging table. A secondary script on node1 can then apply these changes to node2’s database:

-- On node1's astdb.sqlite3
CREATE TABLE replication_log (
    id INTEGER PRIMARY KEY,
    query TEXT,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER replicate_astdb AFTER INSERT ON astdb
BEGIN
    INSERT INTO replication_log (query)
    VALUES ('INSERT INTO astdb VALUES (' || NEW.key || ', ' || NEW.value || ')');
END;

A cron job or daemon on node1 can periodically execute the queries in replication_log against node2’s database.

Tradeoffs: Requires application-level changes, introduces eventual consistency.

3. Distributed SQLite with LiteFS or rqlite

Replace the default SQLite engine with a distributed SQLite solution:

• LiteFS: A FUSE-based filesystem that replicates SQLite databases across nodes. Mount /var/lib/asterisk via LiteFS to enable real-time synchronization.
• rqlite: A lightweight wrapper that adds RAFT consensus to SQLite. Modify FreePBX to write to an rqlite endpoint instead of a local file.

Implementation:

# LiteFS configuration (node1 and node2)
fuse:
  dir: /var/lib/asterisk

Tradeoffs: Adds complexity, may conflict with Asterisk’s internal database locking.

4. Switch to a Cluster-Aware Database Backend

Replace SQLite with a database that natively supports clustering, such as MySQL or PostgreSQL. FreePBX supports external databases via ODBC:

; In /etc/asterisk/astdb.conf
[settings]
engine = odbc
dsn = AsteriskDatabase

Configure AsteriskDatabase in /etc/odbc.ini to point to a MySQL cluster.

Tradeoffs: Requires significant infrastructure changes, introduces external dependencies.

5. Leverage Asterisk’s Realtime API

Use Asterisk’s Realtime Architecture (ARA) to store astdb data in a shared database. This bypasses file-based replication entirely:

; In /etc/asterisk/extconfig.conf
[settings]
sippeers => odbc,asterisk,sippeers

Tradeoffs: Limited to specific data types (e.g., SIP peers), requires schema migration.

6. Hybrid Approach: tmpfs + DRBD

Store astdb.sqlite3 on a tmpfs partition synchronized via Distributed Replicated Block Device (DRBD). This mirrors RAM contents across nodes at the block level:

# Create a DRBD resource for /dev/drbd0
resource astdb {
    protocol C;
    on node1 {
        device /dev/drbd0;
        address 192.168.1.10:7788;
        meta-disk internal;
    }
    on node2 {
        device /dev/drbd0;
        address 192.168.1.20:7788;
        meta-disk internal;
    }
}

Mount the DRBD device as tmpfs on both nodes.

Tradeoffs: High network bandwidth usage, risk of split-brain scenarios.

Final Recommendation:
For minimal latency and maximal consistency, implement Strategy 1 (Forced Disk Persistence) with lsyncd for real-time file synchronization. If infrastructure allows, Strategy 3 (LiteFS) offers a robust long-term solution. Avoid shared filesystems (NFS) and multi-writer configurations, as they risk database corruption.