Description

This PR adds a new admin-only API for getting schema dumps from the connected Postgres instance.

POST /v1alpha1/pg_dump
Content-Type: application/json

{
  "opts": ["-O", "-x", "--schema", "public", "--schema-only"],
  "clean_output": true
}

response:

HTTP/1.1 200 OK
Content-Type: application/sql

CREATE TABLE public.author (
    id integer NOT NULL,
    name text NOT NULL
);
CREATE SEQUENCE public.author_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE public.author_id_seq OWNED BY public.author.id;
ALTER TABLE ONLY public.author ALTER COLUMN id SET DEFAULT nextval('public.author_id_seq'::regclass);

If there is an error, the response will be JSON with the right error code and error messages.

Since this is an API executing a shell script on the server, an option is provided to enable/disable the API. By default, the API is enabled. To disable, use the --enabled-apis by excluding pgdump out of the default value graphql,metadata,pgdump.

Affected components

• Server
• Build System
• Tests

Related Issues

close #1939

Solution and Design

A new admin-only POST endpoint which takes schema as a JSON key. The handler executes a shell script (https://github.com/shahidhk/graphql-engine/blob/pg-dump-on-server/server/src-rsr/run_pg_dump.sh) which executes pg_dump and writes the output to file. This file is then read and sent to the client in the response body.

The clean: true argument will clean the output to remove:

1. SQL front matter, like SET statements.
2. CREATE SCHEMA public.
3. COMMENT ON SCHMEA public is 'standard public schema';
4. Comments (--) and empty newlines.
5. Postgres triggers created by Hasura for event triggers.

Steps to test and verify

curl -d'{"opts": ["-O", "-x", "--schema", "public", "--schema-only"],"clean_output": true}' http://localhost:8080/v1alpha1/pg_dump

Limitations, known bugs & workarounds

The pg_dump binary is not yet included in the docker image, the docker image will not work.

Tests are pending.

NA