Re: BUG #18392: Can't restore database (using pg_restore) after latest Feb 8 update

Sure, here is the full console output.

If you’d like a copy of the database, I’m happy to send it via a non-public channel. (I can share a Dropbox link if you give me a private address to send it to)

Thanks,
Carl

Command failed: pg_restore -U postgres --clean --if-exists --dbname tmf_app_manager /Users/carl/GitHub/conforma/conforma-server/database/_snapshots/TEST_14_11_2024-03-14_15-27-40/database.dump
pg_restore: error: could not execute query: ERROR: relation "public.template" does not exist
LINE 5: public.template
^
QUERY:
SELECT
template.code
FROM
public.template
JOIN public.template_section ON template_id = template.id
WHERE
template_section.id = $1;

CONTEXT: SQL function "get_template_code" during inlining
Command was: CREATE TABLE public.template_element (
id integer NOT NULL,
section_id integer NOT NULL,
code character varying NOT NULL,
index integer,
title character varying,
category public.template_element_category,
element_type_plugin_code character varying,
visibility_condition jsonb DEFAULT 'true'::jsonb,
is_required jsonb DEFAULT 'true'::jsonb,
is_editable jsonb DEFAULT 'true'::jsonb,
validation jsonb DEFAULT 'true'::jsonb,
initial_value jsonb,
validation_message character varying,
help_text character varying,
parameters jsonb,
reviewability public.reviewability DEFAULT 'ONLY_IF_APPLICANT_ANSWER'::public.reviewability NOT NULL,
template_code character varying GENERATED ALWAYS AS (public.get_template_code(section_id)) STORED,
template_version character varying GENERATED ALWAYS AS (public.get_template_version(section_id)) STORED
);

pg_restore: error: could not execute query: ERROR: relation "public.template_element" does not exist
Command was: ALTER TABLE public.template_element OWNER TO postgres;

pg_restore: error: could not execute query: ERROR: type public.template_element does not exist
Command was: CREATE FUNCTION public.template_element_parameters_string(template_element public.template_element) RETURNS text
LANGUAGE sql STABLE
AS $_$
SELECT
parameters::text
FROM
public.template_element
WHERE
id = $1.id
$_$;

pg_restore: error: could not execute query: ERROR: type "public.template_element" does not exist
Command was: ALTER FUNCTION public.template_element_parameters_string(template_element public.template_element) OWNER TO postgres;

pg_restore: error: could not execute query: ERROR: relation "public.template_element" does not exist
Command was: ALTER SEQUENCE public.template_element_id_seq OWNED BY public.template_element.id;

pg_restore: error: could not execute query: ERROR: relation "public.template_element" does not exist
Command was: ALTER TABLE ONLY public.template_element ALTER COLUMN id SET DEFAULT nextval('public.template_element_id_seq'::regclass);

pg_restore: error: could not execute query: ERROR: relation "public.template_element" does not exist
Command was: COPY public.template_element (id, section_id, code, index, title, category, element_type_plugin_code, visibility_condition, is_required, is_editable, validation, initial_value, validation_message, help_text, parameters, reviewability) FROM stdin;
pg_restore: error: could not execute query: ERROR: relation "public.template_element" does not exist
Command was: ALTER TABLE ONLY public.template_element
ADD CONSTRAINT template_element_pkey PRIMARY KEY (id);

pg_restore: error: could not execute query: ERROR: relation "public.template_element" does not exist
Command was: ALTER TABLE ONLY public.template_element
ADD CONSTRAINT template_element_template_code_code_template_version_key UNIQUE (template_code, code, template_version);

pg_restore: error: could not execute query: ERROR: relation "public.template_element" does not exist
Command was: CREATE INDEX i_template_element_category ON public.template_element USING btree (category);

pg_restore: error: could not execute query: ERROR: relation "public.template_element" does not exist
Command was: CREATE INDEX i_template_element_code ON public.template_element USING btree (code);

pg_restore: error: could not execute query: ERROR: relation "public.template_element" does not exist
Command was: CREATE INDEX i_template_element_reviewability ON public.template_element USING btree (reviewability);

pg_restore: error: could not execute query: ERROR: relation "public.template_element" does not exist
Command was: CREATE INDEX i_template_element_section_id_fkey ON public.template_element USING btree (section_id);

pg_restore: error: could not execute query: ERROR: relation "public.template_element" does not exist
Command was: CREATE INDEX i_template_element_template_code ON public.template_element USING btree (template_code);

pg_restore: error: could not execute query: ERROR: relation "public.template_element" does not exist
Command was: ALTER TABLE ONLY public.application_response
ADD CONSTRAINT application_response_template_element_id_fkey FOREIGN KEY (template_element_id) REFERENCES public.template_element(id) ON DELETE CASCADE;

pg_restore: error: could not execute query: ERROR: relation "public.template_element" does not exist
Command was: ALTER TABLE ONLY public.review_response
ADD CONSTRAINT review_response_template_element_id_fkey FOREIGN KEY (template_element_id) REFERENCES public.template_element(id) ON DELETE CASCADE;

pg_restore: error: could not execute query: ERROR: relation "public.template_element" does not exist
Command was: ALTER TABLE ONLY public.template_element
ADD CONSTRAINT template_element_section_id_fkey FOREIGN KEY (section_id) REFERENCES public.template_section(id) ON DELETE CASCADE;

pg_restore: error: could not execute query: ERROR: relation "public.template_element" does not exist
Command was: GRANT ALL ON TABLE public.template_element TO graphile_user;

pg_restore: warning: errors ignored on restore: 18

> On 14 Mar 2024, at 6:58 PM, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
>
> On Thu, 2024-03-14 at 03:06 +0000, PG Bug reporting form wrote:
>> PostgreSQL version: 14.11
>> Operating system: MacOS
>>
>> We regularly save snapshots of our app state using pg_dump and restore them
>> with pg_restore. However, after the latest update (12.18 etc), we can no
>> longer use pg_restore to restore the database -- it just throws a bunch of
>> errors, mainly to do with tables not existing (that should exist).
>
> Please don't keep us guessing and show us the errors. The first couple of
> error messages will be enough, the remainder is often a consequence of those.
>
> Yours,
> Laurenz Albe

> On 14 Mar 2024, at 6:58 PM, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
>
> On Thu, 2024-03-14 at 03:06 +0000, PG Bug reporting form wrote:
>> PostgreSQL version: 14.11
>> Operating system: MacOS
>>
>> We regularly save snapshots of our app state using pg_dump and restore them
>> with pg_restore. However, after the latest update (12.18 etc), we can no
>> longer use pg_restore to restore the database -- it just throws a bunch of
>> errors, mainly to do with tables not existing (that should exist).
>
> Please don't keep us guessing and show us the errors. The first couple of
> error messages will be enough, the remainder is often a consequence of those.
>
> Yours,
> Laurenz Albe