I have created two different dump files one without specifying the schema, another specifying the public
schema
without specifying the public schema'
pg_dump -h IP_ADDRESS -p 5432 -U my_user -Fc my_db > my_db_allschema.dump
and the pg_dump statement when specifying the public schema
pg_dump -h IP_ADDRESS -p 5432 -U my_user -Fc my_db -n public > my_db_publicschema.dump
When using pg_restore to restore the dump files, I get errors with the dump file that was generated when specifying the public schema.
postgres@debian:~$ pg_restore -h localhost -p 5432 -U my_user -d my_db my_db_publicschemaonly.dump
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 8; 2615 2200 SCHEMA public postgres
pg_restore: error: could not execute query: ERROR: schema "public" already exists
Command was: CREATE SCHEMA public;
pg_restore: from TOC entry 212; 1259 18102 TABLE abandoned_url real_estate
pg_restore: error: could not execute query: ERROR: function public.gen_random_uuid() does not exist
LINE 2: id uuid DEFAULT public.gen_random_uuid() NOT NULL
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Command was: CREATE TABLE public.abandoned_url (
id uuid DEFAULT public.gen_random_uuid() NOT NULL
);
Looking at this statement that throws an error
CREATE TABLE public.abandoned_url (
id uuid DEFAULT public.gen_random_uuid() NOT NULL
);
The reason it's throwing an error is because pg_dump has put public
before gen_random_uuid()
, the following statement works fine when removing public before gen_random_uuid()
CREATE TABLE public.abandoned_url (
id uuid DEFAULT gen_random_uuid() NOT NULL
);
Am I creating the dump file incorrectly? Could this be a bug in pg_dump?
gen_random_uuid()
is a built-in function since PostgreSQL 13, so it lives inpg_catalog
not inpublic
. But if you use an older version, maybe you have a custom functionpublic.gen_random_uuid
in your source database. Can you clarify that in your question?