Method and system for copying table data across databases

Abstract

The invention relates to the technical field of data transmission, and provides a cross-database table data copying method and system, and the method comprises the steps: creating an external table of a far-end database through postgresfdw; a COPY grammar support range is increased, a postgresfdw function is expanded by adding a function interface, and a COPY process is modified according to the expanded postgresfdw function; and calling a corresponding function interface according to the positions of the source table and the target table, and copying and transmitting table data by using the modified COPY process. According to the cross-database table data copying method and system, efficient and accurate data copying between the local database and the remote database can be achieved, the operation process is simplified, the data migration efficiency is improved, the data transmission compatibility is improved, repeated development is reduced, and transaction consistency is guaranteed.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of data transmission, and in particular to a method and system for copying table data across databases. BACKGROUND

[0002] In the scenarios of data migration, data redistribution, etc., cross-database table data copying is a common requirement. The existing COPY syntax of openGauss only supports copying table data to a file or standard output, and copying data from a file or standard input to a table, and does not support direct cross-database table-to-table data copying. Or rely on import and export scripts or application layer custom logic to realize fast database data copying, and these methods have the following problems: 1. Low efficiency: data needs to be exported from the source database, and then imported into the target database through the application program or intermediate file, which has a long path and takes more time. 2. Complex operation: involves intermediate storage, multiple network transmissions, etc. 3. Redundant development: different database connection methods need to be repeatedly implemented to adapt to the logic.

[0003] The postgres_fdw (Foreign Data Wrapper) mechanism provided by openGauss provides a unified and efficient kernel-level interface for cross-database access. Through postgres_fdw, users can map tables in remote openGauss databases as local "external tables" to achieve transparent querying and operating. This provides built-in capability support for implementing table copying. However, the original COPY of postgres_fdw only supports copying data from a file to a remote database table, and internally converts it to an insert statement for data insertion, which is very inefficient.

[0004] Chinese Patent No. CN117421371A discloses a method and system for copying tables across databases in a distributed database, a terminal, and a storage medium. In actual application, the scheme has the following deficiencies: 1. Data distribution is performed through a hash value list, and data is evenly distributed to multiple interval segments through a self-incrementing field, which inevitably leads to data distribution skew, causing delays in calculation and replication operations when the data volume is large; 2. Temporary tables or remote tables need to be created and deleted multiple times during data replication, which requires the creation of remote tables on each destination data node and the performance of data replication and deletion operations in multiple steps. This multi-step operation increases the complexity of the system, is prone to errors, consumes additional system resources and time, reduces the efficiency of data replication, and has high management costs in large-scale distributed environments. In the case of hash distribution, multiple hash partition tables need to be established, and remote tables need to be created on each destination data node, which is cumbersome and prone to errors.

[0005] Therefore, how to provide a method for copying table data between different databases based on postgres_fdw while simplifying the operation process, being compatible with the kernel mechanism, improving transmission efficiency, and ensuring consistency has become a technical problem to be solved. SUMMARY

[0006] Therefore, in order to overcome the deficiencies of the prior art, the present application aims to provide a method and system for copying table data across databases.

[0007] According to a first aspect of the present application, a method for copying table data across databases is provided, comprising the following steps: Step S1: creating an external table of a remote database through postgres_fdw; Step S2: increasing the support range of COPY syntax, expanding the function of postgres_fdw by adding function interfaces, and modifying the COPY process according to the expanded postgres_fdw function; Step S3: according to the positions of the source table and the target table, calling the corresponding function interface, and using the modified COPY process to copy and transmit table data.

[0008] Optionally, in the method for copying table data across databases of the present application, in step S1, the postgres_fdw external service function of the local database is connected to the remote database, an external table is created in the local database to map the tables of the remote database, and the user accesses the connected remote database through the external table.

[0009] Optionally, in the method for copying table data across databases of the present application, in step S2, the support range of COPY syntax is increased by modifying the COPY command, wherein the increased support range of COPY syntax includes copying all data of the source database table to the target database table or part of the column, copying the data obtained by conditionally querying the source database table to the target database table or part of the column.

[0010] Optionally, in the method for copying table data across databases of the present application, in step S2, the function of postgres_fdw is expanded by adding function interfaces, including: Adding a COPYTableToForeignTable function interface for copying data from the local database to the remote database; Adding a COPYForeignTableToTable function interface for copying data from the remote database to the local database; An interface of COPYForeignTableToForeignTable function is added to copy data from a source remote database to a target remote database.

[0011] Optionally, in the method for copying table data across databases, in step S2, the COPY process is modified according to the extended postgres_fdw function, including: setting a copy target type COPY_TABLE in the COPY process, and setting an identifier COPY_dest as COPY_TABLE when the identified copy target type COPY_TABLE is table to table.

[0012] Optionally, in the method for copying table data across databases, step S3 includes: analyzing a user COPY request, calling a DoCOPY function to enter a data copy processing process, and identifying a table to table copy target type; judging whether there is at least one table as an external table between a source end table and a target end table, and judging whether the external table is based on a postgres_fdw mechanism when the external table exists; setting an identifier COPY_dest as COPY_TABLE when the external table is based on the postgres_fdw mechanism.

[0013] Optionally, in the method for copying table data across databases, in step S3, when the user's COPY request is to copy data from a remote database to another remote database, a COPYForeignTableToForeignTable interface is called to establish a connection with the source remote database and the target remote database, the source remote external database is set to a state of sending copy data by executing a COPY table TO stdout in the source remote database, the target remote external database is set to a state of receiving copy data by executing a COPY table FROM stdout in the target remote database, a PqgetCOPYData function is called to pull table data from the source remote database, and a PqputCOPYData function is called to send the obtained data to the target remote database.

[0014] Optionally, in the method for copying table data across databases, in step S3, when the COPY request of the user is to copy data from the local database to the remote database, in the data writing processing function COPYSendEndOfRow, it is judged whether COPY_dest is COPY_TABLE. When COPY_dest is identified as COPY_TABLE, the interface COPYTableToForeignTable is called to establish a connection with the remote database where the external table is located according to the information of the external table, the remote database is made to be in a state of receiving copied data by executing the command COPY table FROM stdin in the remote database, the local database table data read is sent to the remote database through the function PQputCOPYData, and the remote database automatically writes the copied table data after receiving the table data.

[0015] Optionally, in the method for copying table data across databases, in step S3, when the COPY request of the user is to copy data from the remote database to the local database, in the data reading function COPYGetDataDefault, it is judged whether COPY_dest is COPY_TABLE. When COPY_dest is identified as COPY_TABLE, the interface COPYForeignTableToTable is called to establish a connection with the remote database, the remote database is made to be in a state of sending COPY data by executing the command COPY table TO stdout in the remote database, the function PqgetCOPYData is called to read the table data from the remote database, and the table data read from the remote database is written into the local database.

[0016] According to a second aspect of the present application, a system for copying table data across databases is provided, which comprises a copy server, the copy server comprising: an external table creating module, configured to create an external table of the remote database through postgres_fdw; a modifying module, configured to increase the range of COPY syntax support, expand the function of postgres_fdw through an added function interface, and modify the COPY process according to the expanded postgres_fdw function; a copy transmission module, configured to call a corresponding function interface according to the positions of the source table and the target table, and perform table data copy transmission by using the modified COPY process.

[0017] According to a third aspect of the present application, a computer device is provided, which comprises a memory, a processor, and a computer program stored in the memory and executable on the processor, and the processor implements the method according to the first aspect of the present application when executing the program.

[0018] The method and system for copying table data across databases have the following beneficial technical effects: 1. Efficient and accurate data copying between local and remote databases is achieved. Through the external table based on postgres_fdw, table data in the local database can be copied to the remote database, and table data in the remote database can also be copied to the local database. In addition, table data in the remote database can be copied to another external table based on postgres_fdw in the remote database. Furthermore, the filtered result data of a local table or an external table query can be copied to the specified part of a column in a remote database table, meeting the needs of different users in complex data migration scenarios.

[0019] 2. Simplifies the operation process and improves data migration efficiency. The long path of data export from the source database, intermediate file or application import to the target database in the traditional method is avoided. Users do not need to rely on complex import and export scripts or application layer custom logic. Data migration can be completed by creating an external table based on postgres_fdw and using the extended COPY command, significantly reducing the number of operation steps and the complexity of data migration. By directly implementing data pulling and pushing at the database kernel level, the time and resource consumption of data transmission are significantly reduced, improving data migration efficiency and user experience.

[0020] 3. Improve data transmission compatibility and reduce repeated development. Through the postgres_fdw framework and COPY protocol of openGauss database, there is no need to customize communication protocols or data packaging logic. The method of the present application can be seamlessly integrated into existing database systems without the need for large-scale modification of the database kernel, reducing the difficulty and risk of technical implementation. The method avoids the high development cost and error-prone problems caused by the need to implement separate adaptation logic for each database interface in traditional methods. The method provides a general solution for data migration between different databases through a unified postgres_fdw interface, reducing the workload of repeated development and improving development efficiency.

[0021] 4. Ensure transaction consistency. By reusing the transaction processing framework of postgres_fdw, good transaction support is provided during data copying. After data migration is completed, the transactions of the local database and the remote database can be synchronized through the callback mechanism, ensuring the consistency and integrity of the data and avoiding data errors caused by inconsistent transactions. BRIEF DESCRIPTION OF DRAWINGS

[0022] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the following will briefly introduce the drawings needed to be used in the embodiments. Obviously, the drawings described in the following are only some embodiments of the present application, and for those skilled in the art, other drawings can also be obtained without creative labor based on these drawings.

[0023] Figure 1 An architecture example diagram of a system for copying table data across databases according to an embodiment of the present application; Figure 2 An architecture example diagram of a copy server of a system for copying table data across databases according to an embodiment of the present application; Figure 3 A step flowchart of a method for copying table data across databases according to an embodiment of the present application; Figure 4 A technical principle example diagram of a method for copying table data across databases according to an embodiment of the present application; Figure 5 An execution example diagram of a method for copying table data across databases according to an embodiment of the present application; Figure 6 An interaction flow example diagram of a method for copying table data across databases according to an embodiment of the present application; Figure 7 A structure schematic diagram of the device provided by the present application. DETAILED DESCRIPTION

[0024] The embodiments of the present application will be described in detail below with reference to the drawings.

[0025] It should be noted that the following embodiments and features in the embodiments can be combined with each other without conflict; and based on the embodiments in the present disclosure, all other embodiments obtained by those skilled in the art without creative labor are within the protection scope of the present disclosure.

[0026] It should be noted that the various aspects of the embodiments described below are within the scope of the appended claims. It should be apparent that the aspects described herein can be embodied in a wide variety of forms and that any specific structure and / or function described herein is merely illustrative. Based on the teachings provided herein one skilled in the art should be able to contemplate these and similar implementations without departing from the scope of the disclosure. For example, an apparatus and / or method could be implemented using any number of the aspects set forth herein. In addition, an apparatus and / or method could be implemented using other structure and / or functionality consistent with the teachings presented herein.

[0027] Figure 1 As an example of an architecture of a system for copying table data across databases according to embodiments of the present application, as shown in FIG. 1, the system can include a copy server 101, a communication network 102, and / or one or more copy clients 103. Figure 1 As an example, the system can include a plurality of copy clients 103. Figure 1 As an example, the system can include a plurality of copy clients 103.

[0028] The copy server 101 can be any suitable server for storing information, data, programs, and / or any other suitable type of content. In some embodiments, the copy server 101 can perform suitable functions. For example, in some embodiments, the copy server 101 can be used for copying table data across databases. As an optional example, in some embodiments, the copy server 101 can be used to: create an external table of a remote database through postgres_fdw; increase the range of COPY syntax support, extend the function of postgres_fdw by adding a function interface, modify the COPY process according to the extended postgres_fdw function; call the corresponding function interface according to the location of the source table and the target table, and perform table data copying transmission using the modified COPY process.

[0029] Figure 2 As an example of an architecture of a copy server of a system for copying table data across databases according to embodiments of the present application, as shown in FIG. 2, the copy server of the present embodiment includes: Figure 2 an external table creation module for creating an external table of a remote database through postgres_fdw; a modification module for increasing the range of COPY syntax support, extending the function of postgres_fdw by adding a function interface, and modifying the COPY process according to the extended postgres_fdw function; a copy transmission module for calling the corresponding function interface according to the location of the source table and the target table, and performing table data copying transmission using the modified COPY process.

[0030] As another example, in some embodiments, the copy server 101 can send a method for copying table data across databases to the copy client 103 for use by a user according to a request of the copy client 103.

[0031] ​As an optional example, in some embodiments, the copy client 103 is configured to provide a visual copy interface configured to receive a selection input operation of the user on the cross-database copy table data, and configured to obtain and display a copy interface corresponding to the option selected by the selection input operation from the copy server 101 in response to the selection input operation, the copy interface at least displaying information of the cross-database copy table data and operation options for the information of the cross-database copy table data.

[0032] In some embodiments, the communication network 102 can be any suitable combination of one or more wired and / or wireless networks. For example, the communication network 102 can include any one or more of the following: the Internet, an intranet, a wide-area network (WAN), a local-area network (LAN), a wireless network, a digital subscriber line (DSL) network, a frame relay network, an asynchronous transfer mode (ATM) network, a virtual private network (VPN), and / or any other suitable communication network. The copy client 103 can connect to the communication network 102 through one or more communication links (e.g., communication link 104), which can link to the copy server 101 via one or more communication links (e.g., communication link 105). The communication links can be any communication links suitable for communicating data among the copy client 103 and the copy server 101, such as network links, dial-up links, wireless links, hard-wired links, any other suitable communication links, or any suitable combination of such links.

[0033] The copy client 103 can include any one or more clients that present interfaces related to the high-speed access ORC external table in a suitable form for use and operation by a user. In some embodiments, the copy client 103 can include any suitable type of device. For example, in some embodiments, the copy client 103 can include a mobile device, a tablet computer, a laptop computer, a desktop computer, and / or any other suitable type of client device.

[0034] Although the copy server 101 is illustrated as one device, in some embodiments, any suitable number of devices can be used to perform the functions performed by the copy server 101. For example, in some embodiments, multiple devices can be used to implement the functions performed by the copy server 101. Alternatively, the functions of the copy server 101 can be implemented using a cloud service.

[0035] Based on the above system, embodiments of the present application provide a method for copying table data across databases, which is illustrated by the following embodiments.

[0036] Figure 3 A flowchart of the steps of a method for copying table data across databases according to an embodiment of the present application.Figure 4 A technical principle diagram of a method for copying table data across databases according to an embodiment of the present application; as Figure 3 and Figure 4 shown, the method for copying table data across databases of the present embodiment can be executed on a copy server, and the method for copying table data across databases includes the following steps: Step S1: Create an external table of a remote database through postgres_fdw.

[0037] Connect the remote database through the postgres_fdw external service function of the local database, create an external table in the local database to map the tables in the remote database, and the user accesses the connected remote database through the external table.

[0038] In the present embodiment, postgres_fdw is an external service function provided by the openGauss database, which is used to connect a remote external database and create an external table in the local database to map the tables in the remote external database, so as to realize accessing the tables in the remote external database like accessing the tables in the local database.

[0039] Step S2: Increase the support range of the COPY syntax, expand the postgres_fdw function through the function interface, and modify the COPY process according to the expanded postgres_fdw function.

[0040] In actual application, the table database can be copied to a specified file or standard output through the COPY table TO command, and the file can be read from the specified file or standard input through the COPY table from, and then written into the table.

[0041] COPY protocol is a mechanism for efficient batch transmission of table data (into and out of the database) in openGauss database, which is based on the data transmission protocol libpq of openGauss, and is divided into COPY_IN state (waiting to accept data) and COPY_OUT state (sending data). When the database is in COPY_IN state, PQputCOPYData can be used to send data to the database, and the database will write the data into the table after receiving the data; when the database is in COPY_OUT state, PQgetCOPYData can be used to pull data from the database. The present embodiment extends the COPY command to support COPYtable TO table and COPY table FROM table syntax, and modifies the COPY processing process, and adds function interfaces for reading data from the remote database and sending data to the remote database in postgres_fdw, so as to realize the function of copying table data across databases.

[0042] As an optional example, in the embodiment, the support range of COPY syntax is increased by modifying the COPY command, wherein the increased support range of COPY syntax includes copying all data of a whole table of a source database to a table of a target database or part of columns of the table, copying data obtained by performing conditional query on a table of a source database to a table of a target database or part of columns of the table. The following is an example of the modified COPY command: COPY table1 TO table2: copy data of a table table1 of a source database to a table table2 of a target database, wherein the table table1 is a table in a local database, and the table table2 is a table in an external database; COPY table1 FROM table2: copy data from a table table2 of a source database to a table table1 of a target database, wherein the table table1 and the table table2 can be a table in a local database or a table in an external database, but at least one of the tables is a table in an external database; COPY (select col1,... FROM table1) TO table2: copy data of a result of conditional query (select col1,... from table1) of a table table1 of a source database to a table table2 of a target external database, wherein the table table1 of the source database can be a table in a local database or a table in an external database, and the table table2 of the target database is a table in an external database; COPY table2(col1,...) FROM (select col1,... FROM table1): copy data of a result of conditional query (select col1,... from table1) of a table table1 of a source database to a column col1 of a table table2 of a target database, wherein the table table1 of the source database can be a table in a local database or a table in an external database, and the table table2 of the target database is a table in an external database.

[0043] As an optional example, in the embodiment, the function interface is increased to expand the function of postgres_fdw, specifically: a COPYTableToForeignTable function interface is added, which is used to copy data of a local database to a remote database; a COPYForeignTableToTable function interface is added, which is used to copy data of a remote database to a local database; a COPYForeignTableToForeignTable function interface is added, which is used to copy data from a source remote database to a target remote database.

[0044] As an optional example, the embodiment modifies the COPY process according to the extended postgres_fdw function, including: setting the copy target type COPY_TABLE in the COPY process, and setting the identifier COPY_dest = COPY_TABLE when the identified copy target type COPY_TABLE is table to table.

[0045] For example, the COPY process is modified to add the copy target type COPY_TABLE. When it is identified that it is a table to table copy, the identifier COPY_dest = COPY_TABLE is set, and the following limitations are made: the local table follows the COPY TO process for copying to the remote table; the remote table follows the COPY FROM process for copying to the local table; and the remote table follows the COPY FROM process for copying to the remote table.

[0046] Step S3: According to the positions of the source table and the target table, the corresponding function interface is called to perform table data copy transmission by using the modified COPY process.

[0047] The user's COPY request is parsed, the DoCOPY function is called to enter the data copy processing process, and the table to table copy target type is identified. It is judged whether there is at least one table as an external table in the source table and the target table, and when there is an external table, it is judged whether the external table is based on the postgres_fdw mechanism. When the external table is based on the postgres_fdw mechanism, the identifier COPY_dest is set as COPY_TABLE.

[0048] When the user's COPY request is to copy data from a remote database to another remote database, the COPYForeignTableToForeignTable interface is called to establish a connection with the source remote database and the target remote database. The source remote external database is in a state of sending copy data by executing COPY table TO stdout in the source remote database, and the target remote external database is in a state of receiving copy data by executing COPY table FROM stdout in the target remote database. The PqgetCOPYData function is called to pull table data from the source remote database, and the PqputCOPYData function is called to send the obtained data to the target remote database.

[0049] When the COPY request of the user is to copy data from the local database to the remote database, it is judged in the write data processing function COPYSendEndOfRow whether COPY_dest is COPY_TABLE. When the COPY_dest identifier is COPY_TABLE, the interface COPYTableToForeignTable is called to establish a connection according to the information of the external table and the remote database where the external table is located, the remote database is made to be in a state of receiving copy data by executing the COPY table FROM stdin command in the remote database, the local database table data read is sent to the remote database through the PQputCOPYData function, and the remote database automatically writes after receiving the copied table data.

[0050] When the COPY request of the user is to copy data from the remote database to the local database, it is judged in the read data function COPYGetDataDefault whether COPY_dest is COPY_TABLE. When the COPY_dest identifier is COPY_TABLE, the interface COPYForeignTableToTable is called to establish a connection with the remote database, the remote database is made to be in a state of sending COPY data by executing the COPY table TO stdout in the remote database, the PQgetCOPYData function is called to read table data from the remote database, and the table data read from the remote database is written into the local database.

[0051] The method for copying table data across databases according to the embodiment of the present application is further described in detail in the following specific scenarios: Figure 5 An execution example diagram of the method for copying table data across databases according to the embodiment of the present application is shown in the following figure: Figure 6 An interaction flow example diagram of the method for copying table data across databases according to the embodiment of the present application is shown in the following figure: Figure 5 And Figure 6 As shown in the above figures, when the user performs remote database data copying in the openGauss database, the processing flow of the openGauss database process is as follows: The database receives the user request, starts a transaction, calls DoCOPY to enter the data copying process, and identifies the table-to-table data COPY operation. The database judges whether at least one of the source table and the target table is an external table, and judges whether the external table is based on postgres_fdw. Then, different situations are processed respectively, and a flag is set to indicate that it is a table-to-table copy (COPY_dest = COPY_TABLE).

[0052] If it is to copy table data from a remote external database to a remote external database, the COPY FROM process is taken, because both the source and the target are remote external databases, the interface in postgres_fdw is directly called, which establishes a connection with the source and target remote external databases at the same time, executes COPY table TO stdout in the source remote external database to make the source remote external database in the state of sending COPY data, executes COPY table FROM stdout in the target remote external database to make the target remote external database in the state of receiving COPY data, then calls PQgetCOPYData to pull table data from the source remote external database, and calls PQputCOPYData to send the obtained data to the target remote external database.

[0053] If it is to copy table data from a remote external database to a remote external database, the COPY FROM process is taken, because both the source and the target are remote external databases, the interface in postgres_fdw is directly called, which establishes a connection with the source and target remote external databases at the same time, executes COPY table TO stdout in the source remote external database to make the source remote external database in the state of sending COPY data, executes COPY table FROM stdout in the target remote external database to make the target remote external database in the state of receiving COPY data, then calls PQgetCOPYData to pull table data from the source remote external database, and calls PQputCOPYData to send the obtained data to the target remote external database.

[0054] If it is to copy table data from a remote external database to a remote external database, the COPY FROM process is taken, because both the source and the target are remote external databases, the interface in postgres_fdw is directly called, which establishes a connection with the source and target remote external databases at the same time, executes COPY table TO stdout in the source remote external database to make the source remote external database in the state of sending COPY data, executes COPY table FROM stdout in the target remote external database to make the target remote external database in the state of receiving COPY data, then calls PQgetCOPYData to pull table data from the source remote external database, and calls PQputCOPYData to send the obtained data to the target remote external database. After the copy operation is executed, the transaction of the local database is committed, and then the transaction started in the remote external database is also committed through the transaction callback mechanism.

[0055] In practical applications, the method and system for copying table data across databases of the embodiment have the following beneficial technical effects: 1. Efficient and accurate data copying between local databases and remote databases Through the external table based on postgres_fdw, the table data of the local database can be copied to the remote database, and the table data of the remote database can also be copied to the local database table. In addition, the table data of the remote database can be copied to another external table based on postgres_fdw remote database table. Furthermore, the filtered result data of the local table or external table query can be copied to the specified part column of the remote database table, meeting the needs of different users in complex data migration scenarios.

[0056] 2. Simplified operation process and improved data migration efficiency The long path of data export from the source library, intermediate file or application import to the target library in the traditional method is avoided. Users do not need to rely on complex import and export scripts or application layer custom logic. Data migration can be completed by creating an external table based on postgres_fdw and using an extended COPY command, which greatly reduces the operation steps, reduces the complexity of data migration, and significantly reduces the time and resource consumption of data transmission by directly implementing data pulling and pushing at the database kernel level, thereby improving data migration efficiency and user experience.

[0057] 3. Improved data transmission compatibility and reduced repeated development Through the postgres_fdw framework and COPY protocol of the openGauss database, without customizing the communication protocol or data packaging logic, the method of the present application can be seamlessly integrated into the existing database system without the need for large-scale modification of the database kernel, thereby reducing the difficulty and risk of technical implementation. The method avoids the high development cost and error-prone problems caused by the need for separate implementation of adaptation logic for each database interface in traditional methods. The method provides a general solution for data migration between different databases through a unified postgres_fdw interface, reduces the workload of repeated development, and improves development efficiency.

[0058] 4. Ensuring transaction consistency By reusing the transaction processing framework of postgres_fdw, good transaction support is provided during data copying. After data migration is completed, the transactions of the local database and the remote database can be synchronized through the callback mechanism, ensuring the consistency and integrity of the data and avoiding data errors caused by inconsistent transactions.

[0059] For example Figure 7As shown, the application further provides a device comprising a processor 210, a communication interface 220, a memory 230 for storing a processor-executable computer program, and a communication bus 240. The processor 210, the communication interface 220, and the memory 230 can communicate with each other through the communication bus 240. The processor 210 can realize the method for copying table data across databases described above by running the executable computer program.

[0060] The computer program in the memory 230 can be implemented in the form of a software functional unit and sold or used as an independent product. Based on this understanding, the technical solutions of the present application essentially or the part that contributes to the prior art or part of the technical solutions can be embodied in the form of a software product. The computer software product is stored in a storage medium and includes a plurality of instructions for causing a computer device (which can be a personal computer, a server, or a network device, etc.) to execute all or part of the steps of the methods of the embodiments. The aforementioned storage medium includes a U disk, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk or an optical disk, and various media that can store program codes.

[0061] The system embodiments described above are merely illustrative. The units illustrated as separate components can or can not be physically separated, and the components illustrated as units can or can not be physical units, i.e., they can be located in one place or distributed on multiple network units. Part or all of the modules can be selected based on actual needs to achieve the purpose of the embodiments. Those skilled in the art can understand and implement without creative labor.

[0062] From the above description of the embodiments, those skilled in the art can clearly understand that the embodiments can be implemented by means of software and the necessary general hardware platform, and of course, can also be implemented by hardware. Based on this understanding, the above technical solutions essentially or the part that contributes to the prior art can be embodied in the form of a software product. The computer software product can be stored in a computer readable storage medium such as a ROM / RAM, a magnetic disk, an optical disk, etc., and includes a plurality of instructions for causing a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods of the embodiments or some parts of the embodiments.

[0063] The above merely illustrates the specific embodiments of the present application, but the protection scope of the present application is not limited thereto, any person skilled in the art can easily think of the changes or replacements within the technical range disclosed by the present application, which should be covered in the protection scope of the present application. Therefore, the protection scope of the present application should be subject to the protection scope of the claims.

Claims

1. A method for copying table data across databases, characterized in that: The method comprises the following steps: Step S1: Create an external table of the remote database through postgres_fdw; Step S2: Increase the scope of COPY syntax support, expand the postgres_fdw function by adding a function interface, and modify the COPY process according to the expanded postgres_fdw function; Step S3: Based on the locations of the source table and the target table, the corresponding function interface is called, and the modified COPY process is used to copy and transfer the table data.

2. The method for copying table data across databases according to claim 1, characterized in that: In step S1, the remote database is connected through the postgres_fdw external service function of the local database, an external table is created in the local database to map the table of the remote database, and the user accesses the table of the connected remote database by accessing the external table.

3. The method for copying table data across databases according to claim 1, characterized in that: In step S2, the COPY syntax support range is increased by modifying the COPY command, wherein the increased support range of the COPY syntax includes copying all data in the entire table of the source database to the table of the target database or part of the column of the table, and copying the data obtained after conditional query in the table of the source database to the table of the target database or part of the column.

4. The method for copying table data across databases according to claim 1, characterized in that: In step S2, the postgres_fdw function is expanded by adding function interfaces, including: Added COPYTableToForeignTable function interface to copy data from the local database to the remote database; Added COPYForeignTableToTable function interface to copy data from a remote database to a local database; Added the COPYForeignTableToForeignTable function interface, which is used to copy data from the source remote database to the target remote database.

5. The method for copying table data across databases according to claim 1, characterized in that: In step S2, the COPY process is modified according to the expanded postgres_fdw function, including: setting the copy target type COPY_TABLE identification in the COPY process, and when the identified copy target type COPY_TABLE is table to table, setting the identifier COPY_dest to COPY_TABLE.

6. The method for copying table data across databases according to claim 1, characterized in that: Step S3 includes: Parse the user's COPY request, call the DoCOPY function to start the data copy process, and identify the target type of the table-to-table copy; Determine whether at least one of the source and target tables is an external table. If an external table exists, determine whether the external table is based on the postgres_fdw mechanism. When the external table is based on the postgres_fdw mechanism, set the flag COPY_dest to COPY_TABLE.

7. The method for copying table data across databases according to claim 1, characterized in that: In step S3, when the user's COPY request is to copy data from a remote database to another remote database, the COPYForeignTableToForeignTable interface is called to establish connections with the source remote database and the target remote database at the same time. By executing COPY table TO stdout on the source remote database, the source remote external database is put into a state of sending copied data. By executing COPY table FROM stdout on the target remote database, the target remote external database is put into a state of receiving copied data. The PqgetCOPYData function is called to pull table data from the source remote database, and the PqputCOPYData function is called to send the obtained data to the target remote database.

8. The method for copying table data across databases according to claim 1, characterized in that: In step S3, when the user's COPY request is to copy data from the local database to the remote database, the write data processing function COPYSendEndOfRow determines whether COPY_dest is COPY_TABLE. When COPY_dest is COPY_TABLE, the COPYTableToForeignTable interface is called to establish a connection with the remote database where the external table is located based on the information of the external table. The remote database is put into a state of receiving copied data by executing the COPY table FROM stdin command in the remote database. The read local database table data is sent to the remote database through the PQputCOPYData function. The remote database automatically writes the copied table data after receiving it.

9. The method for copying table data across databases according to claim 1, characterized in that: In step S3, when the user's COPY request is to copy data from the remote database to the local database, the data reading function COPYGetDataDefault determines whether COPY_dest is COPY_TABLE. When COPY_dest is marked as COPY_TABLE, the COPYForeignTableToTable interface is called to establish a connection with the remote database. By executing COPY table TO stdout in the remote database, the remote database is put into a state of sending COPY data. The PqgetCOPYData function is called to read the table data from the remote database, and the table data read from the remote database is written to the local database.

10. A system for copying table data across databases, characterized in that: The system includes a copy server, and the copy server includes: External table creation module, used to create external tables of remote databases through postgres_fdw; Modify the module to increase the scope of COPY syntax support, expand the postgres_fdw function by adding function interfaces, and modify the COPY process based on the expanded postgres_fdw function; The copy transmission module is used to call the corresponding function interface according to the location of the source table and the target table, and use the modified COPY process to copy and transmit table data.

Images