DOCS vx.y

Importing an existing PostgreSQL database¶

This section is about moving your existing Postgres database to Hasura.

You will need:

hasura CLI
pg_dump
pg_restore
A Hasura project. Run the following command to get one:
```
$ hasura quickstart base
```

Moving the Schema¶

Generate an empty Hasura migration. Run the following command from your project directory.
```
$ hasura migration generate data_import
```
This command will generate four migration files in the migrations directory of your Hasura project. They will look like:
- 1524223609768_data_import.down.sql
- 1524223609768_data_import.down.yaml
- 1524223609768_data_import.up.sql
- 1524223609768_data_import.up.yaml
Take a schema-only dump of your existing database using pg_dump add add it to the migration

Important

You have to use the -s tag to take the schema-only pg_dump.

Your pg_dump command will look something like:
```
$ pg_dump -s dbname --host=localhost --port=5432 > schema.dump
```
Note

If the exported file contains SELECT pg_catalog.set_config('search_path', '', false);,remove the whole line. This can cause issues later when SQL is run without schema qualifiers, since this statement sets search path to '' instead of the default public and pg_catalog.
Copy this schema dump content in the migration that has .up.sql in the end (in the above example, 1524223609768_data_import.up.sql).

You can run:
```
$ cat schema.dump > migrations/1524223609768_data_import.up.sql
```
Apply your schema:
```
$ hasura migration apply
```
Track these tables so that they are accessible by the Hasura APIs. Go to the API Console:
```
$ hasura api-console
```
Go to the Data section. You can find your tables under the Untracked Tables. Just click on Add all to track them.

Your schema has been migrated. Lets move on to migrating the data.

Take a data-only dump of your existing database in custom format using pg_dump.

Important

The flags for data-only and custom format are --data-only and -Fc respectively.

Your pg_dump command will look something like:
```
$ pg_dump --data-only -Fc dbname --host=localhost --port=5432 > data.dump
```
Forward your Hasura postgres microservice to your localhost. Run the following command from your project directory to forward it to the 6432 port of your localhost.
```
$ hasura microservice port-forward postgres -n hasura --local-port 6432
```
Use pg_restore on the postgres microservice running at localhost:6432 to restore this data. Make sure to use --disable-triggers flag so that the schema constraints do not interfere with the restoration.
```
$ pg_restore --disable-triggers -U admin -d hasuradb --host=localhost --port=6432
```

That’s it. You can immediately start using the GraphQL or JSON APIs over this newly imported data.

Check if the schema and data has been migrated. Go to the API-Console and go to the Data section on top.
```
$ hasura api-console
```
Go to the API-Explorer section and try making queries.

« previous | next »

Was this page helpful?