Export and import data
YugabyteDB comes with two utilities – ysql_dump
and ysql_dumpall
– that provide functionality to export the data into a SQL script. These utilities derive from PostgreSQL pg_dump
and pg_dumpall
.
In general, you should use other ways to take backups, such as the distributed backup and recovery capability. However, it makes sense to 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 YSQL dump tools are thread-safe, so they will always give you a consistent version of the database even if run concurrently with other applications that read and update the data.
Export a single database
To export a single database with all its tables, indexes, and so on, use the ysql_dump
utility.
Note
ysql_dump
does NOT export global entities like users, roles, permissions, etc. Use the ysql_dumpall
utility (described below) if you want to include those.
To do the export, run the following command:
$ ./postgres/bin/ysql_dump -d <db-name> > <file>
Where:
<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
For more details, and a list of all available options, see the ysql_dump
reference.
Export all databases
To export all databases, along with the global entities (users, roles, permissions, and so on), use the ysql_dumpall
utility.
To do the export, run the following command:
$ ./postgres/bin/ysql_dumpall > <file>
Where <file>
is the path to the resulting SQL script file.
Two of the script's common command-line options are:
--roles-only
exports just the roles.--schema-only
exports all database objects, without the data.
For more details, and a list of all available options, see the ysql_dumpall
reference.
Import
You can import schemas and objects into YugabyteDB from an SQL script. You can create such a script using the ysql_dump
or ysql_dumpall
tool as described above, or obtain one from an external database that supports PostgreSQL syntax.
To do the import, use the ysqlsh
command line tool:
$ ./bin/ysqlsh -f <sql-script>
Where <sql-script>
is the path to the SQL script to be imported.
You can also use the \i
command in the ysqlsh
shell to import an SQL script:
yugabyte=# \i <sql-script>