Export and import

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.

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

For more details and a list of all available options, see ysql_dump reference.

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.

For more details and a list of all available options, see ysql_dumpall reference.

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 command in the ysqlsh shell to import an SQL script, as follows:

yugabyte=# \i <sql_script>