Howto Backup PostgreSQL Databases Server With pg_dump command

最新推荐文章于 2025-11-24 19:05:43 发布

转载最新推荐文章于 2025-11-24 19:05:43 发布 · 762 阅读

文章标签：

#postgresql #command #server #database #documentation #list

数据库专栏收录该内容

18 篇文章

订阅专栏

本文介绍了如何使用PostgreSQL自带的工具进行数据库备份和恢复操作。包括登录pgsql用户、列出所有数据库、使用pg_dump命令备份单个数据库以及压缩备份文件的方法。此外还提供了使用pg_dumpall命令备份所有数据库及集群数据的示例。

Trac back: http://www.cyberciti.biz/tips/howto-backup-postgresql-databases.html

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 payrollHowever, 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[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: