Bu sayfa henüz yerel dilinizde mevcut değildir. Daha fazla dil seçeneği eklemek için yoğun bir şekilde çalışıyoruz. Desteğiniz için teşekkür ederiz.

Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive
Help Center/ GaussDB/ User Guide/ Database Migration/ Using gs_dump and gs_dumpall to Export Data

Using gs_dump and gs_dumpall to Export Data

Updated on 2025-01-09 GMT+08:00

Scenarios

GaussDB provides gs_dump and gs_dumpall to export required database objects and related information. You can use a tool to import the exported data to a destination database for database migration. gs_dump can export a single database or its objects. gs_dumpall can export all databases or global objects in the database. For details, see Table 1.

NOTE:

In the multitenancy scenario, gs_dump can be used to export a single PDB or its objects, but gs_dumpall cannot support this scenario.

Table 1 Scenarios

Scenario

Export Granularity

Export Format

Import Method

Exporting a single database

Database-level export (see Exporting a Database).

  • Export full information of a database.

    You can use the exported information to create the same database containing the same data.

  • Export all object definitions of a database, including the definitions of the database, functions, schemas, tables, indexes, and stored procedures.

    You can use the exported object definitions to quickly create the same database, without data.

  • Export data of a database.
  • Plain-text
  • Custom
  • Directory
  • TAR
  • Use gsql to import plain-text data files. For details, see "Client Tools" > "gsql" in Tool Reference.
  • For details about how to import data files in .tar, directory, or custom format, see Using gs_restore to Import Data.

Schema-level export (see Exporting a Schema).

  • Export full information of a schema.
  • Export data of a schema.
  • Export all object definitions of a schema, including the definitions of tables, stored procedures, and indexes.
Table-level export (see Exporting a Table).
  • Export full information of a table.
  • Export data of a table.
  • Export the definition of a table.

Exporting all databases

Database-level export (see Exporting All Databases).

  • Export full information of databases.

    You can use the exported full information to create the same host environment containing the same databases and public global objects, with the same data.

  • Export all object definitions of databases, including the definitions of tablespaces, databases, functions, schemas, tables, indexes, and stored procedures.

    You can use the exported object definitions to quickly create the same host environment containing the same databases and tablespaces but without data.

  • Export data of databases.

Plain-text

For details about how to import data files, see Using copy from to Import Data.

Public global object export (see Exporting Global Objects).
  • Export tablespaces.
  • Export roles.
  • Export tablespaces and roles.

gs_dump and gs_dumpall use -U to specify the user that performs the export. If the specified user does not have the required permissions, data cannot be exported. For details about the scenarios where this function can be used, see Table 1.

Precautions

gs_dump and gs_dumpall encrypt the exported data files. These files are decrypted before being imported to prevent data disclosure for higher database security. Note that gsql cannot decrypt and import stored procedures and functions for plain-text files encrypted using gs_dump. Therefore, if the exported database contains stored procedures or functions, use the other three modes to export the database and use gs_restore to restore the database.

When gs_dump or gs_dumpall is used to export data, other users can still operate (read or write) the database.

gs_dump and gs_dumpall can export complete, consistent data. For example, if gs_dump exports data from database A or gs_dumpall exports data from GaussDB database at T1, the exported data is the data status of database A or GaussDB database at T1. Modified data of database A or GaussDB database after T1 will not be exported.

When gs_dump or gs_dumpall is used to export data, generated columns are not dumped.

  • Do not modify the files and contents exported using the -F c/d/t format. Otherwise, the restoration may fail. For files exported using the -F p format, edit the exported files with caution if necessary.
  • If the number of objects (data tables, views, and indexes) in the database exceeds 500,000, you are advised to contact technical support to improve performance and avoid memory problems.
  • To ensure data consistency and integrity, the export tools will set a shared lock for the tables to be dumped. If a shared lock has been set for the table in other transactions, gs_dump and gs_dumpall lock the table after it is released. If the table cannot be locked within the specified time, the dump fails. You can customize the timeout duration to wait for lock release by specifying the --lock-wait-timeout parameter.
  • During an export, gs_dumpall reads tables in all databases. Therefore, you need to connect to the databases as a database administrator to export a complete file. When you use gsql to execute scripts, administrator permissions are also required so as to add users and user groups, and create databases.

Preparing for Data Migration

  1. Prepare an ECS or a device that can access the GaussDB instance over EIP.
    • To connect to a GaussDB instance through an ECS, you must first create an ECS.

      For details on how to create and log in to an ECS, see Purchasing an ECS and Logging In to an ECS in Elastic Cloud Server Getting Started.

    • To connect to a GaussDB instance through a device that can access the GaussDB instance over EIP, you must:
      1. Bind an EIP to the GaussDB instance. For details, see Binding an EIP.
      2. Ensure that the local device can access the EIP that has been bound to the GaussDB instance.
  2. Install the gsql client on the prepared ECS or device that can access the GaussDB database, and connect it to the GaussDB database instance. For details, see Using gsql to Connect to a Database.

Exporting a Database

  1. Create the database and table to be exported and insert data into them.
    create database gs_example;
    
    \c gs_example
    password: 
    
    create schema gs_sch_example;
    set search_path to gs_sch_example;
    create table gs_table_example
    (
     col_1 integer,
     col_2 text,
     col_3 varchar(12),
     col_4 date,
     col_5 time
    );
    insert into gs_table_example values(1,'iamtext','iamvarchar','2006-07-07','12:00:00');
    insert into gs_table_example values(2,'sometext','somevarchar','2006-07-07','12:00:00');
    insert into gs_table_example values(3,'sometext','somevarchar','2006-07-07','12:00:00');
    insert into gs_table_example values(4,'sometext','somevarchar','2006-07-07','19:00:02');
    insert into gs_table_example values(5,'sometext','somevarchar','2006-07-07', null);
    insert into gs_table_example values(6,'sometext','somevarchar','2006-07-07','19:00:02');
  2. Use gs_dump to export data of the gs_example database.
    • Example 1: Use gs_dump to export full information of the gs_example database by specifying the database IP address. The exported files are in .sql format.
      gs_dump -U root -f /tmp/data/gs_example_dump.sql -p 8000 gs_example -F p -h 192.*.*.139; 
      Password:
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:04:20]: The total objects number is 458.
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:04:20]: [100.00%] 458 objects have been dumped.
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:04:20]: dump database gs_example successfully
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:04:20]: total time: 8779  ms
    • Example 2: Use gs_dump to export full information of the gs_example database by specifying the database IP address. The exported information is archived to the /tmp/data/gs_example_dump.tar file in .tar format.
      gs_dump -U root -f /tmp/data/gs_example_dump.tar -p 8000 gs_example -F t -h 192.*.*.139; 
      Password:
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 14:58:49]: The total objects number is 458.
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 14:58:49]: [100.00%] 458 objects have been dumped.
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 14:58:49]: dump database gs_example successfully
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 14:58:49]: total time: 8201  ms
    • Example 3: Use gs_dump to export data of the gs_example database by specifying the database IP address. The exported data does not contain object definitions of the database. The exported files are in custom format.
      gs_dump -U root -f /tmp/data/gs_example_dump.dmp -p 8000 gs_example -a -F c -h 192.*.*.139; 
      Password:
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:07:23]: dump database gs_example successfully
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:07:23]: total time: 8369  ms
    • Example 4: Use gs_dump to export all object definitions of the gs_example database by specifying the database IP address. The exported files are in .sql format.
      gs_dump -U root -f /tmp/data/gs_example_dump_s.sql -p 8000 gs_example -s -F p -h 192.*.*.139; 
      Password:
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:09:37]: The total objects number is 457.
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:09:37]: [100.00%] 457 objects have been dumped.
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:09:37]: dump database gs_example successfully
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:09:37]: total time: 8523  ms
    • Example 5: Use gs_dump to export all object definitions of the gs_example database by specifying the database IP address. The exported files are encrypted in .txt format.
      gs_dump -U root -f /tmp/data/gs_example_dump_s_key.sql -p 8000 gs_example --with-encryption AES128 --with-key abcdefg_?1234567 -s -F p -h 192.*.*.139;
      Password:
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:10:38]: The total objects number is 457.
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:10:38]: [100.00%] 457 objects have been dumped.
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:10:38]: dump database gs_example successfully
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:10:38]: total time: 9101  ms
    Table 2 Common parameters

    Parameter

    Description

    Example

    -U

    Username for database connection.

    NOTE:

    If the username for connecting to the database is not specified, the initial system administrator created during installation is used for connection by default.

    -U jack

    -W

    User password for database connection.

    • This parameter is not required for database administrators if the trust policy is used for authentication.
    • If you connect to the database without specifying this parameter and you are not a database administrator, you will be prompted to enter the password.

    -W ********

    -f

    Folder to store exported files. If this parameter is not specified, the exported files are stored in the standard output. If the output format is (-F c/-F d/-F t), the -f parameter must be specified.

    -f /home/omm/backup/MPPDB_backup.tar

    -p

    TCP port or local Unix-domain socket file name extension on which the server is listening for connections.

    -p 8000

    dbname

    Name of the database to be exported.

    testdb

    -F

    Format of exported files. The values are as follows:

    • p: plain-text
    • c: custom
    • d: directory
    • t: TAR

    -F t

Exporting a Schema

  1. Create a schema to be exported and insert data into it.
    create database gs_example;
    
    \c gs_example
    password: 
    
    create schema gs_sch_example;
    create schema gs_sch_1_example;
    create table gs_sch_example.gs_table_example
    (
     col_1 integer,
     col_2 text,
     col_3 varchar(12),
     col_4 date,
     col_5 time
    );
    create table gs_sch_1_example.gs_table_example
    (
     col_1 integer,
     col_2 text,
     col_3 varchar(12),
     col_4 date,
     col_5 time
    );
    insert into gs_sch_example.gs_table_example values(1,'iamtext','iamvarchar','2006-07-07','12:00:00');
    insert into gs_sch_example.gs_table_example values(2,'sometext','somevarchar','2006-07-07','12:00:00');
    insert into gs_sch_example.gs_table_example values(3,'sometext','somevarchar','2006-07-07','12:00:00');
    insert into gs_sch_example.gs_table_example values(4,'sometext','somevarchar','2006-07-07','19:00:02');
    insert into gs_sch_example.gs_table_example values(5,'sometext','somevarchar','2006-07-07', null);
    insert into gs_sch_example.gs_table_example values(6,'sometext','somevarchar','2006-07-07','19:00:02');
    insert into gs_sch_1_example.gs_table_example values(7,'iamtext','iamvarchar','2006-07-07','12:00:00');
    insert into gs_sch_1_example.gs_table_example values(8,'sometext','somevarchar','2006-07-07','12:00:00');
    insert into gs_sch_1_example.gs_table_example values(9,'sometext','somevarchar','2006-07-07','12:00:00');
    insert into gs_sch_1_example.gs_table_example values(10,'sometext','somevarchar','2006-07-07','19:00:02');
    insert into gs_sch_1_example.gs_table_example values(11,'sometext','somevarchar','2006-07-07', null);
    insert into gs_sch_1_example.gs_table_example values(12,'sometext','somevarchar','2006-07-07','19:00:02');
  2. Use gs_dump to export schemas from the gs_example database at the same time.
    • Example 1: Use gs_dump to export the gs_sch_example and gs_sch_1_example schemas at the same time by specifying the database IP address. The exported files are in directory format.
      gs_dump -U root -f /tmp/data/gs_sch_dump -p 8000 gs_example -n gs_sch_example -n gs_sch_1_example -F d -h 192.*.*.139;
      Password:
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:37:11]: The total objects number is 460.
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:37:11]: [100.00%] 460 objects have been dumped.
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:37:11]: dump schema gs_sch_example gs_sch_1_example successfully
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:37:11]: dump database gs_example successfully
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:37:11]: total time: 9602  ms
    • Example 2: Use gs_dump to export full information of the gs_sch_example schema by specifying the database IP address. The exported files are in .txt format.
      gs_dump -U root -f /tmp/data/gs_sch_dump.sql -p 8000 gs_example -n gs_sch_example -F p -h 192.*.*.139;
      Password:
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:39:00]: The total objects number is 457.
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:39:00]: [100.00%] 457 objects have been dumped.
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:39:00]: dump schema gs_sch_example successfully
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:39:00]: dump database gs_example successfully
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:39:00]: total time: 8582  ms
    • Example 3: Use gs_dump to export data from the gs_example database by specifying the database IP address. The exported data does not contain the gs_sch_example schemas. The exported files are in custom format.
      gs_dump -U root -f /tmp/data/gs_sch_dump.dmp -p 8000 gs_example -N gs_sch_example -F c -h 192.*.*.139;
      Password:
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:41:14]: The total objects number is 458.
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:41:14]: [100.00%] 458 objects have been dumped.
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:41:14]: dump database gs_example successfully
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:41:14]: total time: 8323  ms
    Table 3 Common parameters

    Parameter

    Description

    Example

    -U

    Username for database connection.

    -U jack

    -W

    User password for database connection.

    • This parameter is not required for database administrators if the trust policy is used for authentication.
    • If you connect to the database without specifying this parameter and you are not a database administrator, you will be prompted to enter the password.

    -W ********

    -f

    Folder to store exported files. If this parameter is not specified, the exported files are stored in the standard output.

    -f /home/omm/backup/MPPDB_schema_backup

    -p

    TCP port or local Unix-domain socket file name extension on which the server is listening for connections.

    -p 8000

    dbname

    Name of the database to be exported.

    human_resource

    -n

    Names of schemas to be exported. This option contains the schema and all its contained objects.

    • Single schema: Enter -n schemaname.
    • Multiple schemas: Enter -n schemaname for each schema.
    • Single schemas: -n hr
    • Multiple schemas: -n hr -n public

    -F

    Format of exported files. The values are as follows:

    • p: plain-text
    • c: custom
    • d: directory
    • t: TAR

    -F d

Exporting a Table

  1. Create a schema to be exported and insert data into it.
    create database gs_example;
    
    \c gs_example
    password: 
    
    create schema gs_sch_example;
    create table gs_sch_example.gs_table_example
    (
     col_1 integer,
     col_2 text,
     col_3 varchar(12),
     col_4 date,
     col_5 time
    );
    create table gs_sch_example.gs_table_example_2
    (
     col_1 integer,
     col_2 text,
     col_3 varchar(12),
     col_4 date,
     col_5 time
    );
    insert into gs_sch_example.gs_table_example values(1,'iamtext','iamvarchar','2006-07-07','12:00:00');
    insert into gs_sch_example.gs_table_example values(2,'sometext','somevarchar','2006-07-07','12:00:00');
    insert into gs_sch_example.gs_table_example values(3,'sometext','somevarchar','2006-07-07','12:00:00');
    insert into gs_sch_example.gs_table_example values(4,'sometext','somevarchar','2006-07-07','19:00:02');
    insert into gs_sch_example.gs_table_example values(5,'sometext','somevarchar','2006-07-07', null);
    insert into gs_sch_example.gs_table_example values(6,'sometext','somevarchar','2006-07-07','19:00:02');
    insert into gs_sch_example.gs_table_example_2 values(7,'iamtext','iamvarchar','2006-07-07','12:00:00');
    insert into gs_sch_example.gs_table_example_2 values(8,'sometext','somevarchar','2006-07-07','12:00:00');
    insert into gs_sch_example.gs_table_example_2 values(9,'sometext','somevarchar','2006-07-07','12:00:00');
    insert into gs_sch_example.gs_table_example_2 values(10,'sometext','somevarchar','2006-07-07','19:00:02');
    insert into gs_sch_example.gs_table_example_2 values(11,'sometext','somevarchar','2006-07-07', null);
    insert into gs_sch_example.gs_table_example_2 values(12,'sometext','somevarchar','2006-07-07','19:00:02');
  2. Use gs_dump to export the gs_sch_example.gs_table_example and gs_sch_example.gs_table_example_2 tables at the same time.
    NOTE:
    1. In the following example, after the export, ensure that the schema to which the exported table belongs exists before the import.
    • Example 1: Use gs_dump to export the gs_sch_example.gs_table_example and gs_sch_example.gs_table_example_2 tables at the same time by specifying the database IP address. The exported files are in directory format.
      gs_dump -U root -f /tmp/data/gs_table_dump -p 8000 gs_example -t gs_sch_example.gs_table_example -t gs_sch_example.gs_table_example_2 -F d -h 192.*.*.139;
      Password:
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:49:06]: The total objects number is 458.
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:49:06]: [100.00%] 458 objects have been dumped.
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:49:06]: dump table gs_sch_example.gs_table_example gs_sch_example.gs_table_example_2 successfully
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:49:06]: dump database gs_example successfully
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:49:06]: total time: 7694  ms
    • Example 2: Use gs_dump to export the tables excluding the gs_sch_example.gs_table_example_2 table by specifying the database IP address. The exported files are in custom format.
      gs_dump -U root -f /tmp/data/gs_table_dump.dmp -p 8000 gs_example -T gs_sch_example.gs_table_example_2 -F c -h 192.*.*.139;
      Password:
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:52:07]: The total objects number is 461.
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:52:07]: [100.00%] 461 objects have been dumped.
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:52:07]: dump database gs_example successfully
      gs_dump[user='root'][localhost][port='8000'][gs_example][2024-07-26 15:52:07]: total time: 8203  ms
    Table 4 Common parameters

    Parameter

    Description

    Example

    -U

    Username for database connection.

    -U jack

    -W

    User password for database connection.

    • This parameter is not required for database administrators if the trust policy is used for authentication.
    • If you connect to the database without specifying this parameter and you are not a database administrator, you will be prompted to enter the password.

    -W ********

    -f

    Folder to store exported files. If this parameter is not specified, the exported files are stored in the standard output.

    -f /home/omm/backup/MPPDB_table_backup

    -p

    TCP port or local Unix-domain socket file name extension on which the server is listening for connections.

    -p 8000

    dbname

    Name of the database to be exported.

    human_resource

    -t

    Tables (or views, sequences, foreign tables) to export. You can specify multiple tables by listing them or using wildcard characters. When you use wildcard characters, quote the pattern to prevent the shell from expanding the wildcard characters.

    • Single table: Enter -t schema.table.
    • Multiple tables: Enter -t schema.table for each table.
    • Single table: -t hr.staffs
    • Multiple tables: -t hr.staffs -t hr.employments

    -F

    Format of exported files. The values are as follows:

    • p: plain-text
    • c: custom
    • d: directory
    • t: TAR

    -F d

    -T

    A list of tables, views, sequences, or foreign tables not to be dumped. You can use multiple -t parameters or wildcard characters to specify tables.

    When -t and -T are input, the object will be stored in -t list not -T table object.

    -T table1

Exporting All Databases

Use gs_dumpall to export all database information at a time.
  • Example 1: Use gs_dumpall to export all database information by specifying the database IP address. The exported file is in .sql format. After the command is executed, a large amount of output information will be displayed. total time will be displayed at the end of the information, indicating that the command is executed successfully. In this example, only relative output information is included.
    gs_dumpall -U root -f /tmp/data/dumpall.sql -p 8000 -h 192.*.*.139;
    Password:
    gs_dumpall[user='root'][localhost][port='8000'][2024-07-26 16:02:15]: dumpall operation successful
    gs_dumpall[user='root'][localhost][port='8000'][2024-07-26 16:02:15]: total time: 35133  ms
  • Example 2: Use gs_dumpall to export all database definitions by specifying the database IP address. The exported file is in .sql format. After the command is executed, a large amount of output information will be displayed. total time will be displayed at the end of the information, indicating that the command is executed successfully. In this example, only relative output information is included.
    gs_dumpall -U root -f /tmp/data/dumpall_def.sql -p 8000 -s -h 192.*.*.139;
    Password:
    gs_dumpall[user='root'][localhost][port='8000'][2024-07-26 16:07:50]: dumpall operation successful
    gs_dumpall[user='root'][localhost][port='8000'][2024-07-26 16:07:50]: total time: 21239  ms
Table 5 Common parameters

Parameter

Description

Example

-U

Username for database connection. The user must be a database administrator.

-U omm

-W

User password for database connection.

  • This parameter is not required for database administrators if the trust policy is used for authentication.
  • If you connect to the database without specifying this parameter and you are not a database administrator, you will be prompted to enter the password.

-W ********

-f

Folder to store exported files. If this parameter is not specified, the exported files are stored in the standard output.

-f /home/omm/backup/MPPDB_backup.sql

-p

TCP port or local Unix-domain socket file name extension on which the server is listening for connections.

-p 8000

Exporting Global Objects

Use gs_dumpall to export tablespace object information.
  • Example 1: Use gs_dumpall to export the global tablespace and user information of all databases by specifying the database IP address. The exported files are in .sql format. In this example, only relative output information is included.
    gs_dumpall -U root -f /tmp/data/dumpall_tablespace.sql -p 8000 -t -h 192.*.*.139;
    Password:
    gs_dumpall[user='root'][localhost][port='8000'][2024-07-26 16:10:42]: dumpall operation successful
    gs_dumpall[user='root'][localhost][port='8000'][2024-07-26 16:10:42]: total time: 1800  ms
  • Example 2: Use gs_dumpall to export the global user information of all databases by specifying the database IP address. The exported files are in .txt format. In this example, only relative output information is included.
    gs_dumpall -U root -f /tmp/data/dumpall_user.sql -p 8000 -r -h 192.*.*.139;
    Password: 
    gs_dumpall[user='root'][localhost][port='8000'][2024-07-26 16:12:15]: dumpall operation successful
    gs_dumpall[user='root'][localhost][port='8000'][2024-07-26 16:12:15]: total time: 1269  ms
Table 6 Common parameters

Parameter

Description

Example

-U

Username for database connection. The user must be a database administrator.

-U omm

-W

User password for database connection.

  • This parameter is not required for database administrators if the trust policy is used for authentication.
  • If you connect to the database without specifying this parameter and you are not a database administrator, you will be prompted to enter the password.

-W ********

-f

Folder to store exported files. If this parameter is not specified, the exported files are stored in the standard output.

-f /home/omm/backup/MPPDB_tablespace.sql

-p

TCP port or local Unix-domain socket file name extension on which the server is listening for connections.

-p 8000

-t

Dumping only tablespaces. You can also use --tablespaces-only alternatively.

-t

Sitemizi ve deneyiminizi iyileştirmek için çerezleri kullanırız. Sitemizde tarama yapmaya devam ederek çerez politikamızı kabul etmiş olursunuz. Daha fazla bilgi edinin

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback