Restoring the data from pg_dump doesn't overwrite the data but it appends the data to the original database

Question

I am taking the dump of postgres database using "pg_dump database_name > backup.sql". Later on I am doing some modifications in the original database(database_name) and then I am restoring the data from the backup file(backup.sql). But the result is that, the database doesn't gets restored to the original state, instead it adds the original data to the modified data(modified + original).I just want it to restore to the original state, shall i delete all the data from the database before restoring it from the backup file, since it gives the original state of the database. Or is there any other way to do this?

Answer 1

The default format fo pg_dump is plain, so it creates a COPY statement. Hence when you psql backup.sql you just run those copy over existing data. To rewrite data, you should either drop tables first or pg_dump -F c and pg_restore -c.

Warning - in both cases it will destroy old data (this seems what you want though)

-c --clean Clean (drop) database objects before recreating them. (Unless --if-exists is used, this might generate some harmless error messages, if any objects were not present in the destination database.)

As @Craig Ringer suggests, drop/recreate from backup would be much easier and cleaner. To drop database you run DROP DATABASE au - note that there should be no connected users to success. Then you have to create db: CREATE DATABASE au and run psql -f backup.sql -d au