Other Commands

pg_restore

We saw earlier that we can use pg_dump to backup our database to an *.sql file, and then use the psql command to restore a database. This is fine if we want to restore an entire database.

If, instead, we want more fine tuned control then we can use pg_restore. With pg_restore, we can specify exactly what we want to restore. We can even reorder the database if we want. But, to use pg_restore, we need a custom archive file.

First, let's try dumping the database and storing information about it to a custom archive file:

$ pg_dump -Fc sql_book > db.dump

Then, to drop the database and recreate it from the dump:

$ dropdb sql_book
$ pg_restore -C -d dbname db.dump

Notice that two options were specified. -C and -d. -C will recreate the database based on the information from the dump file. -d is used to specify a database to run the commands to remake our dropped database. In this case, we are specify dbname as the database to use to issue those commands. dbname is an arbitrary database that already exists. It could be named anything. We are using that database to recreate our sql_book database from the db.dump file. pg_restore is issuing the CREATE DATABASE command behind the scenes to make this happen.

If we didn't want to automatically recreate the sql_book database. We could instead recreate it manually and then use pg_restore to restore the data from the dump file.

$ createdb sql_book
$ pg_restore -d sql_book db.dump

See how we didn't specify the -C option. That is because we have already manually recreated the sql_book database with the createdb command.