Export and import
This page documents the preview version (v2.23). Preview includes features under active development and is for development and testing only. For production, use the stable version (v2024.1). To learn more, see Versioning.
YugabyteDB includes ysql_dump
and ysql_dumpall
utilities that allow you to export data into a SQL script. These utilities derive from PostgreSQL pg_dump
and pg_dumpall
.
In general, it is recommended to use other means for backups, such as distributed backup and recovery. However, you can use ysql_dump
and ysql_dumpall
if you intend to restore the data on a database other than YugabyteDB, or if having data in SQL format is a requirement for other reasons, such as regulations.
Both utilities are thread-safe and always produce a consistent version of the database even if run concurrently with other applications that read and update the data.
For more details and a list of all available options, see the following:
Export a single database
To export a single database with all its tables, indexes, and other local artifacts, use the ysql_dump
utility by executing the following command:
./postgres/bin/ysql_dump -d <db-name> > <file>
- db-name is the name of the database to be exported.
- file is the path to the resulting SQL script file.
For example, to export the mydb
database into a file called mydb-dump.sql
located in the backup
folder, the command would be as follows:
./postgres/bin/ysql_dump -d mydb > backup/mydb-dump.sql
Export a single table
To export a single database table, use the ysql_dump
utility by executing the following command:
./postgres/bin/ysql_dump -t <table-name> <db-name> -f <file>
- table-name is the name of the table to be exported.
- db-name is the name of the database to be exported.
- file is the path to the resulting SQL script file.
For example, to export the mytable
table of the mydb
database into a file called mytable-mydb-dump.sql
located in the backup
folder, the command would be as follows:
./postgres/bin/ysql_dump -t mytable mydb -f backup/mytable-mydb-dump.sql
Export all databases
To export all databases, along with the global artifacts such as users, roles, permissions, and so on, use the ysql_dumpall
utility by executing the following command:
./postgres/bin/ysql_dumpall > <file>
file is the path to the resulting SQL script file.
The following are two of the script's common command-line options:
- --roles-only exports just the roles.
- --schema-only exports all database objects without the data.
Import
You can import schemas and objects into YugabyteDB from a SQL script. To create this script, follow instructions provided in Export a single database or Export all databases. Alternatively, you can obtain the script from an external database that supports PostgreSQL syntax.
To import the script, use the ysqlsh
command line tool, as follows:
./bin/ysqlsh -f <sql_script>
sql_script is the path to the SQL script to be imported.
You can also use the \i
meta-command in the ysqlsh
shell to import a SQL script, as follows:
yugabyte=# \i <sql_script>