Howto Backup PostgreSQL Databases Server With pg_dump command

March 1, 2006in Backup, Data recovery, Howto, Linux, Postgresql, UNIX last updated March 1, 2006

Generally, I like to work with MySQL but some time my work force me to work with PostgreSQL database server.

Recently I had received a request to backup PostgreSQL databases as one of our client want to format and reinstall RHEL server.

PostgreSQL is a one of the robust, open source database server. Like MySQL database server, it provides utilities for creating a backup.

Step # 1: Login as a pgsql user

Type the following command:
$ su - pgsql
Get 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.outTo restore a payroll database:
$ psql -d payroll -f payroll.dump.outOR$ createdb payroll $ psql payrollHowever, in real life you need to compress database:$ pg_dump payroll | gzip -c > payroll.dump.out.gzTo restore database use the following command:$ gunzip payroll.dump.out.gz $ psql -d payroll -f payroll.dump.outHere 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.outOR$ pg_dumpall | gzip -c > all.dbs.out.gzTo restore backup use the following command:
$ psql -f all.dbs.out postgresReferences:

PostgreSQL documentation : Backup and Restore chapter

Posted by: Vivek Gite

The author is the creator of nixCraft and a seasoned sysadmin and a trainer for the Linux operating system/Unix shell scripting. He has worked with global clients and in various industries, including IT, education, defense and space research, and the nonprofit sector. Follow him on Twitter, Facebook, Google+.

29 comment

Nilesh Jain says:
November 3, 2006 at 8:29 am
sir,
i am not able to take the backup of postgresql database.in pg_dump commnad it provide me “access denied ”
thanks
Reply Report comment
Yogesh says:
June 20, 2007 at 7:25 am
can you provide how to do offiline backup of the postgresql database.
Reply Report comment
Daniel says:
April 3, 2008 at 12:37 pm
Man, please remember that if you use the gzip option with pg_dumpall you HAVE TO use the gunzip to restore the backup.
gunzip all.dbs.out.gz
and then
psql -f all.dbs.out postgres
Reply Report comment
Leopold Palomo says:
June 4, 2008 at 7:37 pm
The script has a little bug. On,
LIST=$(psql -l | awk ‘{ print $1}’ | grep -vE ‘^-|^List|^Name|template[0|1]’)
if you have a list with several databases, the last row is number. For example in my case:
(19 rows)
and after the script
(19
so I propose you generate the LIST with this:
LIST=$(psql -l | awk ‘{ print $1}’ | grep -vE ‘^-|^List|^Name|template[0|1]|^(‘)
Otherwise, it’s a very nice article.
Thanks.
Reply Report comment
Stefan says:
June 28, 2008 at 3:37 pm
Thanks Vivek for the great script.
Just a few notes:
In the 3rd line
[ ! $DIR ] && mkdir -p $DIR || :
there should be no space character after the exclamation mark!
[ !$DIR ] && mkdir -p $DIR || :
like this it works for me.
@Leopold, thanks for your comment, I had the same problem and your expression for LIST was the solution 🙂
Reply Report comment
Basavaraj says:
September 20, 2008 at 12:27 pm
i have taken backup of DB from other machine. so i want the run those DB on my box.
what are the steps i need to follow to achive it.
please help me
Reply Report comment
subodh keskar says:
April 5, 2009 at 6:07 am
Needed help,new person in software using POSTGRE SQL please guide me with commands that will help to take backup easily and similarly restore when needed.
Reply Report comment
Joe Sircy says:
July 9, 2009 at 5:06 pm
Nice tip, thanks. I have a few notes to share.
You can also avoid the number of rows returned with the “-t” option to psql:
psql -lt
Also, to run the script as root from cron, I used su to run the backup as the postgres user. My complete script:
```
#!/bin/sh
DIR=/backup/pgsql

LIST=$(su - postgres -c "psql -lt" |awk '{ print $1}' |grep -vE '^-|^List|^Name|template[0|1]')

for d in $LIST
do
  su - postgres -c "/usr/bin/pg_dump $d | gzip -c > $DIR/$d.sql.gz"
done
```
Reply Report comment
Hamidreza Zarei says:
July 26, 2009 at 7:08 am
Hi
I want take backup from Postgresql in Tehran (city in Iran) and store it in Sqlserver 2005 in Mashad (city in Iran). Is it possible? How can I do it?
Regards,
Hamidreza
Reply Report comment
Ben says:
September 11, 2009 at 4:40 pm
Hi. I had a slightly different experience. My cron job runs under root, but wanted to run the psql code under postgres user. My script therefore had to be changed to (Note the PGPASSWORD being set AND removed at the end):
#!/bin/bash
export PGPASSWORD=XXXXX
DIR=/home/ben/backups/psql
[ !$DIR ] && mkdir -p $DIR || :
LIST=$(su postgres -c “psql -lt” |awk ‘{ print $1}’ |grep -vE ‘^-|^List|^Name|template[0|1]’)
for d in $LIST
do
pg_dump -U postgres $d | gzip -c > $DIR/$d.out.gz
done
export PGPASSWORD=
Reply Report comment
Brahma Prakash Tiwari says:
November 6, 2009 at 10:50 am
Hi Denial this script is great for me -Brahma Prakash
Reply Report comment
nihed says:
January 20, 2010 at 10:09 am
Nice,
For my case, I add split to have many files and can store it in dumy filesystem
pg_dumpall -Upostgres | gzip -c | split --bytes=1000m
Regards
Reply Report comment
Tanel says:
March 22, 2010 at 3:17 pm
I got errors like this:
-su: /tmp/pgsql/erpengdemo.sql.gz: Permission denied
and tish:
pg_dump: [archiver (db)] connection to database “:” failed: FATAL: database “:” does not exist
And work around was:
#!/bin/sh
DIR=/backup/pgsql
[ !$DIR ] && mkdir -p $DIR || :
chown postgres:postgres $DIR
LIST=$(su – postgres -c “psql -lt” |awk ‘{ print $1}’ |grep -vE ‘^-|:|^List|^Name|template[0|1]’)
for d in $LIST
do
su – postgres -c “/usr/bin/pg_dump $d | gzip -c > $DIR/$d.sql.gz”
done
Reply Report comment
Tanel says:
March 22, 2010 at 3:18 pm
I got errors like this:
-su: /tmp/pgsql/erpengdemo.sql.gz: Permission denied
and tish:
pg_dump: [archiver (db)] connection to database “:” failed: FATAL: database “:” does not exist
And work around was:
#!/bin/sh DIR=/backup/pgsql [ !$DIR ] && mkdir -p $DIR || :
chown postgres:postgres $DIR
LIST=$(su - postgres -c "psql -lt" |awk '{ print $1}' |grep -vE '^-|:|^List|^Name|template[0|1]')
for d in $LIST do su - postgres -c "/usr/bin/pg_dump $d | gzip -c > $DIR/$d.sql.gz" done
Reply Report comment
Andrew Gudkov says:
April 28, 2010 at 7:52 pm
My version of script. The differencies:
1. I am asking psql to output databases without header (-t)
2. Simplified awk call to get rid of colons
```
#!/bin/bash
DIR=/backup/psql
LIST=$(psql -l -t -U root | awk 'NF >=5 { print $1}')
for d in $LIST; do
  pg_dump $d | gzip -c >  $DIR/$d.out.gz
done
```
Reply Report comment

Even better version 🙂

#!/bin/bash
#
# Dayly PostgreSQL maintainance: vacuuming and backuping.
#
##

set -e

for DB in $(psql -l -t -U root | awk 'NF >=5 { print $1}' | grep -v template0); do
  echo "[`date`] Maintaing $DB"
  echo 'VACUUM' | psql -U root -d $DB
  DUMP="$DB.`date '+%Y%m%d'`.sql.gz"
  PREV="$DB.`date -d'1 day ago' '+%Y%m%d'`.sql.gz"
  pg_dump -U root $DB | gzip -c > $DUMP
  md5sum -b $DUMP > $DUMP.md5
  if [ -f $PREV.md5 ] && diff $PREV.md5 $DUMP.md5; then
    rm $DUMP $DUMP.md5
  fi
done

Reply Report comment

I’ve made some script good for cron… It also has impemented restore commands for created backups 🙂 and expiration limit (rotating)

#!/bin/bash
#
# Little script for PGSQLdump all databases (seperate files)
#
# v20100519 -- initial version by Koco
#
############################################################
MYEXECNAME=backup_postgres_v8.4_dbs_script.bash

USER=postgres
PGSQLCMD="/postgres1/v8.4.0/bin/psql --port 5433"
PGSQLCMDlist="$PGSQLCMD -l -t"
PGSQLdumpCMD="/postgres1/v8.4.0/bin/pg_dump --port 5433 -C"

APPEND=`date "+%Y%m%d-%H%M"`
BASEDIR=/postgres1/BACKUPS
BKPDIR=$BASEDIR/bkpV8.4.$APPEND
RSTFILE=$BASEDIR/bkpV8.4.$APPEND/1_restore_commands.README

#How many days do you wanna keep backups
TRESHOLD=30

if [ -f /postgres1/.postvolume ]
then
 DATABASES=`su - $USER -c "$PGSQLCMDlist" |grep | |awk '{ print $1}'| grep -vE '^-|^List|^Name|template[0|1]'`
 mkdir $BKPDIR
 for db in $DATABASES
 do
   su - $USER -c "$PGSQLdumpCMD $db" | bzip2 -c > $BKPDIR/$db.dump.bz2
   echo "#---restore $db" >> $RSTFILE
   echo "bzcat $BKPDIR/$db.dump.bz2 > $BASEDIR/RST.dump.sql" >> $RSTFILE
   echo "su - $USER -c "$PGSQLCMD -d $db -f $BASEDIR/RST.dump.sql" " >> $RSTFILE
   echo "rm $BASEDIR/RST.dump.sql" >> $RSTFILE
   echo "#" >> $RSTFILE
 done
fi


# And find those old ones... and delete them (use nwrecover if you need them back)
FILES=`find $BASEDIR -type d -name 'bkpV8.4.*' -mtime +$TRESHOLD`
for f in $FILES
do
  echo "$APPEND: Treshold reached. Removed $f backup." >> $BASEDIR/$MYEXECNAME.log
  rm -rf $f
done