Database migration backup method and system

Abstract

The invention discloses a database migration backup method and system. The system comprises a parameter input module and a data export module, wherein the data export module is used for exporting data and comprises a table information export unit, a data export unit, a log recording unit, an error processing unit and a permission modification unit; the data transmission module is used for data transmission and comprises a script transmission unit, a data transmission unit, a log recording unit II and a file verification unit; and the data import module is used for importing data and comprises a data import unit, a log recording unit III, a data verification unit and a circulation unit. According to the method, the data migration efficiency is greatly improved, manual operation errors are effectively avoided, and the method has remarkable practical value and popularization potential. The method is suitable for data export and migration scripts of a GreenPlum database cluster in a Centos7 environment, and is particularly suitable for database backup and migration of database management in a multi-tenant and multi-GreenPlum cluster environment of a company.

Description

Technical Field

[0001] The present invention belongs to the field of database technology, and provides a database migration backup method and system. A script suitable for data export and migration of Greenplum database clusters in a Centos7 environment is suitable for database backup and migration of database management in a multi-tenant, multi-Greenplum cluster environment of the company. Background Art

[0002] With the widespread application of big data analysis and multi-tenant environments, database migration and backup have become key links in the company's data management. Especially for Greenplum database, as a parallel processing database system, the need for efficient migration of large-scale data is particularly urgent. Traditional database migration methods usually involve complex manual operations, which are prone to errors and inefficient, especially in multi-tenant environments, facing problems such as different database names for different tenants, different users under the database, and different tablespaces. Existing technologies make it difficult to achieve automated data export and import across different database names. Summary of the invention

[0003] The present invention provides an automated data export and migration script for a Greenplum database in a Centos7 environment, which is particularly suitable for backup and migration of databases in a multi-tenant, multi-Greenplum cluster environment of the company.

[0004] A database migration backup method, the specific implementation steps are as follows:

[0005] Step 1: Input parameters

[0006] 1-1. First, define and receive the main parameters: source tenant ID, SchemaID, target tenant ID, source database name, target database name, source database user, target database user, source IP, and target IP. After starting the script (i.e., enabling this method), connect the source database and the target database. If the connection fails, that is, the input is wrong, the system will prompt the user to enter the correct parameters and exit the execution. Before the script starts executing, manually create an sfs folder in the root directory to store the export files and log files of all databases.

[0007] Step 2: Export Data

[0008] 2-1. Export table information: According to the input source database name, connect to the source database, use the pg_dump command to export the schema table structure information and save it as a .sql file.

[0009] 2-2. Export data: Use the pg_dump command in conjunction with the table structure information file (i.e. the table structure information .sql file exported in step 2-1), loop through the tables in the file, export all tables, and save them in .CSV file format.

[0010] 2-3. Log Recording: Each time a table is exported, the status in the log file is updated, and the export time and file path are recorded.

[0011] 2-4. Error handling: If a table fails to be exported, the script records the error message, skips the table, and continues to execute the subsequent export plan without affecting the overall execution of the script. After all steps are completed, the data table is manually imported according to the error message.

[0012] 2-5. Modify permissions: After the data is exported, modify the table structure information file (that is, the table structure .sql file exported in step 2-1), and replace the source tenant ID in the file with the target tenant ID using the sed command.

[0013] Step 3: Transfer Data

[0014] 3-1. Transfer script: Use the SCP command to transfer the script file from the source server to the user directory of the target server according to the target IP.

[0015] 3-2. Data transfer: After the script file is transferred, transfer the sfs folder (that is, the folder generated in step 1-1) to the target server using the SCP command.

[0016] 3-3. Log recording: During the transmission process, the transmission progress, file size, transmission time and other information are recorded in real time in the log file to facilitate subsequent investigation and audit.

[0017] 3-4. Verify the file: After the SCP command completes the transmission, use the du command to compare the size of the source server folder and the target server folder to confirm the data integrity. If the size does not match during the verification, a retransmission will be performed without verification to prevent the script from taking too long.

[0018] Step 4: Import Data

[0019] 4-1. Import data: Based on the table structure information file (i.e. the table structure information .sql file exported in step 2-1), back up the target database Schema, create a new Schema, and use the psql command loop to import the data table file into the target database.

[0020] 4-2. Log Recording: Each time a table is imported, the log file is updated to record the name of the successfully imported table and the output information of the psql command.

[0021] 4-3. Data verification: Use the psql command to enter the database, use the meta command \l+ to view the database size, and compare the source and destination databases to verify data integrity.

[0022] 4-4. Loop step: The script determines the remaining schemas. If there are any left, it continues to loop and start the data migration operation from step 2-1.

[0023] The beneficial effects of the present invention are as follows:

[0024] The present invention provides an efficient database migration method for the company in the current multi-tenant, multi-GreenPlum cluster environment. Through the automated solution, the present invention achieves the following advantages:

[0025] 1. Reduce operational error rate: Compared with traditional data migration solutions, the present invention effectively reduces human operational errors through a highly automated process, ensuring the accuracy and reliability of data migration.

[0026] 2. Improve work efficiency: The present invention provides a seamless data migration solution, so that databases of different tenants can be efficiently migrated between multiple GreenPlum clusters, greatly improving work efficiency.

[0027] 3. Strong practicability: Although the data transmission speed is not significantly improved, the present invention provides a stable and reliable solution in actual work, which is particularly suitable for complex environments with multiple tenants and multiple clusters.

[0028] In summary, the present invention not only improves the reliability and efficiency of data migration, but also provides important technical support for data management in a multi-tenant, multi-GreenPlum cluster environment. BRIEF DESCRIPTION OF THE DRAWINGS

[0029] Figure 1 This is a schematic diagram of the parameter input process of the present invention.

[0030] Figure 2 It is a schematic diagram of the data export process of the present invention.

[0031] Figure 3 It is a schematic diagram of the data transmission process of the present invention.

[0032] Figure 4 It is a schematic diagram of the data import process of the present invention.

[0033] Figure 5 It is a schematic diagram of the traditional database backup process of the present invention.

[0034] Figure 6 The figure is a schematic diagram of the automated Greenplum database export and migration process of the present invention. DETAILED DESCRIPTION

[0035] The present invention will be further described below in conjunction with the accompanying drawings and embodiments.

[0036] like Figure 1-6 As shown, the present invention also provides a database migration backup system, which specifically includes the following:

[0037] Parameter input module: used to define and receive the main parameters: source tenant ID, SchemaID, target tenant ID, source database name, target database name, source database user, target database user, source IP and target IP, such as Figure 1 As shown. The system mainly connects the source database and the target database. If the connection fails, that is, the input is wrong, the system will prompt the user to enter the correct parameters and exit the execution. Before the script starts executing, manually create an sfs folder in the root directory to store the export files and log files of all databases.

[0038] Data export module: used for data export, including table information export unit, data export unit, log recording unit, error handling unit and permission modification unit;

[0039] Table information export unit: connects to the source database according to the input source database name, exports the table structure information of the schema through the pg_dump command and saves it as a .sql file.

[0040] Data export unit: Use the pg_dump command to combine the table structure information file of the table information export unit (i.e., the table structure information .sql file), loop through the tables in the file, export all tables, and save them in .CSV file format.

[0041] Log Recording Unit I: Each time a table is exported, the status in the log file is updated, and the export time and file path are recorded.

[0042] Error handling unit: If a table fails to be exported, the script records the error information, skips the table, and continues to execute the subsequent export plan without affecting the overall execution of the script. After all steps are completed, the data table is manually imported according to the error information.

[0043] Permission modification unit: After the data export is completed, modify the table structure information file (i.e., table structure information .sql file) and replace the source tenant ID in the file with the target tenant ID through the sed command.

[0044] Data transmission module: used for data transmission, including script transmission unit, data transmission unit, log recording unit II, and file verification unit.

[0045] Script transfer unit: transfers the script file from the source server to the user directory of the target server through the SCP command according to the target IP.

[0046] Data transmission unit: After the script file is transmitted, the sfs folder (i.e., the folder generated by the parameter input module) is transmitted to the target server through the SCP command.

[0047] Log Recording Unit II: During the transmission process, the transmission progress, file size, transmission time and other information are recorded in real time in the log file to facilitate subsequent investigation and audit.

[0048] File verification unit: After the SCP command completes the transmission, use the du command to compare the size of the source server folder and the target server folder to confirm data integrity. If the size does not match during the verification, a retransmission will be performed without verification to prevent the script from taking too long.

[0049] Data import module: used for data import, including data import unit, log recording unit III, data verification unit, and circulation unit;

[0050] Data import unit: according to the table structure information file (i.e., table structure information .sql file), back up the target database schema, create a new schema, and use the psql command loop to import the data table file into the target database.

[0051] Logging Unit III: Each time a table is imported, the log file is updated to record the name of the successfully imported table and the output information of the psql command.

[0052] Data verification unit: Use the psql command to enter the database, use the meta command \l+ to view the database size, and compare the source and destination databases to verify data integrity.

[0053] Loop unit: The script determines the remaining schemas. If there are any remaining, it continues to loop and start the data migration operation from the data export module.

[0054] Furthermore, the present invention realizes automatic migration of database through scripts. The following are the main implementation steps.

[0055] 1. Set up password-free login: The source server needs to log in to the target server without a password. This step can be completed by setting up ssh password-free login. This can avoid manually entering the password every time you connect during the script running process.

[0056] 2. Upload the script: Upload the script file to the home directory of gpadmin on the source server.

[0057] 3. Confirm parameters: When executing the script, collect relevant parameters, including source tenant ID, SchemaID, target tenant ID, source database name, target database name, source database user, target database user, source IP, and target IP.

[0058] 4. Check the database status: confirm whether the source and target servers are running normally and whether there is enough hard disk space.

[0059] 5. Execute the script: Enter the collected parameters and use the bash command to execute the script.

[0060] 6. Clean up data: After the script is executed, clean up the exported data file.

[0061] Experimental data

[0062] Furthermore, in order to evaluate the efficiency of the present invention, we conducted an experimental comparison with the traditional manual backup method, and the specific results are as follows:

[0063] Traditional methods require administrators to manually perform steps such as data export, transfer, import, and permission settings. Average operation time: Each migration takes an average of about 10 hours. Error rate: The error rate is as high as 10%, mainly due to human errors that are prone to occur during manual operations.

[0064] The present invention realizes data migration through automated scripts, reduces the time of manual operation, and ensures the consistency and integrity of data. Average operation time: The average time of each migration is shortened to 8 hours. Error rate: The error rate is 0%, and the automated process ensures the accuracy of the operation.

[0065] Experimental results show that the automated script provided by the present invention has significant advantages in database migration and backup:

[0066] 1. Improve efficiency: Through automated processing, the average time required for each migration was reduced from 10 hours to 8 hours, improving work efficiency by 20%.

[0067] 2. Reduce error rate: The automated process completely eliminates human operational errors, reducing the error rate from 10% to 0%, significantly improving the reliability and accuracy of data migration.

[0068] 3. Enhanced practicality: The present invention provides a practical and efficient method for database management in a multi-tenant, multi-GreenPlum cluster environment, which is particularly suitable for enterprises that need to frequently migrate data.

[0069] In summary, the present invention not only greatly improves the efficiency of data migration, but also effectively avoids human operation errors, and has significant practical value and promotion potential.

Claims

1. A database migration and backup method, characterized in that: Here are the steps: Step 1: Input parameters; Step 2: Export data; Step 3: Transfer data; Step 4: Import data; Step 1 is implemented as follows: 1-1. First, define and receive the main parameters: source tenant ID, SchemaID, target tenant ID, source database name, target database name, source database user, target database user, source IP, and target IP; 1-2. Manually create an sfs folder in the root directory to store the export files and log files of all databases.

2. A database migration and backup method according to claim 1, characterized in that: Step 2 is implemented as follows: 2-1. Export table information: According to the input source database name, connect to the source database, use the pg_dump command to export the schema table structure information and save it as a .sql file; 2-2. Export data: Use the pg_dump command in conjunction with the table structure information file, loop through the tables in the file, export all tables and save them in .CSV file format; 2-3. Log Recording: Each time a table is exported, the status in the log file is updated, and the export time and file path are recorded; 2-4. Error handling: If a table fails to be exported, the script records the error information, skips the table, and continues to execute the subsequent export plan without affecting the overall execution of the script. After all steps are completed, the data table is manually imported according to the error information; 2-5. Modify permissions: After the data is exported, modify the table structure information file and replace the source tenant ID in the file with the target tenant ID using the sed command.

3. A database migration and backup method according to claim 2, characterized in that: Step 3 is implemented as follows: 3-1. Transfer script: Use the SCP command to transfer the script file from the source server to the user directory of the target server according to the target IP; 3-2. Data transfer: After the script file is transferred, transfer the sfs folder to the target server using the SCP command; 3-3. Log Recording: During the transmission process, the transmission progress, file size, and transmission time information are recorded in real time in the log file to facilitate subsequent investigation and auditing; 3-4. Verify the file: After the SCP command completes the transmission, use the du command to compare the size of the source server folder and the target server folder to confirm the data integrity. If the size does not match during the verification, a retransmission will be performed without verification to prevent the script from taking too long.

4. A database migration and backup method according to claim 3, characterized in that: Step 4 is implemented as follows: Step 4: Import Data 4-1. Import data: Based on the table structure information file, back up the target database Schema, create a new Schema, and use the psql command loop to import the data table file into the target database; 4-2. Log Recording: Each time a table is imported, the log file is updated to record the name of the successfully imported table and the output information of the psql command; 4-3. Data verification: Use the psql command to enter the database, use the meta command \l+ to view the database size, and compare the source and destination databases to verify data integrity; 4-4. Loop step: The script determines the remaining schemas. If there are any left, it continues to loop and start the data migration operation from step 2-1.

5. A database migration and backup method according to claim 1, characterized in that This method is used to connect the source database and the target database. If the connection fails, that is, the input is wrong, the system will prompt the user to enter the correct parameters and exit the execution.

6. A database migration backup system, characterized in that The system includes: Parameter input module: used to define and receive the main parameters: source tenant ID, SchemaID, target tenant ID, source database name, target database name, source database user, target database user, source IP and target IP. The system mainly connects the source database and the target database. If the connection fails, that is, the input is wrong, the system will prompt the user to enter the correct parameters and exit the execution. Before the script starts executing, manually create an sfs folder in the root directory to store the export files and log files of all databases. Data export module: used for data export, including table information export unit, data export unit, log recording unit, error handling unit and permission modification unit; Table information export unit: connects to the source database according to the input source database name, exports the table structure information of the schema through the pg_dump command and saves it as a .sql file; Data export unit: Use the pg_dump command to combine the table structure information file of the table information export unit, loop through the tables in the file, export all tables and save them in .CSV file format; Log Recording Unit I: Each time a table is exported, the status in the log file is updated, and the export time and file path are recorded; Error handling unit: If a table fails to be exported, the script records the error information, skips the table, and continues to execute the subsequent export plan without affecting the overall execution of the script. After all steps are completed, the data table is manually imported according to the error information; Permission modification unit: After the data export is completed, modify the table structure information file and replace the source tenant ID in the file with the target tenant ID through the sed command; Data transmission module: used for data transmission, including script transmission unit, data transmission unit, log recording unit II, and file verification unit; Script transfer unit: transfers the script file from the source server to the user directory of the target server through the SCP command according to the target IP; Data transmission unit: After the script file is transferred, the sfs folder is transferred to the target server through the SCP command; Log Recording Unit II: During the transmission process, the transmission progress, file size, and transmission time information are recorded in real time in the log file to facilitate subsequent investigation and audit; File verification unit: After the SCP command completes the transmission, the du command is used to compare the size of the source server folder and the target server folder to confirm the data integrity. If the size does not match during the verification, a retransmission will be performed without verification to prevent the script from taking too long. Data import module: used for data import, including data import unit, log recording unit III, data verification unit, and circulation unit; Data import unit: back up the target database schema according to the table structure information file, create a new schema, and use the psql command loop to import the data table file into the target database; Logging Unit III: Each time a table is imported, the log file is updated to record the name of the successfully imported table and the output information of the psql command; Data verification unit: Use the psql command to enter the database, use the meta command \l+ to check the database size, and compare the source and destination databases to verify data integrity; Loop unit: The script determines the remaining schemas. If there are any remaining, it continues to loop and start the data migration operation from the data export module.

Images