Database-level Import and Export

gs_dump can be used to back up a single database and supports four archive formats. The application scenarios of these archive formats are described in Table 1. You can choose a proper archive format as required.

For details about how to use the gs_dump tool, see "Data Import and Export Tools > gs_dump for Exporting Database Information" in Tool Reference.

**Table 1** Formats of exported files
Format	Value of -F	Description	Suggestion	Import Tool
Plain-text	p	A plain-text script file containing SQL statements and commands. The commands can be executed on gsql, a command line terminal, to rebuild database objects and load table data.	For a small-sized database or the exported SQL file needs to be modified, the plain-text format is recommended.	Before using gsql to restore database objects, you can use a text editor to edit the plain-text export file as needed. For details about how to use the gsql tool, see "Database Connection Tools > gsql for Connecting to a Database > gsql Usage Guide" in Tool Reference.
Custom	c	A binary file that allows the restoration of all or selected database objects from an exported file.	For medium-or large-sized databases, the backup result needs to be exported to a single file. You are advised to customize the archive format.	You can use gs_restore to import database objects from a custom-, directory-, or tar-format archive. For details about how to use the gs_restore tool, see "Data Import and Export Tools > gs_restore for Importing Data" in Tool Reference.
Directory	d	A directory containing directory files of database objects and the data files of tables and BLOBs.	For medium-or large-sized databases, database objects and data files need to be stored and exported in different directories. Therefore, the directory format is recommended.
.tar archive	t	A tar-format archive that allows the restoration of all or selected database objects from an exported file. The tar file cannot be further compressed and has an 8-GB limitation on the size of each single file.	For a small-sized database, you need to export the archive result and pack it. The tar format is recommended.

gs_dump does not back up all global objects (roles, tablespaces, and corresponding permissions) of the database. Therefore, ensure that global objects have been created on the target database or new instance before the restoration. The -g command of gs_dumpall can be used to export global objects and use gsql to import global objects at the target end. For details about how to use the gs_dumpall tool, see "Data Import and Export Tools > gs_dumpall for Exporting All Database Information" in Tool Reference.
gs_dump and gs_restore do not support import and export across database compatibility modes. Ensure that the database compatibility mode and compatibility configuration parameters of the source and target databases are the same. For details about how to query and create a database of a specified compatibility mode, see CREATE DATABASE.
Do not modify the files and contents exported using the -F c/d/t formats. Otherwise, the restoration may fail. If you need to modify or replace the file exported using the -F p format, edit them with caution.
After restoration, you are advised to run ANALYZE on the database to provide useful statistics for the optimizer.

You are advised to run the following command as the initial user or a user with the SYSADMIN permission to back up data. The source database is my_database, and the exported data contains data and object definitions.

-- Plain-text
nohup gs_dump my_database -U root -W ******** -p 8000 -F p -f /data/backup/my_database_backup.sql > /data/backup/my_database_backup.log &
-- Custom
nohup gs_dump my_database -U root -W ******** -p 8000 -F c -f /data/backup/my_database_backup.dmp > /data/backup/my_database_backup.log &
-- Directory
nohup gs_dump my_database -U root -W ******** -p 8000 -F d -f /data/backup/my_database_backup > /data/backup/my_database_backup.log &
-- .tar archive
nohup gs_dump my_database -U root -W ******** -p 8000 -F t -f /data/backup/my_database_backup.tar > /data/backup/my_database_backup.log &

Before restoration, you need to create a target database that has the same attributes as the source database and does not contain any data.

-- Run the following gsql meta-command to view the database attribute information:
\l+
-- Create a target database based on the queried attribute information.
create database my_database2 encoding='xxxxx' LC_COLLATE='xxxxx' LC_CTYPE ='xxxxx' TEMPLATE=xxx DBCOMPATIBILITY 'xxx';

Run the following command as the initial user or a user with the SYSADMIN permissions to restore the database:

-- Plain-text
nohup gsql -d my_database2 -p 8000 -U root -W ******** -f /data/backup/my_database_backup.sql -a > /data/backup/my_database_restore.log &
-- Custom
nohup gs_restore /data/backup/my_database_backup.dmp -d my_database2 -p 8000 -U root -W ******** -F c -v > /data/backup/my_database_restore.log &
-- Directory
nohup gs_restore /data/backup/my_database_backup -d my_database2 -p 8000 -U root -W ******** -F d -v > /data/backup/my_database_restore.log &
-- .tar archive
nohup gs_restore /data/backup/my_database_backup.tar -d my_database2 -p 8000 -U root -W ******** -F t -v > /data/backup/my_database_restore.log &

Parent topic: Best Practices for Import and Export Using Tools

Previous topic: Instance-level Import and Export

Next topic: Schema-level Import and Export