0

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?

2
  • 2
    gen_random_uuid() is a built-in function since PostgreSQL 13, so it lives in pg_catalog not in public. But if you use an older version, maybe you have a custom function public.gen_random_uuid in your source database. Can you clarify that in your question? Nov 1, 2020 at 12:28
  • @david, did you ever come up with an answer here. I'm having the same problem when attempting to restore w/ Heroku / PostGreSQL.
    – Jeremy L.
    Aug 9, 2022 at 17:36

1 Answer 1

1

As Postgres does exports with if exists in both commands, but you can make the database be dropped before inserting the backup

use

When you create the dump

-c
--clean

Output commands to clean (drop) database objects prior to outputting the commands for creating them. (Unless --if-exists is also specified, restore might generate some harmless error messages, if any objects were not present in the destination database.)

This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore.

The same exists for the pg_restore, but if you used it in pg_dump you don't need it here

-c
--clean

Clean (drop) database objects before recreating them. (Unless --if-exists is used, this might generate some harmless error messages, if any objects were not present in the destination database.)

1
  • The table does not exist in the database, the issue comes from pg_dumb adding public right before gen_random_uuid() pg_dump is creating id uuid DEFAULT public.gen_random_uuid() NOT NULL instead of id uuid DEFAULT gen_random_uuid() NOT NULL
    – david
    Oct 31, 2020 at 16:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.