Method For Copying Spanner Databases From Production To Test Environments

Abstract

A method of copying a production database to a test environment using one or more processors. The production database includes a plurality of tables and one or more constraints indicating interleaving of the plurality of tables. The method involves constructing a directed acyclic graph (DAG) of the plurality of tables, whereby each table is represented by a respective node of the DAG and interleaving of the tables is represented by edges of the DAG, topologically ordering the tables according to the DAG, and copying data from the production database to the test environment according to the topological ordering of the tables.

1 . A method of copying a production database to a test environment, wherein the production database comprises a plurality of tables and one or more constraints indicating interleaving of the plurality of tables, the method comprising:
 constructing, by one or more processors, a directed acyclic graph (DAG), wherein each of the plurality of tables is represented by a respective node of the DAG and interleaving of the plurality of tables is represented by edges of the DAG;   topologically ordering, by the one or more processors, the plurality of tables of the production database according to the DAG; and   copying, by the one or more processors, data from the production database to the test environment according to the topological ordering of the plurality of tables.   
     
     
         2 . The method of  claim 1 , wherein copying data from the production database to the test environment according to the topological ordering of the plurality of tables comprises copying data from a root table of the plurality of tables before copying data from leaf tables of the plurality of tables. 
     
     
         3 . The method of  claim 1 , further comprising:
 isolating, by the one or more processors, foreign key enforcement data from the one or more constraints of the production database; and   reinserting, by the one or more processors, the foreign key enforcement data after copying the data from the production database to the test environment.   
     
     
         4 . The method of  claim 1 , further comprising:
 assigning, by the one or more processors, a plurality of worker nodes to copy respective portions of the data from the production database to the test environment in parallel; and   assigning, by the one or more processors, a respective key range to each of the plurality of workers, wherein each worker node is responsible for copying data from the plurality of tables of the production database within its respective key range.   
     
     
         5 . The method of  claim 4 , further comprising:
 estimating a respective number of bytes-per-row for each of the plurality of tables of the production database; and   determining a balanced distribution of key ranges among the plurality of worker nodes based on the estimated number of bytes-per-row for each of the plurality of tables.   
     
     
         6 . The method of  claim 5 , wherein the balanced distribution of key ranges is based further on a number of available worker nodes, wherein the balanced distribution of key ranges splits the data of the production database into a number of portions greater than or equal to the number of available worker nodes. 
     
     
         7 . The method of  claim 5 , wherein the balanced distribution of key ranges is based further on a preset value, wherein the preset value indicates a desired key range between split points. 
     
     
         8 . The method of  claim 4 , further comprising:
 generating, by the one or more processors, a progress table;   monitoring, by the one or more processors, a progress of copying the production database to the test environment for each cell of the progress table; and   in response to the monitored progress, rebalancing, by the one or more processors, at least one of the balanced distribution of key ranges or the assignment of the plurality of worker nodes.   
     
     
         9 . The method of  claim 1 , further comprising:
 for a given table of the test environment, writing data from the production database to a mutation pool associated with the given table of the test environment; and   in response to a size of a transaction of the mutation pool's contents to the given table of the test environment meeting or exceeding a threshold transaction value, flushing the mutation pool.   
     
     
         10 . The method of  claim 1 , wherein the production database is a Spanner database. 
     
     
         11 . A system for copying a production database to a test environment, wherein the production database comprises a plurality of tables and one or more constraints indicating interleaving of the plurality of tables, the system comprising:
 memory storing a representation of a directed acyclic graph (DAG), wherein each of the plurality of tables is represented by a respective node of the DAG and interleaving of the plurality of tables is represented by edges of the DAG; and   one or more processors configured to:   topologically order contents of the plurality of tables of the production database according to the DAG; and   instruct a plurality of worker nodes to copy data from the production database to the test environment according to the topological ordering of the plurality of tables.   
     
     
         12 . The system of  claim 11 , wherein the one or more processors configured to instruct each of the plurality of worker nodes to copy data from a root table of the plurality of tables before data from leaf tables of the plurality of tables. 
     
     
         13 . The system of  claim 11 , wherein the one or more processors are configured to:
 isolate foreign key enforcement data from the one or more constraints of the production database; and   reinsert the foreign key enforcement data after the plurality of workers copy the data from the production database to the test environment.   
     
     
         14 . The system of  claim 11 , wherein the one or more processors are configured to:
 assign respective portions of the data from the production database to the plurality of worker nodes, wherein each respective portion of the data corresponds to an independent key range, and wherein the independent key ranges do not overlap with one another.   
     
     
         15 . The system of  claim 14 , wherein the one or more processors are configured to:
 estimate a respective number of bytes-per-row for each of the plurality of tables of the production database; and   determine a balanced distribution of key ranges among the plurality of worker nodes based on the estimated number of bytes-per-row for each of the plurality of tables.   
     
     
         16 . The system of  claim 15 , wherein the one or more processors are configured to determine the balanced distribution of key ranges based further on a number of available worker nodes, wherein the respective portions of the data is greater than or equal to the number of available worker nodes. 
     
     
         17 . The system of  claim 15 , wherein the one or more processors are configured to determine the balanced distribution of key ranges based further on a preset value, wherein the preset value indicates a desired key range between split points. 
     
     
         18 . The system of  claim 14 , wherein the one or more processors are configured to:
 generate a progress table indicating a progress of each of the plurality of worker nodes copying the plurality of tables, wherein each cell of the progress table corresponds to a respective key range of the data; and   rebalance the assigned respective portions of the data among the worker nodes based on the progress indicated by the progress table.   
     
     
         19 . The system of  claim 18 , wherein the one or more processors are configured to write data from the production database to a mutation pool, wherein each respective key range is separately batched, and wherein the mutation pool is configured to flush the data written to the mutation pool to the test environment upon meeting or exceeding a threshold transaction value. 
     
     
         20 . The system of  claim 11 , further comprising the plurality of worker nodes, wherein the plurality of worker nodes are configured to operate independent of one another.