I want to take a pg_dump of my data from HasuraDB to my local machine. What is the recommended way to do this ?
2
-
Please could you add details on what resources you have already looked at.F_SO_K– F_SO_K2018-02-07 10:31:59 +00:00Commented Feb 7, 2018 at 10:31
-
1Please take some time to read the help page, especially the sections named "What topics can I ask about here?" and "What types of questions should I avoid asking?". And more importantly, please read the Stack Overflow question checklist. You might also want to learn about Minimal, Complete, and Verifiable Examples.Clijsters– Clijsters2018-02-07 10:37:28 +00:00Commented Feb 7, 2018 at 10:37
Add a comment
|
3 Answers
Option 1:
Exec a pg_dump command on your Postgres container:
pg_dump -U username -d dbname > data.sql
In practice, you probably only want the data and not the hasura metadata or schema_migration information. This is a better 'data export' type command:
pg_dump -U username -d dbname --data-only --schema public > data.sql
Option 2:
If Postgres is running as a Docker container:
# Exec into the postgres container
$ docker exec -ti <postgres-id> -- /bin/bash
# Run pg_dump
root@postgres-3391217220-t7bbc:/$ pg_dump -U postgres -d postgres --data-only --schema public > db.sql
^D
# Back on your local machine, copy the dump
$ docker cp <postgres-id>:/db.sql db.sql
1 Comment
user56reinstatemonica8
I get "unknown command "ms" for "hasura" with this - do I need to be running these commands from something like SSH inside the Hasura microservice/host, not locally?
Hasura has a pgdump API command, enabled by default. Here are the docs: https://docs.hasura.io/1.0/graphql/manual/api-reference/pgdump.html
What the docs currently lack is an example. Here's an example I use to dump the Hasura data, but not the schema (which comes from migrations) in order to import it into my locally hosted dev hasura (insert your own hasura-admin-secret, assuming you use one, or replace with other headers as needed):
HASURA_SECRET=som3_Secr3t_Her3
curl -d '{"-Fc", "opts": ["--data-only"]}' -H "x-hasura-admin-secret: $HASURA_SECRET" https://your-host-url.com/v1alpha1/pg_dump > dumpfile
answered Aug 19, 2019 at 8:42
user56reinstatemonica8
33.9k22 gold badges108 silver badges133 bronze badges
4 Comments
Vaibhav Verma
I'm getting a curl: (7) Failed to connect to postgres port 443: Operation timed out error. Any clue why?
ptim
I think the docs have updated slightly since this was posted - there is now some brief example (not a full curl example). Note that the JSON in this example is invalid, and I've had no success passing
-Fc in the opts array (Hasura returns { "path": "$", "error": "internal exception while executing pg_dump", "code": "unexpected" }). Tx, ptimptim
I filed an issue a while back but there's no movement there yet: github.com/hasura/graphql-engine/issues/5671
Lars Blumberg
I also had pgdump fail and removing
-Fc / --format custom fixed it. I assume this is because hasura's postgres client installation is a different version than the postgresql database. A matching major version is required for this option to work.2022: Hasura v.2.1.1
To export data from a postgres/Hasura instance, we need to use Postman or a curl client to make a http POST request against a hidden Hasura endpoint /v1alpha1/pg_dump like so:
http://<your-hasura-host>/v1alpha1/pg_dump
An example curl command:
curl -d '{"opts": ["-a", "-O", "-x", "--inserts", "--exclude-schema=hdb_catalog"], "clean_output": true, "source": "default"}' -H "x-hasura-admin-secret:your-admin-secret-here" http://your-hasura-host/v1alpha1/pg_dump
The above adds a header to specify your Hasura admin secret:
x-hasura-admin-secret:<your admin secret key>
And the -d adds a JSON body with options ("opts") for pg_dump & Hasura's endpoint handler:
{
"opts": ["-a", "-O", "-x", "--inserts", "--exclude-schema=hdb_catalog"],
"clean_output": true,
"source": "default"
}
The pg_dump options ("opts") above are:
-a: data-only, not schemas-O: no ownership statements-x: no access privilege statements--inserts: use SQL statements, not psql commands--exclude-schema=hdb_catalog: no Hasura migration/metadata related records, just our data
The clean_output key/value removes a bunch of SET statements & comments from the dump output such as:
--
-- PostgreSQL database dump
--
-- Dumped from database version 12.4 (Debian 12.4-1.pgdg100+1)
-- Dumped by pg_dump version 13.4 (Debian 13.4-4.pgdg100+1)
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
source specifies the name of the database to dump. Usually this would be default unless you changed it when setting up Hasura.
The output you get should be a single SET statement followed by a bunch of INSERT INTO statements.
This can be copy/pasted directly into Hasura Console's Data > SQL tab and run to import the dumped data.
References/Useful Links
- https://www.postgresql.org/docs/13/app-pgdump.html
- https://hasura.io/docs/latest/graphql/core/api-reference/pgdump.html
- https://hasura.io/docs/latest/graphql/core/migrations/advanced/seed-data-migration.html
Notes
I needed the pg_dump as seed data to use when applying a Hasura migration, from an old instance to a new Hasura instance. But (in my case) the initial migration output by Hasura CLI on the old instance included a bunch of hdb_catalog create schema statements, which would ensure failure when applying the migration onto a new Hasura instance. (The hdb_catalog schema/tables already exist on a new/fresh Hasura install)
To correct this, I needed to reset migrations on the old instance and redo the migration commands (as shown in the aforementioned link). This new migration output didn't include the hdb_catalog schema, and could be applied OK to the new Hasura instance.
I could then just run the pg_dump output in Hasura Console SQL tab/window on the new instance to repopulate my tables.
