At S240, a plurality of files is generated. In an embodiment, the plurality of files is generated in a column-oriented data format, such as Apache® Parquet. In some embodiments, the plurality of files is generated such that a file, a group of files, etc., corresponds to a result of executing a query of the plurality of queries. Thus, data is exported from the database into a plurality of data files.

In an embodiment, data is exported from the database application into the plurality of files by generating the plurality of queries, executing each query on the database, receiving a result for each query, and storing the results as a plurality of data files in a column-oriented data format.

In an embodiment, for example, where a logical backup is generated (e.g., utilizing pg_dump command), the files are generated by converting the logical backup into a plurality of column-oriented data format files. In another embodiment, the files are generated by parsing the database files.

At S250, a database data backup is generated. In an embodiment, the data backup is generated based on the plurality of data files. In some embodiments, the data backup includes a timestamp, a version identifier, and the like, which indicate a date, a time, a combination thereof, and the like, at which the data backup was generated. In an embodiment, the data backup is utilized in restoring a database. In certain embodiments, the data backup is stored in a cloud storage environment, such as AWS S3.

In some embodiments, the data backup includes a data structure, such as metadata of the database, a data schema of the database, table data, a store procedure, a view, a combination thereof, and the like. In an embodiment, database data (e.g., schema, views, store procedures, etc.) are extracted from a dump, for example utilizing pg_dump, without storing the data itself. Thus, a pg_dump command can be utilized to generate the data files (e.g., Parquet files) and to generate the machine backup, e.g., by extracting the metadata of the database, including views, store procedures, schema, etc.

It should be noted that a data backup is not the same as a storage backup. In a storage backup, a block-for-block copy of the storage device is created, which includes the database data and additionally, a lot of data that is not useful for the actual database application, such as temporary files. It is therefore advantageous to store a backup only of the data on the database, without all the unnecessary files, folders, etc., which are not essential for the database to function properly.

In certain embodiments, a machine backup is generated, which includes data of the machine that is utilized to deploy the database application. Restoring a machine backup to a machine allows deployment of a machine that functions as the original machine, sans the data of the database. Once the data of the database application is written there, the machine is fully restored and functional.

In an embodiment, a machine backup is generated as a file-level backup, as a block-level backup, as a product key store, a combination thereof, and the like.

In an embodiment, a machine backup includes data, information, and the like, which is utilized in restoring a machine. In some embodiments, restoring a machine includes generating a new machine according to the parameters of the original machine hosting the database.

FIG. 3 is an example flowchart of a method for selecting a primary key for generating a backup for minimizing differential backup size, implemented in accordance with an embodiment. In an embodiment, it is advantageous to detect a key that allows for grouping together of rows that change, when utilizing column-oriented file formats for storing a backup.

For example, by grouping together changes in the database, such that rows that are likely to change are in the same row group, the total number of row groups that include a row that is changed is reduced, and thus the number of differential backups that need to be stored is likewise reduced. As an example, when a first-row group includes a row that has a change, and a second-row group includes a row that has a change, a differential backup needs to be generated for each row group. However, if it is possible to regroup the rows, such that the two rows that include a change are in the same group, then only a single group requires a differential backup, which reduces the amount of storage required for the backup.

In some embodiments, the backup is stored in a plurality of objects referred to as “backup objects.” According to an embodiment, these backup objects are arranged, ordered, and the like, in such a way that only a relatively small number of them typically need to be updated. This approach leads to more efficient backup and deduplication processes. For example, consider a table in an append-only database that includes two columns: Social Security Number (SSN) and the date when each respective SSN was added to the table. In this case, the rows of the table are ordered by the date column. An append-only database is a type of database where data can only be added, so organizing the data by date during a backup minimizes the number of updates to the backup, as previous data record reads are minimal. Alternatively, arranging such a backup by ascending (or descending) order of the SSN itself might result in having to read a previous data record of a backup (which includes several rows), and updating that data record to now include the new data for a current backup.

As another example, consider a database table that includes three columns: age, date of birth, and name. The age column is updated when a person's birthday occurs. Therefore, it is more efficient, according to an embodiment, to order the data according to a key that includes the date of birth, as this grouping would require fewer changes when updates are made.

At S310, a plurality of rows are extracted from a database. In an embodiment, the plurality of rows is extracted utilizing a primary key of the database. In some embodiments, a primary key is generated based on a composite of multiple-column identifiers. For example, in an embodiment, two identifiers, each of a distinct column, form together a primary key. In certain embodiments, a plurality of primary keys are selected, each primary key corresponding to a table of the database. In an embodiment, a primary key is a database key which is an attribute or a set of attributes in a database table that is used to identify and establish relationships between records uniquely. For example, according to an embodiment, the primary key is an index key. In some embodiments, the key is a unique key, which ensures all values in a column or combination of columns are unique across records. In certain embodiments, the key is a composite key, such as selected by a combination of columns.

In an embodiment, the plurality of rows are extracted from the database utilizing the methods discussed in more detail herein, for example with respect to FIG. 2. In an embodiment, the primary key includes a value assigned to each row, which is a unique value, such that no two rows include the same value of the primary key.

At S320, a backup is generated. In an embodiment, the backup includes a plurality of files. In some embodiments, the plurality of files is generated in a column-oriented data format, such as Apache® Parquet. In some embodiments, the plurality of files is generated such that a file, a group of files, etc., corresponds to a result of executing a query of the plurality of queries. Thus, data is exported from the database into a plurality of data files.