Object already exists errors while restoring a clean pg_dump

Question

I am creating pg dump of a database using following command:

pg_dump mydatabase -Fc -c -U mydatabaseuser -f mydatabase_pgdump_$(date +^CY%m%dT%H%M")

Then i am trying to to restore that dump using following:

pg_restore -d mydatabase -U postgres mydatabase_pgdump_20230422T0540

However, this gives me a lot of object already exists errors, such as:

pg_restore: error: could not execute query: ERROR:  relation "user_token" already exists

The destination database is already there, however I have include -c --clean in my pg_dump command, so shouldn't all the existing object first dropped and then recreated? Or i am missing something?

Answer 1

Some database objects you are restoring might exist, so try dropping them manually before restoring. Something like:

DROP DATABASE db_name

OR

DROP SCHEMA schame_name

might work.

You need to clean it properly & restore it to empty database.

Answer 2

Per the docs:

https://www.postgresql.org/docs/current/app-pgdump.html

-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 ignored when emitting an archive (non-text) output file. For the archive formats, you can specify the option when you call pg_restore.

Note the last section. If you want -c to take effect you will need to add it to the pg_restore as:

pg_restore -c -d mydatabase -U postgres mydatabase_pgdump_20230422T0540