Cannot restore Postgresql databases got "database already exists" error

user_2715292 0
Viewed 8362

I have take backup by pg_dumpall > test.out and test.out successfully generated, hence backup completed.

I have used command psql -f test.out postgres for restore But got following errors with restoring backup:

databases already exists
relation "products" already exists
duplicate key value violates unique constraint "products_pkey"

I actually want to replace the data in the existing db with backup. How to do that?

postgresql
3 Answers

The problem is that the database you're trying to restore already exists.

You can run a DROP DATABASE database_name command that will delete your existing database and then you can run your test.out file.

Or you can run pgdumpall --clean > test.out and then run the resulting file. The clean flag will make the resulting files have the DROP DATABASE command in them.

user_29850950

Do you use the bellow command ?

 psql -h localhost -U [login role] database_name -f /home/database.backup
user_90683100

I think a flow like this might help, because we don't want drop the database each time we call the backup file.

First, we need to create a backup file using the --format=custom [-Fc] to restore it using pg_restore. We can use a connection string postgresql://<user>:<pass>@localhost:5432/<dbname> and replace <user>, <pass>, and <dbname> with your information.

pg_dump -v -Fc \
postgresql://<user>:<pass>@localhost:5432/<dbname> \
> db-20211122-163508.sql

To restore we will call it using --clean [-c] and --create [-C] to drop the database before restoring. Replace <user>, <host>, <port>, and <dbname> with your information.

pg_restore -vcC \
-U <user> \
-h <host> \
-p <port> \
-d <dbname> \
< db-20211122-163508.sql

This way you don't need to use clean when you create the backup file.

user_53720080
Related