PostgreSQL - do we need to DROP the database and recreate it if we want to RESTORE it?

Question

I worked with many different databases (SQL Server, Oracle, IBM db2) and for all of them if we have a backup it is just enough to execute RESTORE command in order to restore the data.

I am beginner in PostgreSQL.

As I see in documentation and on this question also https://stackoverflow.com/questions/35289888/restore-postgres-database-dump for succesfull restoration of the database we need to:

* DROP database
* CREATE database
* execute psql dbname < dumpfile

I am really confused (based on the behavior from all other databases ) that we need to drop and recreate database if we want to restore? It's hard for me to believe that Postgres really is implemented in that way.

Answer 1

If you run pg_dump with the --clean option then the generated SQL dump would contain drop statements for all dumped objects (note that when using the custom format and pg_restore you don't need to decide on this when dumping, but when restoring).

However if the target database contains tables (or other objects) that were not contained in the source, those would not be dropped.

Using pg_dump --clean (or pg_restore --clean) is pretty much the same behaviour as with Oracle's impdp using the TABLE_EXISTS_ACTION=REPLACE. That too wouldn't drop (replace) tables that were not part of the source.

To avoid having tables in the target that were not contained in the source, people very often simply drop the entire database before importing the dump. But this is (when using the --clean option) not really required.