Backup and recovery of open source database PostgreSQL

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