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.outHere 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')
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:
[*]PostgreSQL documentation : Backup and Restore chapter
页:
[1]