On 11/3/22 08:38, Post Gresql 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.
The -v to pg_restore should output the progress:
https://www.postgresql.org/docs/current/app-pgrestore.html
-v
--verbose
Specifies verbose mode. This will cause pg_restore to output
detailed object comments and start/stop times to the output file, and
progress messages to standard error. Repeating the option causes
additional debug-level messages to appear on standard error.
Something like:
pg_dump -d test -U postgres -Fc -n test -f test_sch.out
pg_restore -d t -U postgres -v test_sch.out
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA "test"
pg_restore: creating TABLE "test.c2"
pg_restore: creating TABLE "test.supplies"
pg_restore: creating SEQUENCE "test.supplies_id_seq"
pg_restore: creating SEQUENCE OWNED BY "test.supplies_id_seq"
pg_restore: creating TABLE "test.up_test"
pg_restore: creating DEFAULT "test.supplies id"
pg_restore: processing data for table "test.c2"
pg_restore: processing data for table "test.supplies"
pg_restore: processing data for table "test.up_test"
pg_restore: executing SEQUENCE SET supplies_id_seq
pg_restore: creating CONSTRAINT "test.c2 pk1"
pg_restore: creating CONSTRAINT "test.supplies supplies_pkey"
No, no mention of creating the schema.
As you see the schema was created first.
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>
Well the schema should have already been created.
Assuming this:
pg_restore: connecting to database for restore
pg_restore: creating FUNCTION "my_schema.update_b()"
was not the result of an edit then something is not working correctly.
What is my_schema.update_b() doing?
in the dump file does not implicitly create the schema as a
create table <schema>.<table name>
would?
The only workaround I can think of is to dump in plain text and then
edit the dump file.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
