PostgreSQL - Backing up and Restoring a database

Backing up

Backing up a database has to do with taking a snapshot at a particular point in time of the database. This usually consists of generating an sql file that if executed will re-create the database state at the point at which the backup was taken.
This is usually done when we want to move the database to a different server or is usually kept in the event our live database has been compromised. In the event it was compromised, we have the backup which we can use to restore the data.

In PostgreSQL, this is accomplished using the pg_dump command.
The syntax is

pg_dump dbname > dumpfile

where the dump file is usually and sql file.

Command line options

-h is used to specify the host. This is defaulted to localhost or the value for the PGHOST environment variable.
-p is used to specify the port. This is defaulted to PGPORT environment variable or the compiled-in default. In practice this is usually 5432.
-U is used to specify the user. This is defaulted to PGUSER environment variable or the operating system username if the environment variable is not defined.

Restoring

Think of restoring the database as the reverse of backing up the database. This is done when you want to recreate a database based on a snapshot taken.

In PostgreSQL, this is done using the psql command.
The syntax is

psql -X dbname < dumpfile

The command line options are the same as those available when using pg_dump.

It is worth noting that restoring the database does not involve creating the database, so this will need to be done before the command is executed. You can use SQL to do this:

CREATE DATABASE dbname;