1

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.

2

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.

  • Hi. I used pg_dump tinker > tinker.bak command for backup and I found psql dbname < dumpfile for restore. Should I use pg_restore instead? – Dejan Jun 26 at 11:05
  • If you created the dump using a plain text dump (which is the default when using pg_dump) then you can only restore it using psql. If you want to use pg_restore you have to create a "custom format" dump, using pg_dump --format=c ... – a_horse_with_no_name Jun 26 at 11:11
  • sorry I am not sure what is this custom format dump? can you give me an example? thanks – Dejan Jun 26 at 11:13
  • I still cannot understand based on the documentation what is custom format? Can you please explain? " Specify format of the archive. It is not necessary to specify the format, since pg_restore will determine the format automatically. If specified, it can be one of the following: c custom The archive is in the custom format of pg_dump." – Dejan Jun 26 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.