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:

🐧 If you liked this page, please support my work on Patreon or with a donation.
🐧 Get the latest tutorials on SysAdmin, Linux/Unix, Open Source & DevOps topics via:
CategoryList of Unix and Linux commands
File Managementcat
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilitiesdig 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
  • Tilak Oct 21, 2014 @ 11:50

    HI,

    > 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.

    Thanks
    Tilak

  • Ravi Dhoriya Oct 9, 2014 @ 9:46

    Thank you so much! 😀

  • 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.

  • 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 |

  • Yogesh Sep 27, 2012 @ 6:50

    Thanks Vivek for ur great help 🙂

  • 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?

Leave a Reply

Your email address will not be published. Required fields are marked *

Use HTML <pre>...</pre>, <code>...</code> and <kbd>...</kbd> for code samples.