nixCraft Poll

Topics

Howto Backup PostgreSQL Databases Server With pg_dump command

Posted by Vivek Gite [Last updated: April 27, 2008]

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 stay up to date with the latest Linux tips, news and announcements? Subscribe to our free e-mail newsletter or RSS feed to get all updates. You can Email this page to a friend.

You may also be interested in other helpful articles:

Discussion on This Article:

  1. Nilesh Jain Says:

    sir,
    i am not able to take the backup of postgresql database.in pg_dump commnad it provide me “access denied ”
    thanks

  2. toine; » Blog Archive » del.icio.us (February 10th) Says:

    [...] Howto Backup PostgreSQL Databases | nixCraft – [...]

  3. Yogesh Says:

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

  4. Daniel Says:

    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

  5. Leopold Palomo Says:

    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.

  6. Stefan Says:

    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 :)

Leave a Reply

We encourage your comments, and suggestions. But please stay on topic, be polite, and avoid spam. Thank you very much for stopping by our site!

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

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word

Tags: , , , , , , , , , , , , ,

Copyright © 2004-2008 nixCraft. All rights reserved - TOS/Disclaimer - Privacy policy - Sitemap - Powered by Open source software.