Postgresql database backup

Login as a pgsql user

$ su - pgsql

Get list of database(s) to backup

$ psql -l

Backup database using pg_dump command

pg_dump is a utility for backing up a PostgreSQL database. It dumps only one database at a time. General syntax:

pg_dump databasename > outputfile

To dump a network database:

$ pg_dump network > network.dump.out

To restore a network database:

$ psql -d network -f network.dump.out

OR
$ createdb network

$ psql network

However, in real life you need to compress database:

$ pg_dump network | gzip -c > network.dump.out.gz

To restore database use command:

$ gunzip network.dump.out.gz

$ psql -d network -f network.dump.out

Here is a shell script for same task

#!/bin/bash
DIR=/backup/psql
[ ! $DIR ] && mkdir -p $DIR || :
LIST=$(psql -l | awk '{ print $1}' | grep -vE '^-|^List|^Name|template[0|1]')
for d in $LIST
do
pg_dump $d | gzip -c > $DIR/$d.out.gz
done

Another option is use to pg_dumpall command. As a name suggest it dumps (backs up) each database, and preserves cluster-wide data such as users and groups. You can use it as follows

$ pg_dumpall > all.dbs.out

OR
$ pg_dumpall | gzip -c > all.dbs.out.gz

To restore it use command:

$ psql -f all.dbs.out postgres