Multimaster replication for firebird in python
Once there was the task of synchronizing two databases running under Firebird DBMS. The situation in brief is as follows.
There is an accounting program that operates in two stores located a few kilometers away from each other. Internet connection - through different providers with the reliability and speed corresponding to our realities. In each of the cases, it is possible to change the provider only for a more expensive one with worse quality connection, so placing the base in only one of the stores and remote connection from the other does not work out under any sauce. In each of the stores, receipt and expense documents are entered, reference books are edited, and records of other economic activities are kept. Considering that the entered information has some, but nonetheless commercial value, the question of the security of the transmitted data also cannot be ignored. Having received something like this, I went to think. I submit the result of my thoughts to the community court.
')
To begin with - not all data will be replicated. There are service tables, there are log tables, there are registries and logs that are generated automatically from the primary documentation tables - they should be excluded. So first we decide that we will synchronize. For this, the RPL_TABLES table contains the names of the tables being synchronized and the service information - whether all fields need to be replicated and whether this table is the header of the document (this affects the logic of the database). Table RPL_FIELDS, respectively, stores the names of the fields of those tables that need to be synchronized not completely.
Next, three procedures were written that, based on this information, generate the necessary triggers for logging all changes in the specified tables. Two of them - RPL_ALLFIELDS and RPL_SOMEFIELDS - generate triggers, which in turn generate sql queries for other replication participants to execute. The most difficult thing when writing them was not to be mistaken in the number of quotes :) The third one - RPL_INSTALL - analyzes the above-mentioned settings tables and runs the necessary procedure for them. When writing these procedures, the execute statement / execute block was used , which, among other things, allows changing the base metadata from the inside of the procedure.
As a result, in the RPL_LOG table, all changes made by users of the program, regardless of where, when, or how they (users) made them (changes), are saved. Now go to the interaction between multiple databases.
Synchronization is carried out according to the “star” scheme: each database connects to the synchronization server, floods its changes with it since the last synchronization session, receives change sets from other database clients, applies them to itself and disconnects. The server works with itself in exactly the same way, being at the same time a client.
In detail, it happens like this. Each client database has a table RPL_SESSIONS. At the beginning of a replication session, it marks the beginning of a new session, the identifier of which is stored in the RPL_LOG table for each generated query. After that, all the accumulated requests from the previous session are selected from the RPL_LOG table, written to a file (for further analysis in case of problems, and just in case) and uploaded to the server in the RPL_BLOB table. Together with the blob, the database identifier and session number are written. Further, similar blobs from other databases are pumped out of the server and applied to their database. After that, the client’s RPL_DATABASES table records information on the number of the last successfully synchronized session for each of the neighbor bases and (again, in case of problem analysis), the base identifier, blob identifiers, sessions and synchronization time are written in the RPL_RECEIVED table.
Cross-database interaction is done using a simple python script. In fact, this is my first working python script, so please do not scold for the approach, syntax and curvature of the solutions. The script can be conventionally divided into two parts - a class to simplify working with the database and the consistent execution of the above queries.
This script is executed by the system scheduler every 15 minutes and, as practice has shown, it successfully handles the impossibility of connection and interruptions of communication during synchronization. In the first case, it just crashes on timeout, and in the second, when you try to insert already existing data, the database discards them by restricting the primary key.
To ensure security between servers running databases, a VPN is raised and all work is done only through it. In addition, a separate user has been created for replication in the database, which has read access to only the RPL_ * tables.
At the moment, replication does not correctly solve the situation when the same data changes simultaneously in different databases. Under the existing approach, the two databases simply “exchange information”: in the first database, after replication, the data from the second database will be saved, the second, respectively, those that were added to the first. Now such a restriction is not significant for us, since the same data in different databases is rarely edited and, as a rule, by one person. So while someone gets from the store to the store, most likely already will have completed the next replication session. But, nevertheless, there is a problem, and it will be solved.
A solution has been created that provides usable speed and reliability when it is necessary to synchronize data between remote servers connected by unreliable communication channels. At the same time, the solution is not tied to a specific database * and can be easily used for other databases with almost no changes. Moreover, the solution is self-sufficient in the sense that the synchronization server can be a completely separate database, in which there will only be RPL_ * tables. This can be useful if all the database servers that need to be synchronized are behind NATs and there is no way to bring them out (for example, mobile Internet in Ukraine). In addition, the solution does not require changes in the programs working with the database, and does not depend on the OS installed on the database server - if only firebird, python and (optionally) vpn can be started there.
* In fact, the only binding is the is_docheader option in the RPL_TABLES table, which implies the presence of the field in the corresponding table and the generation of the query when only this field is changed, ignoring any other changes in the table.
Those who wish to try the solution can download the full SQL script and the Python client with whole files. I would be grateful for the ideas, criticism and bugs found.
There is an accounting program that operates in two stores located a few kilometers away from each other. Internet connection - through different providers with the reliability and speed corresponding to our realities. In each of the cases, it is possible to change the provider only for a more expensive one with worse quality connection, so placing the base in only one of the stores and remote connection from the other does not work out under any sauce. In each of the stores, receipt and expense documents are entered, reference books are edited, and records of other economic activities are kept. Considering that the entered information has some, but nonetheless commercial value, the question of the security of the transmitted data also cannot be ignored. Having received something like this, I went to think. I submit the result of my thoughts to the community court.
')
Work from the inside
To begin with - not all data will be replicated. There are service tables, there are log tables, there are registries and logs that are generated automatically from the primary documentation tables - they should be excluded. So first we decide that we will synchronize. For this, the RPL_TABLES table contains the names of the tables being synchronized and the service information - whether all fields need to be replicated and whether this table is the header of the document (this affects the logic of the database). Table RPL_FIELDS, respectively, stores the names of the fields of those tables that need to be synchronized not completely.
Next, three procedures were written that, based on this information, generate the necessary triggers for logging all changes in the specified tables. Two of them - RPL_ALLFIELDS and RPL_SOMEFIELDS - generate triggers, which in turn generate sql queries for other replication participants to execute. The most difficult thing when writing them was not to be mistaken in the number of quotes :) The third one - RPL_INSTALL - analyzes the above-mentioned settings tables and runs the necessary procedure for them. When writing these procedures, the execute statement / execute block was used , which, among other things, allows changing the base metadata from the inside of the procedure.
As a result, in the RPL_LOG table, all changes made by users of the program, regardless of where, when, or how they (users) made them (changes), are saved. Now go to the interaction between multiple databases.
Synchronization is carried out according to the “star” scheme: each database connects to the synchronization server, floods its changes with it since the last synchronization session, receives change sets from other database clients, applies them to itself and disconnects. The server works with itself in exactly the same way, being at the same time a client.
In detail, it happens like this. Each client database has a table RPL_SESSIONS. At the beginning of a replication session, it marks the beginning of a new session, the identifier of which is stored in the RPL_LOG table for each generated query. After that, all the accumulated requests from the previous session are selected from the RPL_LOG table, written to a file (for further analysis in case of problems, and just in case) and uploaded to the server in the RPL_BLOB table. Together with the blob, the database identifier and session number are written. Further, similar blobs from other databases are pumped out of the server and applied to their database. After that, the client’s RPL_DATABASES table records information on the number of the last successfully synchronized session for each of the neighbor bases and (again, in case of problem analysis), the base identifier, blob identifiers, sessions and synchronization time are written in the RPL_RECEIVED table.
Work outside
Cross-database interaction is done using a simple python script. In fact, this is my first working python script, so please do not scold for the approach, syntax and curvature of the solutions. The script can be conventionally divided into two parts - a class to simplify working with the database and the consistent execution of the above queries.
This script is executed by the system scheduler every 15 minutes and, as practice has shown, it successfully handles the impossibility of connection and interruptions of communication during synchronization. In the first case, it just crashes on timeout, and in the second, when you try to insert already existing data, the database discards them by restricting the primary key.
To ensure security between servers running databases, a VPN is raised and all work is done only through it. In addition, a separate user has been created for replication in the database, which has read access to only the RPL_ * tables.
Perfection
At the moment, replication does not correctly solve the situation when the same data changes simultaneously in different databases. Under the existing approach, the two databases simply “exchange information”: in the first database, after replication, the data from the second database will be saved, the second, respectively, those that were added to the first. Now such a restriction is not significant for us, since the same data in different databases is rarely edited and, as a rule, by one person. So while someone gets from the store to the store, most likely already will have completed the next replication session. But, nevertheless, there is a problem, and it will be solved.
Results
A solution has been created that provides usable speed and reliability when it is necessary to synchronize data between remote servers connected by unreliable communication channels. At the same time, the solution is not tied to a specific database * and can be easily used for other databases with almost no changes. Moreover, the solution is self-sufficient in the sense that the synchronization server can be a completely separate database, in which there will only be RPL_ * tables. This can be useful if all the database servers that need to be synchronized are behind NATs and there is no way to bring them out (for example, mobile Internet in Ukraine). In addition, the solution does not require changes in the programs working with the database, and does not depend on the OS installed on the database server - if only firebird, python and (optionally) vpn can be started there.
* In fact, the only binding is the is_docheader option in the RPL_TABLES table, which implies the presence of the field in the corresponding table and the generation of the query when only this field is changed, ignoring any other changes in the table.
Those who wish to try the solution can download the full SQL script and the Python client with whole files. I would be grateful for the ideas, criticism and bugs found.
Source: https://habr.com/ru/post/117917/
All Articles