# Database Migration

You can migrate your existing databases over to DBaaS using the `pg_dump`, `pg_restore` and `psql` tools.

### Use the SQL script format

To dump a database use the following command:

```bash
pg_dump -U <username> -h <host> -p <port> -t <tablename> <databasename> -f dump.sql
```

The `-t <tablename>` flag is optional and can be added if you only want to dump a single table.

This command will create a script file containing all instructions to recreate your database. It is the most portable format available. To restore it, simply feed it into `psql`. The database to restore to has to already exist.

```bash
psql -U <username> -h <host> -p <port> -d <databasename> -f dump.sql
```

### Use the custom format

`pg_dump` can also be used to dump a database in an archived format. Archived formats cannot be restored using `psql`, instead `pg_restore` is used. This has some advantages like smaller filesize and bandwith savings, but also speedups in the restore process by restoring concurrently.

In this guide we will use the "custom" format. It is compressed by default and provides the most flexible restore options.

```bash
pg_dump -U <username> -h <host> -p <port> -F c <databasename> -f dump
```

The flag `-F c` is selecting the custom archive format. For more information, refer to the [<mark style="color:blue;">PostgreSQL Documentation</mark>](https://www.postgresql.org/docs/current/app-pgdump.html).

To restore from a custom format archive you have to use `pg_restore`. The following command assumes that the database to be restored already exists.

```bash
pg_restore -U <username> -h <host> -p <port> -F c -d <databasename> dump
```

When specifying the `-C` parameter, `pg_restore` can be instructed to recreate the database for you. For this to work you will need to specify a database that already exists. This database is used for initially connecting to and creating the new database. In this example we will use the database "postgres", which is the default database that should exist in every PostgreSQL cluster. The name of the database to restore to will be read from the archive.

```bash
pg_restore -U <username> -h <host> -p <port> -F c -C -d postgres dump
```

Large databases can be restored concurrently by adding the `-j <number>` parameter and specifying the number of jobs to run concurrently.

For more information on `pg_restore` refer to the [<mark style="color:blue;">PostgreSQL Documentation</mark>](https://www.postgresql.org/docs/current/app-pgrestore.html).

{% hint style="info" %}
**Note:** The use of `pg_dumpall` is not possible because it requires a superuser role to work correctly. Superuser roles are not obtainable on managed databases.
{% endhint %}