Howto Backup PostgreSQL Databases Server With pg_dump command
来源:互联网 发布:优化很好的单机游戏 编辑:程序博客网 时间:2024/06/08 07:59
PostgreSQL is a one of the robust, open source database server. LikeMySQL 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 forbacking 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.out
To restore a payroll database:$ psql -d payroll -f payroll.dump.out
OR$ createdb payroll
However, in real life you need to compress database:
$ psql payroll $ pg_dump payroll | gzip -c > payroll.dump.out.gz
To restore database use the following command:$ gunzip payroll.dump.out.gz
Here 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 itdumps (backs up) each database, and preserves cluster-wide data such asusers and groups. You can use it as follows:$ pg_dumpall > all.dbs.out
OR$ pg_dumpall | gzip -c > all.dbs.out.gz
To restore backup use the following command:$ psql -f all.dbs.out postgres
References:
- PostgreSQL documentation : Backup and Restore chapter
<script type="text/javascript"><!--google_ad_client = "pub-7825705102693166";google_ad_slot = "1427449066";google_ad_output = "textlink";google_ad_format = "ref_text";google_cpa_choice = ""; // on file//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script><script language="JavaScript1.1" src="http://pagead2.googlesyndication.com/cpa/ads?client=ca-pub-7825705102693166&oe=UTF-8&dt=1217401112419&lmt=1217399696&prev_slotnames=0573728892&output=textlink&slotname=1427449066&correlator=1217401112248&url=http%3A%2F%2Fwww.cyberciti.biz%2Ftips%2Fhowto-backup-postgresql-databases.html&region=_google_cpa_region_&ref=http%3A%2F%2Fwww.google.com%2Fsearch%3Fq%3Dpostgresql%2Bbackup%26ie%3Dutf-8%26oe%3Dutf-8%26aq%3Dt%26rls%3Dorg.mozilla%3Aen-GB%3Aofficial%26client%3Dfirefox-a&frm=0&ff=Verdana&cc=100&ga_vid=2836092585708641000.1217401112&ga_sid=1217401112&ga_hid=139355008&flash=9.0.124&u_h=1050&u_w=1680&u_ah=1020&u_aw=1680&u_cd=32&u_tz=480&u_his=1&u_java=true&u_nplug=15&u_nmime=59"></script>
- Howto Backup PostgreSQL Databases Server With pg_dump command
- Howto Backup PostgreSQL Databases Server With pg_dump command
- postgreSql pg_dump
- Postgresql--pg_dump &&pg_restore
- Postgresql之pg_dump
- postgresql pg_dump备份测试
- Postgresql备份之pg_dump
- MySQL command to show list of databases on server
- Aptitude cache backup howto
- HowTo: The Ultimate Logrotate Command Tutorial with 10 Examples
- postgresql之pg_dump命令详解
- postgresql pg_dump,pg_restore实例详解
- How to Backup Different Release Databases with the Same Catalog Database
- HowTo: Flush Contents Of a Memcached Server Using Command Line
- Using OpenLDAP with Microsoft SQL Server and Oracle Backend Databases
- HOWTO: Set up VNC server with resumable sessions
- PostgreSQL数据库导出命令pg_dump详解
- PostgreSQL数据库导出命令pg_dump详解
- 右键菜单添加清空文件夹内容
- 通过ACM将wav转换成Windows GSM 6.10
- python dbpool
- 百日JAVA WEB魔鬼训练
- 转载:一位老工程师给年轻人的“十大忠告”
- Howto Backup PostgreSQL Databases Server With pg_dump command
- [ZZ]Ubuntu电源管理问题的说明和解决方案
- Java 面向对象编程思想之我见
- ASP.NET学习笔记(15,16)--格式化显示数据,实现计算域
- A Simple Windows HTTP Wrapper Using C++
- 《Ext详解与实践》节选:文件上传
- oracle7.3生成dmp向oracle9i导入字符集乱码问题
- Fedora9下安装Skyeye
- Javascript技术之详尽解析event对象