How to backup PostgreSQL database using pg_dump, pg_dumpall and psql.

If you are using PostgreSQL in a production environment, it is important to take precautions to ensure that your users’ data is not lost.

By frequently backup your database, and/or automating backups with a cron task,

you will be also able to quickly restore your system in the event that your database is lost or corrupted.

PostgreSQL includes tools to make this task simple and easy to manage.eg. pgadmin, phppgadmin etc.

Single Database SQL Dump.

1. Login into postgres user.

[root@ashish] # su - postgres

2. Dump the content of database.

postgres@ashish:-$  pg_dump dbname > dbname.bak

3. Restoring database

postgres@ashish:-$ psql dbname < dbname.bkp

There are several option available for the backup format:

1. *.bak: compressed binary format
2. *.sql: plaintext dump
3. *.tar: tarball

Remote Database backup.

If you need to back up a remote server add -h (host) and -p (port) options:

postgres@ashish:-$ pg_dump -h host_name -p port_number database_name > database.sql

postgres@ashish:-$ pg_dump -h 198.168.1.1 -p 5432 dbname > dbname.bak

All Database Backup using pg_dumpall

[root@ashish] # pg_dumpall > pg_backup.bak