Help Center/ GaussDB/ Developer Guide(Distributed_V2.0-8.x)/ Best Practices/ Best Practices for Import and Export Using COPY/ Typical Scenarios/ Importing and Exporting Data with Extreme Performance
Updated on 2025-05-29 GMT+08:00
View PDF
Share

Importing and Exporting Data with Extreme Performance

When there are strict demands for data import and export performance, and the import and export occur between clusters of the same version, you can use the BINARY format to read and store data as binary numbers other than regular text. Although this format provides performance advantages over others, it comes with the following limitations:

  1. The BINARY format is specific to GaussDB, making it non-portable. It is advisable to use this format only for importing and exporting data between databases of the same version.
  2. The BINARY format is tightly coupled with specific data types. For example, while the TEXT format allows exporting data from a smallint field and importing it into an integer column, this is not possible with the BINARY format.
  3. Certain data types cannot be imported or exported using the BINARY format. For details, see BINARY limitations.

Export command:

1
2
3
4
 
-- data_source can be a table name or a SELECT statement.
-- server_encoding can be obtained using SHOW server_encoding.
SET client_encoding = '{server_encoding}';
COPY {data_source} TO '/path/export.bin' binary;

Import command:

1
2
3
4
 
-- data_destination can only be a table name.
-- file_encoding indicates the encoding format used during binary file export.
SET client_encoding = '{file_encoding}';
COPY {data_destination} FROM '/path/export.bin' binary;

Before choosing the BINARY format, carefully review its limitations. Use it to enhance import and export performance only when you are completely certain that these limitations will not impact the data to be exported.

Examples

  1. Prepare data. 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
    		 
    gaussdb=# REATE DATABASE db1 ENCODING='UTF-8' LC_COLLATE='en_US.UTF-8' LC_CTYPE ='en_US.UTF-8' DBCOMPATIBILITY = 'ORA';CREATE DATABASE
gaussdb=# \c db1
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "db1" as user "omm".

db1=# CREATE TABLE test_copy(id int, name text);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'id' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE

db1=# INSERT INTO test_copy VALUES(1, 'aaa');
INSERT 0 1
db1=# INSERT INTO test_copy VALUES(3, e'cc\tc');
INSERT 0 1
db1=# INSERT INTO test_copy(name) VALUES('ddd');
INSERT 0 1
db1=# INSERT INTO test_copy VALUES(5, e'ee\\e');
INSERT 0 1
db1=# INSERT INTO test_copy VALUES(6, ',');
INSERT 0 1
db1=# INSERT into TEST_COPY VALUES(7, '"');
INSERT 0 1

db1=# SELECT * FROM test_copy;
 id |   name    
----+-----------
  1 | aaa
  3 | cc      c
    | ddd
  5 | ee\e
  6 | ,
  7 | "
(6 rows)

  2. Export data. 

    1
2
3
4
    		 
    db1=# SET client_encoding = 'UTF-8';
SET
db1=# COPY test_copy TO '/home/xy/test.bin' BINARY;
COPY 6

  3. Import data. 

    db1=# TRUNCATE test_copy;
TRUNCATE TABLE

db1=# SET client_encoding = 'UTF-8';
SET
db1=# COPY test_copy FROM '/home/xy/test.bin' BINARY;
COPY 6

Parent topic: Typical Scenarios