On Thu, Nov 3, 2022 at 8:39 AM Post Gresql <postgre...@taljaren.se> wrote:
> > On 2022-11-03 15:43, Adrian Klaver wrote: > > On 11/3/22 07:28, Post Gresql wrote: > >> Hello > >> > >> I first successfully ran > >> > >> pg_dump -U postgres -b -E UTF8 -f my_dump --format=custom -n my_schema > --no-owner -v my_db > >> > >> but then > >> > >> pg_restore --single-transaction -v -U postgres -O -e -d my_other_db > my_dump > >> > >> failed with > >> > >> pg_restore: connecting to database for restore > >> pg_restore: creating FUNCTION "my_schema.update_b()" > >> pg_restore: [archiver (db)] Error while PROCESSING TOC: > >> pg_restore: [archiver (db)] Error from TOC entry 7142; 1255 8618854 > FUNCTION update_b() previous_owner > >> pg_restore: [archiver (db)] could not execute query: ERROR: schema > my_schema" does not exist > >> Command was: CREATE FUNCTION "my_schema".update_b() RETURNS void > >> LANGUAGE plpgsql > >> AS $$ > >> BEGIN > >> PERFORM pg_advisory_xact_lock(1); > >> > >> INSERT INTO ...; > >> END; > >> $$; > >> > >> > >> This is PG version 10.19 > >> > >> Is there a solution for this (apart from upgrading to a newer PG > version)? > > > > Was there another error before this? > > > > Did the -v show the schema my_schema being created? > > > No there were no other output apart from what I showed above. > > No, no mention of creating the schema. > > > Btw. it worked fine when I used plain text output and moved the part of > creating the function to near the end of the dump file. > > Could it be that the > > create function <schema>.<function name> > > in the dump file does not implicitly create the schema as a > > create table <schema>.<table name> > > would? > > I don't know where you got the idea that a schema is implicitly created via create table...a schema is never implicitly created. It is unlikely, though possible (casts were just fixed in 10.20), that you've discovered a dependency tracking bug. At this point you would need to produce and show a self-contained test case to provide further help. The information you have shown so far indeed looks problematic but insufficient to diagnose further. pg_dump -n schema is documented to dump both the schema and its objects so pg_restore should be restoring both, and should be restoring schemas before trying to restore most anything else. You could try playing with pg_restore -L and try to get a sequence that works. David J.
