Howto Backup PostgreSQL Databases Server With pg_dump command

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 payroll
However, 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.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.outOR$ pg_dumpall | gzip -c > all.dbs.out.gzTo restore backup use the following command:
$ psql -f all.dbs.out postgresReferences:

Want to read Linux tips and tricks, but don't have time to check our blog everyday? Subscribe to our daily email newsletter to make sure you don't miss a single tip/tricks. Subscribe to our weekly newsletter here!

{ 1 trackback }

toine; » Blog Archive » del.icio.us (February 10th)
02.11.07 at 12:19 am

{ 7 comments… read them below or add one }

1 Nilesh Jain 11.03.06 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

2 Yogesh 06.20.07 at 7:25 am

can you provide how to do offiline backup of the postgresql database.

3 Daniel 04.03.08 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

4 Leopold Palomo 06.04.08 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.

5 Stefan 06.28.08 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 :)

6 Basavaraj 09.20.08 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

7 subodh keskar 04.05.09 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.

Leave a Comment

You can use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Tagged as: , , , , , , , , , , , , ,

Previous post: What are BogoMips

Next post: Force vmware to configure a network interface