My task is to perform continous replication on a Firebird 3.0.5 read-only database. The data will go to Google Cloud Platform, either Cloud SQL or BigQuery.
Read-only means I cannot use CREATE TRIGGER inside the database. I also cannot use the Logical Replication feature since it’s Firebird 4.0 minimum.
The only solution I see would be to perform scheduled exports and imports of the whole database.
Hi gregoireborel,
Since you can’t use ‘CREATE TRIGGER’ or the built-in logical replication of Firebird 4.0, you may consider creating a scheduled script that periodically queries the database for new or updated records. This method, known as ‘polling,’ is a suitable approach for working with Firebird’s multi-generational architecture, which offers unique advantages but isn’t fully compatible with standard log-based Change Data Capture (CDC) tools. The script would efficiently track changes by comparing the last sync time with a timestamp column, such as LAST_MODIFIED_DATE, or by checking for new records based on an auto-incrementing ID.
Once the script captures the data changes, it should export them to a file in a common format like CSV or JSON, which is then uploaded to a Google Cloud Storage (GCS) bucket. From GCS, the path forward depends on your final goal. For a replica to support transactional workloads, a scheduled gcloud sql import job would load the data into a Cloud SQL instance. This is ideal for applications that need a relational database. Alternatively, if the goal is large-scale analytics and business intelligence, a BigQuery load job would be the better option. For this, you could use the BigQuery Data Transfer Service to automate the process of moving data from GCS into BigQuery tables, leveraging BigQuery’s power for complex queries and massive datasets.
Thank you for your input. Unfortunately, my client has almost no control and information on this database. I looked at the tables and most of them don’t have a timestamp column (unless you are talking about a pseudo-column?). Also, primary keys do not seem to exist, or at least they are not correctly implemented (according to my client).
On StackOverflow, someone has suggested to use the RDB$RECORD_VERSIONpseudo-field but I’m not sure to fully understand. It would also not handle the deleted records.
Note that this database has a lot of tables but does not seem heavy (probably a few GB). Honestly I feel like it would probably be simpler for everyone to simply perform a full export/import every X days.
My client needs a temporary solution (understand, for a couple of years) and just want to perform analysis in order to pinpoint the discrepancies in this database.
I’m currently testing different tools to do this: isql, fbexport, etc.