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?
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.
pgdumpall --clean > test.out is worked for me, thank your @Gregory AreniusDo you use the bellow command ?
psql -h localhost -U [login role] database_name -f /home/database.backup
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.
