Postgresql – pg_restore showing errors when specifying schema when backing up with pg_dump

pg-dumppg-restorepostgresql

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?

Best Answer

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.)

Related Question