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