[PostgreSQL] Backup & Restore DB

To backup a database, we can use UI program such as pgAdmin, phpPgAdmin or command line tools such as pg_dump.

Following is steps to backup & restore using command line tools.

Export

pg_dump -h host.com -p 5432 -U db_user -d db_name -Fp > exported_file_name.sql

-F is used to specify the format of the output file, which can be one of the following:

  • p – plain-text SQL script
  • c – custom-format archive
  • d – directory-format archive
  • t – tar-format archive

Import

Use psql to restore with plain-text exported file:

psql -h host.com -p 5432 -U db_user -d db_name < exported_file_name.sql

Use pg_restore to restore with other types:

pg_restore -h host.com -p 5432 -U db_user -d db_name -c exported_file_name.bak

If you use pg_restore you have various options available, for example:

  • -c to drop database objects before recreating them,
  • -C to create a database before restoring into it,
  • -e exit if an error has encountered,
  • -F format to specify the format of the archive.

Export all

Use pg_dumpall to export all databases from database server including roles, schema,…

pg_dumpall -h host.com -p -5432 -U db_user > file_all.sql

Copy database

create database new_copy_db template old_db;

Check for more details here.