Backup and recovery of open source database PostgreSQL

Source: Internet
Author: User
Tags postgresql psql



The database used recently for work has been PostgreSQL, an open source database, and any individual can use the database for commercial purposes. In the use of PostgreSQL, let me the most obvious feeling is that the database is really good, although the database installation package is very small, but the performance and ease of operation is not lost to other commercial large database, in addition to the command line interface to operate the database directly feel really cool. When using a database, it is impossible for us, as a database administrator of a small company, to avoid the problem of backup and recovery of data. PostgreSQL Although all aspects of a bit a lot, but in the database backup this aspect, it does not support incremental backup, this really makes people feel very pity ah. However, his flaws, in general, this is a very good database software.



There are three ways that PostgreSQL supports backup: The first is backup with SQL statements, the second is file system level backup, and the third is online backup and timely response. In general, the first way of backup basically has been used, but also very convenient.



In the first way of backup and recovery, the tools we use primarily are pg_dump,pg_dumpall,psql,pg_restore. The following is a few of the four tools between how to complete the backup and restore this operation. Where Pg_dump and Pg_dumpall are primarily used for backup, the difference between the two is obvious, the former is used to back up a database, and the latter is used to back up the entire DB cluster. The difference between Psql and pg_restore for recovering data is that psql can only be used for recovery of backup files that are backed up in the default format, while Pg_restore is used to restore recovery of data backed up in custom, directory, and tar formats. That's the main difference in the first aspect. Here's a script for my backup database to share and communicate.


@ echo off
Rem PostgreSQL database backup script, which does not support the backup of big data objects
Set the path = % % path; C: \ Program Files \ PostgreSQL 9.4 \ bin \;
Set the fileName = database backup
Set, fileDir, = % homepath % \ winsun \ % Date: Date: ~ 0, 4 % % % ~ 5, 2
If not exist %fileDir% (mkdir %fileDir%)
If exist %fileDir%\%fileName% (del %fileDir%\ fileName%)
Pg_dump-u postgres-d database-p 5432-w-f t-if-exists-c-f %fileDir%\ fileName%
Pg_dump-u postgres-d database-p 5432-w-if-exists-c-f %fileDir%\ fileName%.sql 





Backup and recovery of open source database PostgreSQL


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.