How to restore a dump containing CASTs into a database with a new user? - Mailing list pgsql-general

From Thorsten Schöning
Subject How to restore a dump containing CASTs into a database with a new user?
Date
Msg-id 1566481610.20200719202547@am-soft.de
Whole thread Raw
Responses Re: How to restore a dump containing CASTs into a database with a new user?
List pgsql-general
Hi all,

one of my apps and databases uses custom CASTs and is used with the
user "postgres" for historical reasons. I would like to change that to
use a non-superuser for that app+database only. So I dumped the DB
using the C-format and tried to restore into a newly creeated DB:

> createuser --encrypted --pwprompt ams_sm_mtg
> createdb --encoding=UTF-8 --locale=de_DE.UTF-8 --owner=ams_sm_mtg --template=template0 ams_sm_mtg
> pg_restore --dbname=ams_sm_mtg --no-owner --role=ams_sm_mtg < pg_dump/dump.c

Expectation was that whatever gets created in that DB is owned by the
new user. But I'm running into the following error:

> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 2721; 2605 342334 CAST CAST (character varying AS inet)
> pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of type character varying or type inet
>     Command was: CREATE CAST (character varying AS inet) WITH FUNCTION public.fn_cast_inet_from_varchar(character
varying,integer, boolean) AS ASSIGNMENT; 

Most things I've found are related to that topic is about PLPGSQL,
which is not the problem I have (yet?). Somewhere the use of "-n" has
been suggested and restoring succeeds with that, but my CASTs are
missing afterwards. So that is not a solution.

> pg_restore --dbname=ams_sm_mtg --no-owner --role=ams_sm_mtg -n public < pg_dump/dump.c

I'm having trouble to understand the root cause and how things should
work:

Is there some wrong owner in the dump related to anything of the CAST?

Or can the CAST really only be created when the new DB-owner owns
those types? Is that necessary per database then or globally for the
public schema or one of the template databases or ...?

What is the proper way to restore a dump containing arbitrary CASTs? I
would have expected that pg_restore is handling everything, like it
does for all other objects.

Thanks!

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning       E-Mail: Thorsten.Schoening@AM-SoFT.de
AM-SoFT IT-Systeme      http://www.AM-SoFT.de/

Telefon...........05151-  9468- 55
Fax...............05151-  9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow




pgsql-general by date:

Previous
From: "Abraham, Danny"
Date:
Subject: RE: Re: PG 9.5.5 cores on AIX 7.1
Next
From: Tom Lane
Date:
Subject: Re: How to restore a dump containing CASTs into a database with a new user?