Loading ...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 payrollHowever, in real life you need to compress database:
$ psql payroll $ pg_dump payroll | gzip -c > payroll.dump.out.gzTo restore database use the following command:$ gunzip payroll.dump.out.gzHere is a shell script for same task:
$ psql -d payroll -f payroll.dump.out
#!/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:
- Download of the day: PostgreSQL 8.3 Database Server
- Download of the day: PostgreSQL 8.2
- How to MySQL backup and data recovery with mysql-zrm
- PostgreSQL get back storage occupied by deleted tuples or records
- Quickly Backup / dump MySql / Postgres database to another remote server securely
Discussion on This Article:
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!
Tags: backup database, command gunzip, database type, gzip, open source database, payroll database, pg, pg_dump_command, postgresql databases, postgresql documentation, postgresql_database_server, postgres_database_backup, psql_command, rhel
sir,
i am not able to take the backup of postgresql database.in pg_dump commnad it provide me “access denied ”
thanks
[...] Howto Backup PostgreSQL Databases | nixCraft – [...]
can you provide how to do offiline backup of the postgresql database.
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
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.
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