Database replication

Abstract

A method performed by a replication manager in a system is described. The replication manager first modifies the schema definition of a subscriber database to remove index and constraint objects and c

Description

BACKGROUND

[0001] Database replication is the process of migrating data from a primary database to one or more target databases to produce backup databases. Backup databases are required in the event of a database failure or major database update.

[0002] Databases are often used to store transactions relating to a particular service. For example, for services where users have an account, transactions are logged whenever the user updates their account. In another example, transactions are logged whenever two separate accounts interact either by sending data or sending objects to one another. Services that support a high number of users must therefore have the ability to store a large number of transactions. It is commonplace today for organisations that need to store large amounts of transactions to outsource their database storage to third parties. SUMMARY

[0003] This Summary is provided to introduce a selection of concepts in a simplified form that are further described below in the Detailed Description. This Summary is not intended to identify key features or essential features of the claimed subject matter, nor is it intended to be used to limit the scope of the claimed subject matter.

[0004] A method performed by a replication manager in a system is described. The replication manager first modifies the schema definition of a subscriber database to remove index and constraint objects and creates a DDL-to-DML object in a publisher database. A plurality of rebuild commands is then generated which, when executed, recreate the removed index and constraint objects and replica identity indexes. A rebuild graph is generated which determines the order in which the plurality of rebuild commands are executed at the subscriber node. After the schema definition of the subscriber is modified and the DDL-to-DML object is created in the publisher database, the publisher node is instructed to generate a database snapshot of the publisher database. The subscriber is instructed to export the database snapshot and execute a sequence of selected rebuild commands.

[0005] A first aspect provides a method performed in a system comprising: a replication manager; a publisher node, comprising a publisher database, a primary filesystem associated with the publisher database including a plurality of tables, and a write ahead log, WAL, including WAL files produced by Data Modification Language, DML, commands committed to the publisher database; and a subscriber node, comprising a subscriber database, wherein the replication manager controls the publisher node and the subscriber node; the method comprising: modifying, by the replication manager, a schema definition for the subscriber database to remove index and constraint objects; creating, by the replication manager, a Data Definition Language-to-Data Modification Language, DDL-to-DML, objects in the publisher database, wherein the DDL-to-DML object comprises: an event trigger which activates in response to the commitment of a DDL command to the primary filesystem, and a set of instructions that are executed in response to the activation of the event trigger, wherein the set of instructions cause the Structured Query Language, SQL, of the DDL command to be written into a bespoke table of the plurality of tables and recorded as a DML command that produces a WAL file; generating, by the replication manager, a plurality of rebuild commands, to be executed by the subscriber node and committed to the subscriber database, wherein the rebuild commands, when executed, recreate the index and constraint objects that were removed from the plurality of tables in the subscriber database and create a plurality of replica identity indexes for facilitating replay of WAL data on the subscriber node; generating, by the replication manager, a rebuild graph which determines the order in which the plurality of rebuild commands are executed by the subscriber node, wherein the rebuild graph comprises instructions to execute at least some of the commands for recreating the removed index and constraint objects, and commands for creating replica identity indexes in parallel; subsequent to modifying the schema definition of the subscriber node and creating the DDL-to-DML object in the publisher database, instructing, by the replication manager, the publisher node to generate a database snapshot of the publisher database; triggering, by the replication manager, the transfer of the database snapshot to the subscriber node using the modified schema definition to create an initial copy without indexes and constraint objects, the database snapshot comprising the plurality of tables; instructing, by the replication manager, the subscriber node to execute a plurality of the rebuild commands in an order determined using the rebuild graph to replicate the publisher database in the subscriber database; subsequent to the execution of at least a portion of the rebuild commands by the subscriber node, initiating the streaming of at least a portion of decoded WAL files from the publisher node to the subscriber node and the execution of the received decoded WAL files.

[0006] A second aspect provides a database replication system comprising a publisher node, a subscriber node and a replication manager. The publisher node comprising a publisher database, a primary filesystem associated with the publisher database including a plurality of tables, and a write ahead log, WAL, including WAL files produced by data modification language, DML, commands committed to the publisher database. The subscriber node comprising a subscriber database. The replication manager for controlling the publisher node and the subscriber node, the replication manager storing instructions either in the publisher node, the subscriber node or in a separate replication manager entity, to perform a method of facilitating logical replication as described above or herein.

[0007] A third aspect provides a computer program comprising instructions which, when the program is executed by a computer, cause the computing device to carry out a method of logical replication as described above or herein.

[0008] A fourth aspect provides a computer readable medium having stored thereon a computer program comprising instructions which cause a computing device to carry out a method of logical replication as described above or herein.

[0009] The above features may be combined as appropriate, as would be apparent to a skilled person, and may be combined with any of the aspects of the examples described herein. BRIEF DESCRIPTION OF THE DRAWINGS

[0010] Examples will now be described in detail with reference to the accompanying drawings in which:

[0011] FIG. lisa system diagram of the database replication architecture;

[0012] FIG. 2 is a diagram of a rebuild graph for determining the order of rebuild command execution by a subscriber node; |'0013| FIG. 3 is a time flow of communications between, and actions taken at: a replication manager, a publisher node and a subscriber node; and

[0014] FIG. 4 is a schematic diagram of a general computing device suitable to perform the methods described herein. |0015| The accompanying drawings illustrate various examples. The skilled person will appreciate that the boundaries of the different entities that are shown (e.g. by way of boxes or other shapes) in the drawings represent one example of the boundaries. It may be that in some examples, entities may be divided into two or more smaller entities and / or entities shown may be combined. Common reference numerals are used throughout the figures, where appropriate, to indicate similar features. DETAILED DESCRIPTION

[0016] The following description is presented by way of example to enable a person skilled in the art to make and use the invention. The present invention is not limited to the embodiments described herein and various modifications to the disclosed embodiments will be apparent to those skilled in the art.

[0017] Embodiments will now be described by way of example only.

[0018] A database typically includes a storage medium that stores physical data on a corresponding filesystem. The data (e.g., tables of data) is represented by files in the filesystem. Without such a filesystem, the data placed within the storage medium would be one large body of data without any indication of what data corresponds to what data objects.

[0019] Databases also include a database management system (DBMS) which is system software that is responsible for creating, managing and maintaining databases. When a transaction is committed to a database (i.e., an update to a record in the database), it is the responsibility of the DBMS to guarantee the “durability” of the committed data. That is, after the commit is completed, if the DBMS process crashes, the operating system crashes or power is lost in the system, the data that was committed to the database will be retrievable once the DBMS is running again. Therefore, the DBMS needs to guarantee that the affected data is stored in such a way that it is written into the filesystem and disk before allowing processes to occur beyond the database commit.

[0020] Using an example of a large table of 500GB, multiple files in the system would be used to represent said table. Each of the files being 1GB in size for example. When a transaction takes place, the data stored in the table changes instantly, however the DBMS does not write the data change into the corresponding filesystem immediately. The reason being is that every time a commit was processed, 1GB of data (corresponding to the file associated with the changed data object in the table) would need to be flushed (i.e., synchronising the temporary state of data with a permanent state of data stored in the database). Performing this flushing for every single commit is impractical for production purposes.

[0021] To avoid this issue, when a commit is made the information corresponding to the change in the database is written into a file called a write-ahead-log (WAL). The WAL is a strictly sequential log of changes that need to be applied to the files corresponding to the table to accurately reflect the state of the database. The log of changes contained within the WAL correspond to Data Modification Language (DML) statements to be executed on a filesystem. Examples of such DML statements include INSERT, UPDATE and DELETE commands. In contrast to the typically 1GB size of files in the filesystem, each entry in the WAL may only be a few kilobytes. As a result, writing into the WAL at the point of commit is far quicker than modifying the database and flushing 1GB to the filesystem. Thus, in the event of a crash as discussed previously, the DBMS can guarantee data changes are written into the log with the use of a WAL. The reconciliation (i.e., changing the file to reflect the WAL) of the table data files takes place in the background whilst commits are continually written into the WAL. Once entries into the WAL have been processed (i.e., the recorded change has been made in the filesystem), they are discarded. 10022] An additional benefit of employing a WAL is that if one wishes to maintain an up-to-date replica (backup) of a database, it is sufficient to copy the table data files from the filesystem and then apply the changes as recorded in the WAL. One process that employs such a method is physical replication. Firstly, the DBMS within the original database (i.e., the database to be backed up) “reserves” or “retains” WAL such that it is not immediately discarded after the corresponding database commit is written into the filesystem. Whilst the WAL is reserved, either a direct copy of the data files or a snapshot of the filesystem to be restored subsequently is copied into a standby instance (i.e., location of the backup). After the copy or the snapshot of the filesystem is completed, the WAL files, that were generated whilst the copying was being performed, are transferred to the newly created standby to be implemented on the copy of the files in the filesystem.

[0023] WAL files are therefore constantly streamed from the primary database to the standby database such that the replica can constantly receive the “instructions” to keep the replica up-to-date. Physical replication is a byte-by-byte process that directly transfers the WAL log from the primary database to the standby database. Physical replication is effective for maintaining high-availability standby replicas of databases such that the standby database can be promoted to primary database with near zero downtime in the event the original primary database is no longer operational.

[0024] Whilst providing these benefits, physical replication also has numerous disadvantages. Firstly, the primary instance and the standby instance must be running the exact same version of software. This is due to the byte-by-byte nature of physical replication and newer versions of the same software are likely to have different byte layouts. Secondly, the primary database and the backup database must be running on the exact same computer architecture and operating system family. The reason for this is because the byte arrangement is heavily dependent on both the underlying computer architecture and the underlying operating system. Lastly, the filesystem of the primary instance is implicitly made available to the backup instance. This is of particular concern when the primary and backup instances are provided for and maintained by separate providers (e.g., cloud computing providers). Service providers will not allow their respective filesystems to be copied to other providers due to the risks of exposing their intellectual property and the risk of providing an avenue for a cyberattack at the primary instance. Put differently, allowing physical replication to 3rd parties outside of a single provider poses serious security risks that are outside of the provider’s control.

[0025] An alternative to physical replication is logical replication which overcomes some of the aforementioned issues. Logical replication shares similarities with physical replication in that it requires an initial copy and a subsequent stream of changes to the initial copy to be applied to the data. However, logical replication sends the WAL data in a higher level format compared to physical replication. The format is independent of any DBMS and the endianness of the underlying computer architecture (i.e., the data can be consumed by software that is completely unrelated to the DBMS). The terms “publisher node” and “subscriber node” (which may be abbreviated to “publisher” and “subscriber”) are terms in the art that are used in the context of logical replication to describe the primary instance and the standby / backup instance.

[0026] One difference provided for by logical replication is associated with the initial copy. Physical replication duplicates table data files from the filesystem, whereas logical replication exports a database snapshot that is accessible from within the DBMS. In the process of performing database replication, visibility of the exported snapshot of the publisher database is requested such that it can be extracted. This effectively reserves a static snapshot of the data inside the database, such that clients can subsequently connect to the database, and request visibility of that static snapshot. The clients can then export the data in a textual format which is consumable by the subscriber database.

[0027] Another difference, as briefly discussed previously, is that a higher level format is used to represent the WAL data. The higher level messages represent the exact data that was inserted, updated, or deleted, as a consequence of a DML statement having been executed at the publisher. The higher level messages are generated by decoding the WAL data that were produced from the DML statements having been committed to the primary database and reconstructing the consequences of the commands. In the discussion below regarding the logical replication, it is described that the subscriber consumes the WAL stream. It should be appreciated that in the context of logical replication, the consumption of the WAL stream means the consumption of decoded WAL files in the WAL stream.

[0028] Logical replication also comes with numerous drawbacks. Firstly, the initial copy process can be very slow. The reason for this is that although the command for extracting the snapshot of the database at the publisher is optimised for bulk operations, the subscriber schema comprises many constraints and indexes which slows down bulk operations (i.e., can cause blocking to occur during replication, can cause additional input / output (IO) for writing into index structures and reading data for constraint checking, etc.). In some examples, the index maintenance employs randomly generated universally unique identifiers (UUID). This means that the data held within the index is unsorted when it arrives over a COPY stream (i.e., from the publisher to the subscriber). For instance, if the UUIDs were not generated at random, there would be a broad correlation between the time of arrival and the UUID value. Whilst the use of randomly generated UUIDs provides numerous benefits such as being suitable for distributed databases that do not encounter collisions and enhanced anonymity, it does mean that the copying of data from such indexes is slower than if the values stored in the index were monotonic. The reason for this reduction in copying efficiency is because the data is not stored in the order it is written into the index. There is no correlation between the order in which data is written into the index and how it is stored (e.g., a row arriving over the COPY stream could be placed anywhere into an index and not just into the “right most” page, which is the case for monotonically-generated identifiers).

[0029] Another problem associated with logical replication is that schema is not replicated. This is due to the fact that there is no support for Data Definition Language (DDL) handling in logical replication. As the schema at the subscriber is not updated to match any schema changes in the publisher, data representing the WAL stored in the publisher that arrives at the subscriber relating to an updated schema at the publisher will not fit into the table schema at the subscriber. This causes the replication to error until the schema is updated. Conventionally, human intervention is required to ensure this does not occur by manually synchronizing the schema of the subscriber in response to a DDL statement being executed on the publisher. The requirement for manual or human intervention at the subscriber results in data replication being paused which may also cause service downtime.

[0030] Described herein are methods and systems that are designed to improve on logical replication to minimize or overcome the issues identified above.

[0031] Figure 1 is a schematic diagram of the architecture 100 for database replication according to the methods described herein. A primary database, referred to herein as the publisher database 102, is stored in a publisher node 104 (referred to as the “publisher” herewith) that is a database used to record transactions associated with a service. In some examples, the transactions may reflect changes to a user’s account setting or recording transfers of objects between different accounts. Transactions or updates are communicated to the publisher database in the publisher either directly from client devices 122 requesting changes or indirectly via a relay (e.g., a server). A backup database, referred to as the ‘subscriber database’ 110 is stored within a subscriber node 108 (referred to as the “subscriber” herewith) which communicates with the publisher 104. Both the publisher 104 and the subscriber 108 communicate with a replication manager 120. The nature of the communications with the replication manager 120 is discussed below. In some examples, the publisher 104 and the subscriber 108 are stored in servers maintained by different service providers. The subscriber may store an additional local backup 124 of the subscriber database 110 within the same server environment. Also included into Figure lisa schematic representation 116 of the replication manager 120 monitoring the replication lag between the publisher and the subscriber which will be discussed further herein.

[0032] The location of the replication manager 120 is arbitrary as it can be stored in the same server environment as the publisher 104 or the subscriber 108. From a high-level, the replication manager is software or logic that receives information from the publisher and the subscriber to provide informed instructions. Additionally, the replication manager utilises code / instructions stored within the subscriber and publisher that trigger certain processes to occur on said nodes which will be discussed below. Although Figure 1 shows the replication manager being a separate entity compared to the publisher and subscriber, this is only for illustrative purposes as it is intended to symbolise instructions distributed throughout the architecture 100 which cause the methods described herein. For example, the replication manager could be distributed across the publisher and subscriber or in a totally separate server environment. The replication manager 120 logic may alternatively be within the DBMS of the publisher and / or subscriber or the replication manager 120 logic may be exclusively located in the publisher and / or subscriber

[0033] The publisher database 102 includes an associated primary filesystem which includes a plurality of tables. The publisher node stores a WAL log which includes a plurality of WAL files that are produced by DML commands that have been committed to the publisher database (i.e., the record of changes committed to the publisher database that need to be applied to the tables). The WAL log is written to disk (i.e. stored in non-volatile memory) so that it can be recovered in the event that the DBMS crashes. |0034| The process of replicating the publisher database 102 into the subscriber database 110 begins with the replication manager 120 modifying the schema for the subscriber database to remove index and constraint objects. The consequence of this action is that the subscriber schema is no longer a direct facsimile of the publisher schema. As an example, consider the following exemplary schema definition: CREATE TABLE tl (n BIGINT, a TEXT, b TEXT, CONSTRAINT pk_tl PRIMARY KEY (n), CONSTRAINT ck_tl CHECK (LENGTH (b) <LENGTH (a))); CREATE INDEX idxl ON tl (a); CREATE TABLE t2 (n BIGINT, m BIGINT, c TEXT, CONSTRAINT pk_t2 PRIMARY KEY (n), CONSTRAINT uk_t2 UNIQUE (m), CONSTRAINT fk_t2 FOREIGN KEY (n) REFERENCES tl (n)); ALTER TABLE t2 REPLICA IDENTITY USING INDEX uk_t2; [0035| The replication manager 120 generates a set of statements to modify the schema, dropping all constraints and indexes, thus yielding the following modified schema: CREATE TABLE tl (n BIGINT, a TEXT, b TEXT); CREATE TABLE t2 (n BIGINT, m BIGINT, c TEXT); [0036| The subscriber schema does not contain index and constraint objects that would otherwise exist, and this has the effect of reducing the time taken for the initial load of the tables at the subscriber 108. The initial load of tables which employ randomly generated UUIDs in the indexes has been shown to be too slow for practical implementation without this modification of the schema. Of course, the commands corresponding to the removed index and constraint objects will have to be implemented at a later stage to ensure full replication of the database snapshot of the publisher database. However, because of their removal, the index and constraint objects (and the DDL commands associated with said objects) can be dealt with in a significantly more efficient manner (which will be discussed later) compared to using the original schema of the subscriber (e.g., by using a “COPY FROM STDIN” command). (0037] The replication manager 120 also performs a modification at the publisher node 102 by creating a set of schema objects in the publisher database which are referred to herein as DDL-to-DML (Data Definition Language-to-Data Modification Language) objects. The purpose of the inclusion of a DDL-to-DML object in the publisher database is to overcome the issue where DDL commands are not replicated during logical replication. (0038] In short, the DDL-to-DML obj ects effectively turn the DDL commands into data. A DDL-to-DML object comprises an event trigger which activates in response to the commitment of a DDL command to the primary filesystem. The event trigger then captures the DDL Structured Query Language (SQL) string that caused the activation of the event trigger, along with other relevant variables from the execution environment, such as the role that executed the DDL command. This information is written into a table which is included within the plurality of tables in the primary filesystem. The table is a bespoke table for SQL strings associated with DDL commands. As an example, the data objects introduced into the publisher database may take the form: 1. Schema called replication 2. Table called ddl_transaction 3. Table called ddl_statement 4. Event trigger called capture_ddl

[0039] As a result of the modification performed by the replication manager, any DDL command which is committed to the publisher database during runtime results in a data modification occurring within the bespoke table (i.e., it effectively issues an INSERT into the bespoke table). This means that the transaction modifying the data dictionary via DDL is also inserted into the ddlstatement table. This change to the bespoke ddlstatement table is then reflected in the WAL with the creation of a WAL file for the DDL command. When the WAL is streamed to the subscriber node, it includes the WAL file for the DDL command that can be executed by the subscriber node. The execution of the DDL command originating from the DDL-to-DML object results in schema changes at the publisher being replicated in the subscriber. This does not require any human intervention. (0040] Clearly the publisher and the subscriber share the same table structure, however instead of an event trigger (which activates in response to the commitment of a DDL command to the primary filesystem), the tables at the subscriber includes a regular trigger. The regular trigger, which will also be referred to as a ‘replay trigger’ herein, may be defined as: CREATE FUNCTION replication.on_insert_ddl_statement () RETURNS TRIGGER LANGUAGE PLPGSQL AS $$ BEGIN EXECUTE FORMAT('SET LOCAL ROLE %i', NEW.role); EXECUTE FORMAT('SET LOCAL SEARCH_PATH TO %S', NEW.SEARCH_PATH); EXECUTE FORMAT('%S', NEW.statement); RETURN NULL; END; CREATE TRIGGER on_insert_ddl_statement AFTER INSERT ON replication.ddl_statement FOR EACH ROW EXECUTE FUNCTION replication.on_insert_ddl_statement(); [00411 From a high level, the replay trigger causes logical replication workers on the subscriber, when it inserts into the ddl.statement table, to also execute the statement found in the inserted row. For example, if the logical replication workers in the subscriber executed the following commands: INSERT INTO replication.ddl_statement (txid, executed_at, role, search_path, statement) VALUES (12345, '2024-01-01 12:34:56', 'dbowner', 'public', 'CREATE INDEX idx ON t(n)'); Then it would also execute the following commands immediately after having made the insert, thus enabling replication of the schema: SET LOCAL ROLE dbowner; SET LOCAL SEARCH_PATH TO public; CREATE INDEX idx ON t(n);

[0042] WAL logs are strictly sequential in nature meaning the order of the WAL files is always in the order in which the associated commits are made to the database. The WAL file associated with the DDL command is therefore also included in a sequential manner into the WAL log. This guarantees that the DDL command is executed by the subscriber at the correct time relative to the consumption of subsequent WAL files in the log. In other words, the schema change at the subscriber resulting from the consumption of the WAL file associated with the DDL command will take place after database changes that are compatible only with the previous schema and before database changes that are compatible only with the new schema. This provides a robust mechanism of ensuring replication errors do not occur at the subscriber due to schema changes at the publisher. Conventionally, this process would have to be manual to make such a guarantee. This is especially beneficial in large databases that require numerous and frequent updates and avoids the risk of service downtime awaiting manual intervention.

[0043] After the replication manager implements the modifications described above to the subscriber and publisher, the logical replication process between the publisher database 102 and the subscriber database 110 can commence. The replication process starts where a database snapshot of the publisher database is extracted from the publisher 104. This can be achieved using commonplace client tools within the DBMS of the publisher database. Logical replication workers at the publisher request visibility of the exported database snapshot and then extract the data visible in the snapshot using a command that converts the visible data into a textual format (e.g., using a COPY to STDOUT command) that can be transmitted.

[0044] In addition, once the replication manager 120 has performed the modifications to the subscriber schema and publisher described above, it generates a plurality of rebuild commands 112. The rebuild commands 112 are the commands that are required to be executed by the subscriber so that the initial schema of the publisher database is completely restored in the subscriber database. Replay of WAL on the subscriber is dependent upon the restoration of the initial schema and consequently, these rebuild commands facilitate the replay of WAL data on the subscriber.

[0045] The rebuild commands are derived from the publisher schema object definitions, and need to be executed on the subscriber, to restore the parity of schema definition between the two databases. These commands include creating the index and constraint objects which were removed from the subscriber, after the database snapshot was copied from the publisher. For example, restoring an index or a constraint object in the subscriber may require the execution of multiple commands for such objects to be created. The rebuild commands are not limited to just these commands. Logical replication further requires that each replicated table has a specially-nominated index associated with it, so that changes being processed from the replication stream can be applied efficiently. Without such an index, the application of, for example, even a single row update would require the entire table be scanned which would be very inefficient. This index is commonly known as a replica identity index. The replica identity index is not required during the loading of the initial copy of the database (i.e., extraction of database from database snapshot) but it is required to implement subsequent changes to the database (i.e., during consumption of the WAL stream).

[0046] After the replication manager 120 has generated the rebuild commands, the replication manager then determines the order in which the rebuild commands should be executed. The aim of the order is to maximise efficiency and to reduce the amount of time it takes to rebuild the publisher database snapshot. To achieve this, the replication manager 120 generates a rebuild graph 114 from the rebuild commands.

[0047] As described previously, the rebuild commands 112 rebuild the index and constraint objects that were removed from the plurality of tables of the publisher database and one or more commands for creating a plurality of replica identity indexes for populating the plurality of tables in the subscriber. The rebuild graph 114 arranges these commands in a defined order to be executed by the subscriber.

[0048] The replication manager 120 firstly generates the rebuild graph 114 such that at least some of the indexes that were removed from the plurality of tables are rebuilt in parallel. For example, create index commands are executed in parallel. Executing them in parallel provides an improvement in the speed at which tables in the subscriber database are populated.

[0049] The method for generating the rebuild graph may further comprise splitting out the creation of key constraints from their respective indexes. At least some of the indexes are then created in parallel (executing create index commands in parallel) and the key constraints, that have been split, are implemented after the creation of the indexes. For reference, key constraints are made up of primary key constraints, unique key constraints, and foreign key constraints. A primary or unique key is a column or a combination of columns in a table that uniquely identifies each row. Whereas a foreign key establishes a link between data in two tables, controlling what can be stored in the foreign key table. (0050] In one example, the rebuild of constraints is achieved by first creating the constraint as being invalid and, later on, validating the constraint once its associated index has been created. This approach means that heavier weight locks, due to the execution of constraint commands, are held for a smaller time. For reference, the heavier locks result in a greater proportion of a table being uneditable whilst a command is being executed on the table. The heaviest lock would be a complete lock of a table for instance. The approach adopted by the replication manager in generating the rebuild graph also means that multiple constraints against a table can effectively be created in parallel, as the underlying index creation commands do not conflict with each other. (0051] Since the creation of the replica identity indexes at the subscriber is required for the consumption of the WAL stream 118, commands associated with the creation of the replica identity indexes may be prioritised over other commands in the generated rebuild graph. A benefit of including this priority in the generation of the rebuild graph is that it reduces the time it takes for individual tables at the subscriber to be ready to consume the WAL stream 118. As a result, the subscriber may be capable of consuming WAL stream 118 whilst other tables in the subscriber database are still being populated. In some examples, consuming WAL whilst tables are being repopulated is facilitated using multiple replication slots within the DBMS. During the initial population of the subscriber database, multiple replication slots are used to facilitate different tables being copied at different times. This mechanism allows different tables to copy and finish at different rates or times. This provides a significant efficiency improvement as the WAL being retained by the publisher can be streamed to the subscriber earlier and thus reduce the likelihood of the publisher running out of memory space. (0052] Figure 2 illustrates an example of such a rebuild graph. The exemplary rebuild graph is for two tables (tl and tl) each comprising a primary key constraint that are linked by a foreign key constraint. To explain the format of the graph: the commands are organised into columns where commands in a common column are able to be executed in parallel. To emphasise this, commands performed in parallel are linked by dotted lines. The order in which the commands are performed is organised from left to right, where columns of commands on the left are performed first. The arrows originating from a command signify that command is dependent on another command where the arrow terminates. For example, command 212 requires the completion of execution of command 204. |0053| To explain the meaning of each command, 202 is the command for creating an invalid constraint ck on table tl in the first set of commands to be executed in parallel. This constraint is validated in command 224 in the final set of commands to be executed. The first set of commands 204-210 executed in parallel are create index commands associated with tables tl and t2. The second set of commands 212-216 are commands which add constraints to the indexes created in the first set of parallel commands. Again, the second set of commands can be executed in parallel. The commands which, when executed, add primary key constraints can be executed in parallel as described previously having been split from their underlying indexes. The third set of commands 218-222 include commands to create the replica identity index for tables tl 222 and t2 218. The create replica identity index commands are executed in parallel along with a command that adds a foreign constraint between the primary constraints of tables tl and t2 in the example illustrated in Figure 2. In the final set of commands 224 and 226, the constraints are validated in parallel once the index and their respective constraints have been successfully created.

[0054] In the example illustrated by Figure 2, because the replication manager generated all of the required rebuild commands to fully rebuild all objects that were dropped, it can identify commands that can be performed in parallel, and thus generate a rebuild graph to optimise the recreation of the objects.

[0055] In some examples, the tables stored at the publisher may be partitioned. This means that the data residing in a table is partitioned into multiple separate tables which can make it easier to manage and query the data. As is known, a partitioned table (a root node) comprises leaf nodes (which are regular tables originating from the root node) and may comprise internal nodes (which are regular tables originating from the root node that have been partitioned again). An aspect of the partition hierarchy is that leaf nodes have the same constraints declared against them as the root node. Partitioned tables can thus also impact the replication process.

[0056] As discussed previously, the replication manager modifies the schema definition of the subscriber to remove (or drop) index and constraint objects. However, doing this on a leaf node would have the result that the definition of that partition would no longer match that of the root node. Therefore, in examples that employ table partitioning, the replication manager employs specific handling. During the drop / removal of the constraints and indexes at the subscriber, the leaf node tables of a partition hierarchy are first detached. As an example, a table ‘r’ has been partitioned using the time recorded for each transaction into: r_2024, r_2023, r_2022_hl, r_2022_h2. Note r_2022 is an internal node, and r_2024, r_2023, r_2022_hl and r_2022_h2 are leaf nodes. To perform the detaching of the leaf nodes, using the example above, the following commands are executed: ALTER TABLE r DETACH PARTITION r_2024; ALTER TABLE r DETACH PARTITION r_2023; ALTER TABLE r_2022 DETACH PARTITION r_2022_hl; ALTER TABLE r_2022 DETACH PARTITION r_2022_h2;

[0057] Once detached, the leaf nodes become regular tables in terms of being able to drop constraints and indexes which is performed in accordance with the modified schema definition of the subscriber as discussed previously. This allows the COPY process (from the COPY FROM STDIN command) to populate the tables in the subscriber followed by the rebuilding of the indexes and constraints in the manner discussed previously. Once the rebuild of a leaf partition is complete, it is reattached to the root node. The commands associated with the handling of table partitions are simply another type of rebuild command along with those for creating indexes and constraints. Thus, they can also be included into the generated rebuild graph. For example, if a separate table ‘s’ is declared to be a subset of the table ‘r’ by a referential constraint, the command validating the constraint should not be executed until all of the partition leaf nodes of table ‘r’ have been reattached. This can be expressed in the rebuild graph as an edge from the validate constraint command to all commands that are attach partition for table ‘r’.

[0058] Once the replication manager has generated the rebuild commands 112 and the rebuild graph 114 and the database snapshot 106 of the publisher database has been extracted, the logical replication of the present replication system can commence.

[0059] The database snapshot 106 is sent from the publisher to the subscriber where the snapshot is extracted. Due to the modified schema of the subscriber, the extracted tables from the database snapshot are devoid of indexes and constraints. The replication manager then instructs the subscriber 108 to issue the rebuild commands 112 in a sequence defined using the rebuild graph 114. The subscriber then executes the rebuild commands (as instructed by the replication manager) to begin the recreation of indexes and constraints along with the replica identity indexes in an optimised manner. Thus, reducing the required time to rebuild the initial copy of the publisher database in the subscriber database.

[0060] To continue the logical replication process, the publisher is instructed by the replication manager to start decoding the WAL and begin streaming the decoded WAL 118 to the subscriber. The subscriber then consumes the decoded WAL to reduce the replication lag built up at the publisher. Whilst the subscriber consumes the decoded WAL, it also consumes the DDL commands written into the WAL thus changing the schema at the subscriber in accordance with the publisher. All of the steps described above are performed in an entirely automated manner thus reducing the requirement for manual interference and reducing replication downtime.

[0061] To provide further efficiency gains, the replication manager may dictate certain criteria for the eligibility of when a rebuild command, from the generated rebuild commands 112, may be executed. To determine a next rebuild command to execute, all rebuild commands that have not yet been executed may be assessed by the replication manager to identify any rebuild commands that are eligible for execution (e.g. because any dependencies within the rebuild graph have been satisfied) and then one of the identified, eligible, rebuild commands is selected for execution and the subscriber 108 is instructed to issue the selected rebuild command. This selection may be random and / or may prioritise certain types of rebuild commands, such as commands for creating replica identity indexes. In the event that no rebuild commands are found to be eligible, the process waits for a short period of time (e.g. one or two seconds) and then repeats the assessment to identify any rebuild commands that are eligible for execution.

[0062] The replication manager may exclude commands from being executed (by classifying them as ineligible), which are dependent on at least one other command, where the other command(s) are yet to be committed to the database. Additionally or alternatively, commands associated with tables that are currently locked (due to the execution of other commands) are considered ineligible for execution. Both of these criteria reduce the likelihood of delays occurring during the execution of the rebuild commands since they remove many possible causes of a command having to wait during execution on the subscriber. This in turn reduces the build-up of pending WAL data at the publisher.

[0063] In another example, the replication manager 120 may classify a rebuild command in the rebuild graph as ineligible if it is associated with a table that is yet to reach a certain replication state.

[0064] The information regarding the replication state of the tables within the subscriber is collected by the replication manager by scraping information from the subscriber. The scraping may be performed at regular intervals (or substantially continuously) to maintain up to date information regarding the state of the subscriber. The information scraped from the subscriber can be used to inform the replication manager as to whether a table is ready to have commands executed on it. (0065] A table’s replication state may be classed into discrete states and the following set of states are an example of the states that may be used. A table may be in an “initial state” where the table has yet to be populated with any data.

[0066] A table may be in a “data copying state” where the table is in the process of being populated through the export of the initial snapshot from the publisher. Upon detecting the table is in the “data copying state”, the replication manager performs additional scraping to determine the progress of the initial data copy. 100671 A table may be in a “data finished copying state” where a table in the subscriber has successfully been populated with the data from the initial snapshot. Upon detecting the table is in the “data finished copying state”, the replication manager may determine that certain rebuild commands are now eligible for execution. In particular, replication manager will favour executing the command that creates the special replica identity index, as this index is required for the logical replication workers to be able to apply messages found in the WAL stream. (0068] A table may be in a “synchronised state” where the table in the subscriber is in the process of consuming WAL files from the WAL stream. |0069( A table may be in a “ready state”, where all of the WAL, received from the publisher in the WAL stream, associated with the table has been consumed. Put differently, the table in the subscriber database is an exact replica of the table in the publisher database. The replication manager may now determine that there are more rebuild commands to execute such as recreating referential integrity constraints. This is possible now, rather than earlier, as the involved tables are fully populated and therefore the referential constraint(s) should hold true.

[0070] To summarise, the replication manager may apply the above criteria when considering the eligibility of rebuild command execution where the criteria may be determined as a result of the scraping performed by the replication manager.

[0071] The replication manager may also consider yet more factors when choosing which rebuild commands should be executed (i.e., when classifying commands as eligible / ineligible for execution). For example, the scraping performed by the replication manager can also collect information about the CPU capacity of the subscriber. The replication manager can infer from the CPU capacity, how much CPU is being used by the rebuild commands currently being executed. Based on the CPU capacity, the replication manager may pause the execution of any further rebuild commands (e.g. by classifying all rebuild commands as ineligible for a period of time) to let the subscriber process its current workload and therefore does not overwhelm the subscriber. To prevent this, the replication manager may also define a CPU threshold of the subscriber that serves as a trigger to prevent rebuild commands from being executed. For example, the CPU threshold may be 70%, 75%, 80%, 85%, 90%, or 95% of the CPU capacity at the subscriber.

[0072] The replication manager can also perform scraping at the publisher that can provide information to be used in the rebuild command execution decision process (i.e., the process of classifying a rebuild command in the rebuild graph as eligible or ineligible). Whilst the replication of the publisher database to the subscriber database is taking place, the publisher is still receiving and committing transactions to the publisher database. During this process, the publisher is also adding files to the WAL. The publisher is also having to retain WAL longer whilst the subscriber both populates tables and executes the rebuild commands, such that it can be streamed to the subscriber once the subscriber is ready. This can take up memory in the publisher and may even result in the publisher running out of memory space. A simple solution to this issue is to increase the memory capacity at the publisher such that larger amounts of WAL can be retained. However, if the replication lag is allowed to accrue to large amounts, there is a risk that the subscriber will never be able to “catch up” with the publisher such that it is a true replica and so simply increasing the memory capacity at the publisher is not a full solution. The reason for this potential inability for the subscriber to catch up with the publisher is because it takes longer to consume WAL at the subscriber than to create WAL files at the publisher. The consumption of the WAL is limited by various factors such as the single thread processing of the WAL stream, networking constraints and bottlenecks, implementation of a VPN and IO limitations at the subscriber, etc.

[0073] The solution provided by the replication manager of monitoring the publisher and reacting to this, as described below, also ensures that the replication process can automatically respond to the unpredictable and unavoidable fluctuations in the volume of transactions recorded in the publisher causing variations in the creation rate of new WAL files.

[0074] The amount of WAL having to be retained by the publisher can be expressed as a “replication lag” since it reflects the discrepancy between the publisher database and the subscriber database. This is because the publisher usually discards WAL once it has been committed to the publisher database filesystem, and so the amount of retained WAL in the publisher can be used as a measure of the replication lag. Referring back to Figure 1, the replication manager 120 is shown to be constantly monitoring replication lag in 116. (0075] The replication manager may then use the replication lag as an input into the rebuild command execution decision making. For example, it may pause execution of the rebuild commands that are not already being executed when a replication lag threshold is met (e.g. by classifying those commands as ineligible). This means that rebuild commands being executed by the subscriber are allowed to run to completion, eventually reaching a point where there are no commands being run. In one example, this would result in all rebuild commands which are not currently running being classified as ineligible whilst the threshold lag is above the threshold. In other examples, most types of rebuild commands that are not currently running are classified as ineligible but one or more types of rebuild commands (e.g. commands associated with the creation of the replica identity indexes) may be excluded and may still be classified as eligible if other constraints (such table state) are satisfied. Figure 1 shows this where a threshold Zis included on the replication lag monitor 116. When certain types of rebuild commands are being executed, the subscriber may be blocked from consuming the WAL stream for the affected table(s), and therefore WAL accrues at a greater rate on the publisher than it would otherwise do. For example, when creating an index on a table, other processes that are wanting to write data into that table will be blocked.

[0076] The replication lag threshold may be defined in numerous ways. It may be defined as the amount of memory taken by retained WAL (e.g., 4GB). Alternatively, it may be defined by the remaining disk space of the publisher from the disk space that is taken up by the database itself. The threshold may be defined as a percentage of the remaining disk space (or disk space that is not taken up by the database) where the percentage may be 5%.

[0077] In other examples, the time it will take for commands to complete is estimated and from the estimated running time, an estimation of the amount of WAL that will accrue during the running time of such commands is made. The accrued WAL estimation can therefore be used to prevent new commands from being run where the threshold will only be exceeded once a command currently being run is complete or at least partially complete. Put differently, the replication lag threshold may be considered exceeded if it is predicted that it will be exceeded upon completion of a currently running command, even if at the particular time point the replication lag is below the threshold.

[0078] To illustrate this aspect, consider a first and a second create index command that can be run in parallel. At a first time point, the first command is being run at the subscriber resulting in a lock being placed on a table such that it cannot consume WAL. When the first command was executed (i.e., classified as being eligible for execution) the retained WAL is 2GB below the threshold. An estimation is made that predicts the first command will take three hours to complete and in that three hours, 3GB of retained WAL is estimated to accrue at the publisher meaning the threshold will be exceeded at completion. The second command, which is scheduled to be classified as eligible for execution during the runtime of the first command, is instead classified as ineligible since the first command will result in the threshold being exceeded after its completion. If this predictive method were not in place, the second command will be executed meaning the lock on the table will be prolonged resulting in further accrual of WAL at the publisher.

[0079] Whilst the execution of new rebuild commands is paused, some of the WAL files from the publisher are cleared by streaming WAL files which are associated with tables that in one of the data finished copying state, a synchronised state or a ready state. The publisher and subscriber communicate such that once a WAL file has been successfully consumed at the subscriber, the publisher can discard that particular WAL file. The replication manager will subsequently resume the commencement of rebuild commands execution once the lag drops below the lag threshold. The replication manager thus reduces the risk of memory at the publisher being taken up by retained WAL files.

[0080] To provide a chronological illustration of the events that occur during the replication of the publisher database, Figure 3 depicts a timeline of the replication events. The order of the events illustrated is not intended to be limiting but is instead intended to provide a clear example of how an example set of the events occur due to the actions implemented by the replication manager 120.

[0081] The replication manager 120 first modifies the schema definition at the subscriber 108 (in the subscriber database 110) to remove index and constraint objects 302. As a result, the subscriber is now ready to receive extracted tables from a database snapshot where the tables are devoid of indexes and constraints. The replication manager then creates the DDL-to-DML objects at the publisher (in the publisher database 102). As a result, if the publisher receives a DDL commands (i.e., to modify the schema), it is configured to automatically convert the DDL command into, for example, an ALTER TABLE command in the replication table created as part of the DDL-to-DML objects.

[0082] The replication manager 120 then generates the set of rebuild commands 306 that are required to restore the subscriber schema to the same state as that of the publisher. As discussed previously, the replication manager implements specific criteria when generating the rebuild commands.

[0083] The replication manager 120 then generates a rebuild graph 308 which is to be used by the replication manager 120 to determine the order in which the rebuild commands should be executed. As discussed previously, the replication manager applies specific rules when constructing the rebuild graph. As a result, the replication manager can automatically select the rebuild commands for execution by the subscriber in an optimised manner.

[0084] The replication manager 120 is now ready to instigate the load of the initial copy of the publisher database (publisher 104) in the subscriber database (subscriber 108). The replication instructs the publisher to create and make available a database snapshot to the subscriber 310. In response, the publisher accesses the database snapshot from the DBMS 312. The database snapshot is then transmitted 314 to the subscriber 108 where it is extracted 316. Once the database snapshot has been transmitted to the subscriber, the replication manager selects rebuild commands for execution 317 and instructs the subscriber to execute the selected rebuild commands 318. As a result, the subscriber now has all the components to start rebuilding the initial copy of the publisher database by executing the rebuild commands 320 according to the instructions from the replication manager. This means that the rebuild commands are executed by the subscriber in an order dictated by the rebuild graph. 100851 Whilst the previous actions are occurring, the publisher database is still operating in its normal manner by receiving database transactions and committing said transactions to the publisher database. At the publisher, WAL files are generated in the WAL 301 whilst transactions are committed to the publisher database. The publisher is also, during the rebuild, retaining WAL (which has already been committed to the primary filesystem) to be streamed to the subscriber once the rebuilt tables at the subscriber are ready to consume the WAL. (0086] Whilst the rebuild commands are being executed 320 by the subscriber 108 and the publisher is generating WAL files 301, the replication manager 120 performs scraping of the publisher 324 and the subscriber 322. This provides information to the replication manager 120 as to the status of the population of the tables in the subscriber, the CPU capacity at the subscriber and / or the amount of replication lag built up at the publisher. This information is collected in an automatic manner and can be used, as discussed previously, in the decision making process when considering whether a rebuild command should be executed (i.e. in the process of selecting a rebuild command for execution). Although Figure 3 shows the scraping taking place at specific times, during operation the scraping is continuous such that as soon as a table reaches a required replication state, the replication manager logic automatically classifies the relevant commands in the rebuild graph as eligible. The replication process in Figure 3 shows the execution of new rebuild commands being paused 328 in response to the scraping information indicating the replication lag at the publisher exceeds the threshold 326. Whilst new command execution is paused, rebuild commands that were already being run at the time of the pause continue to run until completion but all other rebuild commands may be classified as ineligible (as described above). To reduce the amount of WAL being retained at the publisher, the decoded WAL log on the publisher is streamed 330 to the subscriber (during the period in which command execution is paused) where it is consumed 332. This streaming is initiated by the replication manager (e.g. by asking the publisher and subscriber to begin the replication). The consumption of the WAL at the subscriber is possible due to the portion of the rebuild commands which have previously been executed 320 (e.g. following the rebuild of the replica identity indexes).

[0087] The continual scraping at the publisher 336 (and subscriber 334) informs the replication manager when the replication lag has dropped below the threshold. In response to the detection of the replication lag dropping below the threshold the replication manager resumes selection of new rebuild commands 340 and instructs the subscriber to execute the selected rebuild commands 341. The subscriber then resumes executing the rebuild commands 342.

[0088] As mentioned previously, at the publisher, WAL files are still being generated 301. This also includes the generation of DDL command WAL files 336 originating from the execution of DDL commands committed to the publisher database. The DDL-to-DML object (previously created at 304) activates in response to the detection of the DDL command causing the DDL command WAL file to be generated.

[0089] The publisher streams decoded WAL 346 to the subscriber where the decoded WAL includes the DDL command WAL file created from the activation of the DDL-to-DML object. As a result of the consumption of the WAL file associated with the DDL command, the schema of the concerned table in the subscriber is automatically modified 348 in the manner described previously.

[0090] The replication process 300 includes numerous actions. All of the actions occur automatically after the implementation of the replication manager 120 thus requiring no manual input. This provides significant improvements to the efficiency of the database replication. [00911 Figure 4 illustrates various components of an example computing device 400 suitable to perform any of the methods performed by the replication manager 120, the publisher node 104 or the subscriber node 108. The computing device 400 comprises one or more processor(s) 402 which are microprocessors, controllers or any other suitable type of processors for processing computer executable instructions to control the operation of the device in order to perform the actions shown in Figure 3. In some examples, for example where a system on a chip architecture is used, the processors 402 include one or more fixed function blocks (also referred to as accelerators) which implement a part of the actions in Figure 3 in hardware (rather than software or firmware). That is, the methods described herein are implemented in any one or more of software, firmware, or hardware. The computing device comprises a memory 404 for storing instructions that are executed by the processor(s) 402 to cause the computing device to implement the methods described herein. It shall be appreciated that references to the memory of the computing device refer to either or both volatile and non-volatile memory. Platform software comprising an operating system 406 or any other suitable platform software is provided at the computing-based device to enable application software 408 to be executed on the device. The application software 408 may comprise computer executable instructions that, when executed by the processor(s), cause the computing device 400 to function as one of a replication manager, publisher or subscriber as described above. Although the computer storage media (memory 404) is shown within the computing-based device 400 it will be appreciated that the storage is, in some examples, distributed or located remotely and accessed via a network or other communication link (e.g., using communication interface 410). The communication interface 410 enables the computing device 400 to communicate with other computing devices. For example, where the computing device is operating as a replication manager, the communication interface 410 enables the replication manager to receive information from and send commands to a publisher and a subscriber according to the methods described herein.

Claims

1. A method performed in a system comprising:a replication manager;a publisher node, comprising a publisher database, a primary filesystem associated with the publisher database including a plurality of tables, and a write ahead log, WAL, including WAL files produced by Data Modification Language, DML, commands committed to the publisher database; anda subscriber node, comprising a subscriber database, wherein the replication manager controls the publisher node and the subscriber node;the method comprising:modifying, by the replication manager, a schema definition for the subscriber database to remove index and constraint objects (302);creating, by the replication manager, a Data Definition Language-to-Data Modification Language, DDL-to-DML, objects in the publisher database (304), wherein the DDL-to-DML object comprises: an event trigger which activates in response to the commitment of a DDL command to the primary filesystem, and a set of instructions that are executed in response to the activation of the event trigger, wherein the set of instructions cause the Structured Query Language, SQL, of the DDL command to be written into a bespoke table of the plurality of tables and recorded as a DML command that produces a WAL file;generating, by the replication manager, a plurality of rebuild commands, to be executed by the subscriber node and committed to the subscriber database (306), wherein the rebuild commands, when executed, recreate the index and constraint objects that were removed from the plurality of tables in the subscriber database and create a plurality of replica identity indexes for facilitating replay of WAL data on the subscriber node;generating, by the replication manager, a rebuild graph which determines the order in which the plurality of rebuild commands are executed by the subscriber node (308), wherein the rebuild graph comprises instructions to execute at least some of the commands for recreating the removed index and constraint objects, and commands for creating replica identity indexes in parallel;subsequent to modifying the schema definition of the subscriber node and creating the DDL-to-DML object in the publisher database, instructing, by the replication manager, the publisher node to generate a database snapshot of thepublisher database (310);triggering, by the replication manager, the transfer of the database snapshot to the subscriber node using the modified schema definition to create an initial copy without indexes and constraint objects (314), the database snapshot comprising the plurality of tables;instructing, by the replication manager, the subscriber node to execute a plurality of the rebuild commands in an order determined using the rebuild graph to replicate the publisher database in the subscriber database (318);subsequent to the execution of at least a portion of the rebuild commands by the subscriber node, initiating the streaming of at least a portion of decoded WAL files from the publisher node to the subscriber node and the execution of the received decoded WAL files.

2. The method according to claim 1, further comprising: in response to the execution of one of the decoded WAL files comprising the DDL command written into the bespoke table, modifying the schema of the subscriber node using the DDL command.

3. The method according to claim 2, further comprising:creating a replay trigger in the subscriber node which activates in response to inserting the SQL of the DDL command into the replicated bespoke table in the plurality of tables at the subscriber node;wherein in response to the activation of the replay trigger, the replay trigger causes the execution of the inserted DDL command; andwherein modifying the schema of the subscriber node is caused by the execution of the inserted DDL command.

4. The method according to any preceding claim, wherein the WAL is sequential in the order in which commands are committed to the publisher database, and the WAL file resulting from the commit of the DDL command to the primary filesystem is stored sequentially.

5. The method according to any preceding claim, further comprising:performing, by the replication manager, scraping of the subscriber to determine a replication state of the tables and a current CPU capacity of the subscriber node, whereinthe replication state is a measure of the extent to which the plurality of tables in the subscriber node have been populated; andperforming, by the replication manager, one or both of:scraping of the subscriber to determine a current CPU capacity of the subscriber node; andscraping of the publisher to determine a replication lag between the publisher database and the subscriber database, wherein the replication lag is the amount of data discrepancy there is between the publisher database and the subscriber database.

6. The method according to claim 5, wherein the replication state of each of the plurality of tables in the subscriber node is one of:an initial state, where a table in the plurality of tables is yet to be populated with any data;a data copying state, where the table in the subscriber node is in the process of being populated with the data from the exported database snapshot;a data finished copying state, where the table in the subscriber node has successfully been populated with the data from the exported database snapshot;a synchronised state, where the table in the subscriber node is in the process of executing decoded WAL files; anda ready state, where the table in the subscriber node has executed all WAL files associated with the table that have been streamed from the publisher node.

7. The method according to claims 5 or 6, wherein the plurality of rebuild commands is a plurality of commands eligible for execution.

8. The method of according to claim 7, further comprising prioritising commands for creating replica identity indexes from the plurality of commands eligible for execution.

9. The method according to claim 8 further comprising:classifying at least one of the rebuild commands as ineligible for executionaccording to the following criteria:commands which are dependent upon at least one other command, where at least one other command had not been committed to the subscriber database,commands associated with tables that are currently locked by another command, orcommands associated with tables yet to reach a pre-determined replication state.

10. The method according to any of claims 8 or 9, wherein the execution of rebuild commands is paused by classifying all of the rebuild commands awaiting execution as ineligible, in response to the following criteria being met:the CPU capacity of the subscriber node is exceeded or at least above a predetermined threshold of the CPU capacity, and / ora replication lag between the publisher database and the subscriber database is greater than a predefined lag threshold.

11. The method according to claim 10, wherein the predefined lag threshold is at least one of:a volume of data from retained WAL files at the publisher; ora percentage of the memory of the publisher which is not allocated to the publisher database.

12. The method according to claims 10 or 11, wherein the lag threshold is considered to have been exceeded if the accrued WAL at the publisher is estimated to be exceeded after completion of running commands.

13. The method of any preceding claim, wherein generating the rebuild commands further comprises splitting a plurality of key constraint commands into separate commands, wherein the plurality of key constraint commands is associated with at least one index; and generating the rebuild graph further comprises ordering a create index commandfor the associated at least one index before the constraint commands;wherein the plurality of key constraint commands and the associated create index command are from the commands for creating index and constraint objects.

14. The method of any preceding claim, wherein at least a portion of the plurality of tables in the publisher database are partitioned into a plurality of leaf nodes, the method further comprising:during removal of the index and constraint objects at the subscriber, detaching the plurality of leaf nodes from the partitioned tables;instructing the subscriber to execute the rebuild commands on the leaf nodes;wherein generating the rebuild graph further comprises including commands which, when executed, reattach each leaf node to its respective table of which it was a partition; andwherein commands for validating constraints on the partitioned tables is expressed as an edge to commands for reattaching each leaf node to the partitioned tables.

15. The method of any preceding claim, wherein the DML commands are database transactions.

16. The method of any preceding claim, wherein the publisher node and the subscriber node are hosted by different service providers.

17. The method of any preceding claim, wherein the indexes of the plurality of tables employ randomly generated universally unique identifiers, UUIDs.

18. A database replication system, comprising:a publisher node (104) comprising a publisher database (102), a primary filesystem associated with the publisher database including a plurality of tables, and a write ahead log, WAL, including WAL files produced by data modification language, DML, commands committed to the publisher database;a subscriber node (108) comprising a subscriber database (110); anda replication manager (120) for controlling the publisher node and the subscriber node, the replication manager storing instructions either in the publisher node, the subscriber node or in a separate replication manager entity, to perform the method according to claims 1 to 17.

19. A computer program comprising instructions which, when the program is executed by a computing device, cause the computing device to carry out the method of claims 1 to 17.

20. A computer readable medium having stored thereon the computer program of claim 19.

Images