postgresqlpg-restorepostgresql-15

Object already exists errors while restoring a clean pg_dump

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?

Solution

  • 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