PostgreSQL ทำ Backup & Restore ด้วย pg_dump

การทำ Backup และ Restore PostgreSQL บน Linuz Server

Step # 1: Login as a pgsql user

Type the following command:
$ su - pgsqlGet list of database(s) to backup:
$ psql -l

Step # 2: Make a backup using pg_dump

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
Task: dump a payroll database
Type the following command
$ pg_dump payroll > payroll.dump.out

To restore a payroll database:
$ psql -d payroll -f payroll.dump.out

OR$ createdb payroll$ psql payroll
However, in real life you need to compress database:
$ pg_dump payroll gzip -c > payroll.dump.out.gz

To restore database use the following command:
$ gunzip payroll.dump.out.gz$ psql -d payroll -f payroll.dump.out

Here is a shell script for same task:

#!/bin/bash
DIR=/backup/psq
#[ ! $DIR ] && mkdir -p $DIR :
[ !$DIR ] && mkdir -p $DIR :
LIST=$(psql -l awk ‘{ print $1}’ grep -vE ‘^-^List^Nametemplate[01]^\(’)
#LIST=$(psql -l awk '{ print $1}' grep -vE '^-^List^Nametemplate[01]')
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 backup use the following command:

$ psql -f all.dbs.out postgres



$pg_dump -U devdba -d devdb -p 5438 -F c -b -v -f devdb.dmp