Importing an existing PostgreSQL database

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

You will need:

Moving the Schema

  1. 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
  2. Take a schema-only dump of your existing database using pg_dump add add it to the migration


    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


    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.

  3. 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
  4. Apply your schema:

    $ hasura migration apply
  5. Track these tables so that they are accessible by the Hasura APIs. Go to the API Console:

    $ hasura api-console
  6. 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.

Moving the data

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


    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
  2. 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
  3. 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.

Verifying the migration

  1. 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
  2. Go to the API-Explorer section and try making queries.