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?

Disengagement

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

Fogy

Usually better to drop and re-create the database. That way you know you dropped data types, schemas, and anything else. – Currant

yes - OP did not show us dbname, so either he hides it, or have in env, or works in postgres. I did not dare to offer him dropping db :) – Fogy

So what things should I delete from the database before restoring? Should I delete the "tables" , "views", "schemas" or else what? And if I have to drop all the tables from the database, then how can I do it in a single query without writing the name of all the tables, since it is a huge database containing 107 tables? – Disengagement

please quote exact command you use to make a backup and mention the dbname you use. the easiest and clean way - drop db and recreate it from backup – Fogy

"filename=bc_"$(date +\%s)".sql – Disengagement

"filename=bc_"$(date +\%s)".sql " and "pg_dump au > $filename" is what I am using write now to create the backup file, the name of the database is "au". And for taking the backup, I am using "psql au < bc_1493202909.sql " where "bc_1493202909.sql " is the backup file which was created. If I use drop db , will it not drop the whole database? N for restoring we have will have to create one new database, isn't it? – Disengagement

Answer 2

Take the dump with -c option: pg_dump -c database_name > backup.sql. See pg_dump docs.

Sofer