Exporting a Database

You can use gs_dump to export data and all object definitions of a database from openGauss. You can specify the information to export as follows:

  • Export full information of a database, including its data and all object definitions.

    You can use the exported information to create a database containing the same data as the current one.

  • 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 a database that is the same as the current one, except that the new database does not have data.

  • Export data of a database.

Procedure

  1. Log in as the OS user omm to the primary node of the database.

  2. Use gs_dump to export data of the postgres database.

    gs_dump -U jack -f /home/omm/backup/postgres_backup.tar -p 8000 postgres -F t 
    Password:
    

    Table 1 Common parameters

    Parameter

    Parameter Description

    Example Value

    -U

    Username for database connection.

    NOTE:

    If the username 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 abcd@123

    -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/postgres_backup.tar

    -p

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

    -p 8000

    dbname

    Name of the database to export.

    postgres

    -F

    Select the format of file to export. The values of -F are as follows:

    • p: plaintext
    • c: custom
    • d: directory
    • t: .tar

    -F t

    For details about other parameters, see “Server Tools > gs_dump” in the Tool Reference.

Examples

Example 1: Run gs_dump to export full information of the postgres database and compress the exported files in .sql format.

gs_dump -f /home/omm/backup/postgres_backup.sql -p 8000 postgres -Z 8 -F p
Password:
gs_dump[port='8000'][postgres][2017-07-21 15:36:13]: dump database postgres successfully
gs_dump[port='8000'][postgres][2017-07-21 15:36:13]: total time: 3793  ms

Example 2: Run gs_dump to export data of the postgres database, excluding object definitions. The exported files are in a custom format.

gs_dump -f /home/omm/backup/postgres_data_backup.dmp -p 8000 postgres -a -F c
Password:
gs_dump[port='8000'][postgres][2017-07-21 15:36:13]: dump database postgres successfully
gs_dump[port='8000'][postgres][2017-07-21 15:36:13]: total time: 3793  ms

Example 3: Run gs_dump to export object definitions of the postgres database. The exported files are in .sql format.

gs_dump -f /home/omm/backup/postgres_def_backup.sql -p 8000 postgres -s -F p
Password:
gs_dump[port='8000'][postgres][2017-07-20 15:04:14]: dump database postgres successfully
gs_dump[port='8000'][postgres][2017-07-20 15:04:14]: total time: 472 ms

Example 4: Run gs_dump to export object definitions of the postgres database. The exported files are in text format and are encrypted.

gs_dump -f /home/omm/backup/postgres_def_backup.sql -p 8000 postgres --with-encryption AES128 --with-key 1234567812345678 -s -F p
Password:
gs_dump[port='8000'][postgres][2018-11-14 11:25:18]: dump database postgres successfully
gs_dump[port='8000'][postgres][2018-11-14 11:25:18]: total time: 1161  ms
Feedback
编组 3备份
    openGauss 2024-05-25 00:45:01
    cancel