I have a dump.sql file that I want to restore to a local database on my machine. However, when I try to import it, I get an error saying all schemas, functions, relations, constraints, roles are already exist, even though my database has no data or tables initially. Also show duplicate key value violates unique constraint errors.
I’ve checked that the database is empty before importing. Does anyone know why this might be happening or how to resolve it?
I try this code for re store my db.
psql -U <username> -d <db name> -f <dump file path>
2
Answers
Considering only the information you shared, it seems that your dump file has not only data but also the DDL to create your tables and other objects. So, if your current database already contains those objects, you shall want to take a different approach in order to restore your data.
As a first alternative, you may first create another database, then you can restore the dump file into it.
As a second alternative, you may want to edit the dump file and simply remove the section where the tables are created. But although it can fix the messages above already existing objects, it certainly will not get rid of errors related to constraint conflicts. These errors suggest that your database either is not empty or your dump file does not contain all needed objects, just to mention a few examples.
Create new database and try restore dump.sql file in that database or import data in new database.