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:

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

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:

🐧 Get the latest tutorials on Linux, Open Source & DevOps via RSS feed or Weekly email newsletter.

🐧 29 comments so far... add one
CategoryList of Unix and Linux commands
Disk space analyzersncdu pydf
File Managementcat
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network UtilitiesNetHogs dig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04
29 comments… add one
  • Nilesh Jain Nov 3, 2006 @ 8:29

    i am not able to take the backup of postgresql pg_dump commnad it provide me “access denied ”

  • Yogesh Jun 20, 2007 @ 7:25

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

  • Daniel Apr 3, 2008 @ 12:37

    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

  • Leopold Palomo Jun 4, 2008 @ 19:37

    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

    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.


  • Stefan Jun 28, 2008 @ 15:37

    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 🙂

  • Basavaraj Sep 20, 2008 @ 12:27

    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

  • subodh keskar Apr 5, 2009 @ 6:07

    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.

  • Joe Sircy Jul 9, 2009 @ 17:06

    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:

    LIST=$(su - postgres -c "psql -lt" |awk '{ print $1}' |grep -vE '^-|^List|^Name|template[0|1]')
    for d in $LIST
      su - postgres -c "/usr/bin/pg_dump $d | gzip -c > $DIR/$d.sql.gz"
  • Hamidreza Zarei Jul 26, 2009 @ 7:08

    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?

  • Ben Sep 11, 2009 @ 16:40

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

    [ !$DIR ] && mkdir -p $DIR || :
    LIST=$(su postgres -c “psql -lt” |awk ‘{ print $1}’ |grep -vE ‘^-|^List|^Name|template[0|1]’)
    for d in $LIST
    pg_dump -U postgres $d | gzip -c > $DIR/$d.out.gz
    export PGPASSWORD=

  • Brahma Prakash Tiwari Nov 6, 2009 @ 10:50

    Hi Denial this script is great for me -Brahma Prakash

  • nihed Jan 20, 2010 @ 10:09

    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


  • Tanel Mar 22, 2010 @ 15:17

    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:

    [ !$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
    su – postgres -c “/usr/bin/pg_dump $d | gzip -c > $DIR/$d.sql.gz”

  • Tanel Mar 22, 2010 @ 15:18

    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:

    [ !$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
    su - postgres -c "/usr/bin/pg_dump $d | gzip -c > $DIR/$d.sql.gz"

  • Andrew Gudkov Apr 28, 2010 @ 19:52

    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

    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
  • Andrew Gudkov Apr 28, 2010 @ 21:32

    Even better version 🙂

    # 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
    • koco May 19, 2010 @ 7:55

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

      # Little script for PGSQLdump all databases (seperate files)
      # v20100519 -- initial version by Koco
      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"`
      #How many days do you wanna keep backups
      if [ -f /postgres1/.postvolume ]
       DATABASES=`su - $USER -c "$PGSQLCMDlist" |grep | |awk '{ print $1}'| grep -vE '^-|^List|^Name|template[0|1]'`
       mkdir $BKPDIR
       for db in $DATABASES
         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
      # 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
        echo "$APPEND: Treshold reached. Removed $f backup." >> $BASEDIR/$MYEXECNAME.log
        rm -rf $f
  • Nika May 28, 2010 @ 16:21

    I’m having problems with script and commands. could you help me with it?

    DATE=”date +%m%d”

    pg_dump $BASE | gzip > $

  • sreemon Dec 6, 2010 @ 13:52


    We were using the PostgreSQL for Bugzill, Now we lost the Password Doc, how can we take the backup of the database,
    We have the root password


  • Amza Marian Jul 24, 2011 @ 21:07

    You can list dbs with:

    psql -l -t | cut -d'|' -f1 |sed -e 's/ //g'|grep .

    A great example can be found here:

  • Jasmine Dec 29, 2011 @ 7:35

    where should i type that commands?

    • blackmennewstyle Jun 21, 2013 @ 14:41

      In your terminal (shell)

  • yoshie2x Jul 31, 2012 @ 3:47

    Hi everyone,
    I am new to freebsd, and I used postgresql for my database management.
    There was this scenario that i accidentally delete this particular database
    and the backup setting for our server is not one by one, meaning all databases.

    My question is how can I restore that particular database using the backup file which is a compressed file which is composed of several database?
    Is there a way to extract a particular database on that file?

  • Yogesh Sep 27, 2012 @ 6:50

    Thanks Vivek for ur great help 🙂

  • Pascal Apr 4, 2013 @ 21:06

    Another version to get the list of databases:
    psql -lqt | grep -vE ‘^ +(template[0-9]+|postgres)? *|’ | cut -d’|’ -f1| sed -e ‘s/ //g’ -e ‘/^$/d’

    As my `psql -lqt` output is:
    abcdefghij | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
    postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
    template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
    | | | | | postgres=CTc/postgres
    template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
    | | | | | postgres=CTc/postgres
    abc | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

  • what is the path of out file in pg_dump Nov 20, 2013 @ 6:30

    I taken dump using pg_dump > sample.sql
    where the file is goin to save?

    • Marian Amza Nov 20, 2013 @ 12:15

      In your current directory. When you type the command from directory “/home/michael”, the backup will be in this dir.

  • Ravi Dhoriya Oct 9, 2014 @ 9:46

    Thank you so much! 😀

  • Tilak Oct 21, 2014 @ 11:50


    > Does RHEL supports auto backup and restoration for PostgreSQL? If yes how to configure.
    > Does Ubuntu supports auto backup and restoration for PostgreSQL? If yes how to configure.


Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre> for code samples. Still have questions? Post it on our forum