How to backup and restore PostgreSQL database

How to backup and restore PostgreSQL database

1. How to backup PostgreSQL database

If you have already used MySQL, for sure you have been familiar with the powerful  mysqldump backup tool. PostgreSQL  has a similar tool, the pg_dump utility

$pg_dump -h localhost -U postgres -d [db_name] > /tmp/dump.sql

In which [db-name] is the name of the database you want to backup (note: there is NO square bracket)

2. How to restore PostgresSQL database

To restore a PostgreSQL database from backup file, You can use the commandline utility provide with PostgreSQL installation: psql (very similar to mysql tool , isn't it?)

$psql -h localhost -p 5432 -d [db_name] -U postgres < /tmp/template.sql

Note that [db_name] is the database that has been already created, if not, you can create a new database using createdb

$createdb -p 5432 -h localhost -E UTF8 -U postgres [db_name]