PostgreSQL备份与恢复(SQL dump)

1、备份

The idea behind this dump method is to generate a text file with SQL commands that, when fed back to the server, will recreate the database in the same state as it was at the time of the dump. PostgreSQL provides the utility program pg_dump for this purpose. The basic usage of this command is:

pg_dump dbname > outfile

这种方式的主要原理是生成一个包含SQL命令的文本文件，在恢复的时候通过这些命令来数据库回复到其备份时的状态。备份的命令很简单，如上所示。

As you see, pg_dump writes its result to the standard output. We will see below how this can be useful.

pg_dump命令可以把备份结果写到标准输出里，接下来我们会看到这样做的好处。

pg_dump is a regular PostgreSQL client application (albeit a particularly clever one). This means that you can perform this backup procedure from any remote host that has access to the database. But remember that pg_dump does not operate with special permissions. In particular, it must have read access to all tables that you want to back up, so in practice you almost always have to run it as a database superuser.

pg_dump是普通的PostgreSQL客户端应用.这意味着你可以在任何连接到数据库的远程客户端上运行它.但需要注意的是，pg_dump并不是在某个特殊的权限下运行的，实际上，它需要有访问所有需要备份的表的权限，所以该命令通常是数据库管理员来操作的.

To specify which database server pg_dump should contact, use the command line options -h host and -p port. The default host is the local host or whatever your PGHOST environment variable specifies. Similarly, the default port is indicated by the PGPORT environment variable or, failing that, by the compiled-in default. (Conveniently, the server will normally have the same compiled-in default.)

可以通过-h参数和-p参数来指定需要备份的数据库.如果不指定默认的主机是localhost或PGHOST(环境变量).同样，默认的端口也可以由PGPORT来指定.

Like any other PostgreSQL client application, pg_dump will by default connect with the database user name that is equal to the current operating system user name. To override this, either specify the -U option or set the environment variable PGUSER. Remember that pg_dump connections are subject to the normal client authentication mechanisms (which are described in Chapter 19).

pg_dump与其他的客户端应用一样，在连接数据库时使用的默认的用户名与当前机器的用户名是一样的，也可以通过-U或PGUSER来指定.注意，pg_dump连接也是受普通客户端应用认证机制的约束.

An important advantage of pg_dump over the other backup methods described later is that pg_dump's output can generally be re-loaded into newer versions of PostgreSQL, whereas file-level backups and continuous archiving are both extremely server-version-specific. pg_dump is also the only method that will work when transferring a database to a different machine architecture, such as going from a 32-bit to a 64-bit server.

pg_dump命令一个明显的优势是它所备份的文件不受数据版本的限制，可以恢复不同版本的数据库上，而类似于文件备份或归档备份则要求数据库版本必须一致.另外，pg_dump命令备份的文件也不受机器架构的影响，比如32位的备份文件恢复到64位的机器上.

Dumps created by pg_dump are internally consistent, meaning, the dump represents a snapshot of the database at the time pg_dump began running. pg_dump does not block other operations on the database while it is working. (Exceptions are those operations that need to operate with an exclusive lock, such as most forms of ALTER TABLE.)

pg_dump命令可以获取当前数据库的一个快照，来保证数据库内容的一致性.而且，在备份的时候，其他针对数据库的操作也不受影响.

2、恢复

The text files created by pg_dump are intended to be read in by the psql program. The general command form to restore a dump is

psql dbname < infile

备份产生的sql文本文件可以通过以上命令恢复到指定数据库中.

where infile is the file output by the pg_dump command. The database dbname will not be created by this command, so you must create it yourself from template0 before executing psql (e.g., with createdb -T template0 dbname). psql supports options similar to pg_dump for specifying the database server to connect to and the user name to use. See the psql reference page for more information.

需要注意的是，这条命令不会主动创建数据库，如果dbname不存在需要用户自己基于原来的数据库模板重新创建(createdb -T template0 dbname).psql命令也支持pg_dump在连接数据库时的那些参数。

Before restoring an SQL dump, all the users who own objects or were granted permissions on objects in the dumped database must already exist. If they do not, the restore will fail to recreate the objects with the original ownership and/or permissions. (Sometimes this is what you want, but usually it is not.)

在恢复之前，所有应该拥有恢复数据/对象的用户都应该存在或提前创建好.如果不存在的话，这些东西也不会在备份时被重新创建.

By default, the psql script will continue to execute after an SQL error is encountered. You might wish to run psql with the ON_ERROR_STOP variable set to alter that behavior and have psql exit with an exit status of 3 if an SQL error occurs:

在恢复时如果遇到错误,psql的默认处理时忽略并继续恢复，用户可以指定ON_ERROR_STOP来终止恢复.

psql --set ON_ERROR_STOP=on dbname < infile

Either way, you will only have a partially restored database. Alternatively, you can specify that the whole dump should be restored as a single transaction, so the restore is either fully completed or fully rolled back. This mode can be specified by passing the -1 or --single-transaction command-line options to psql. When using this mode, be aware that even a minor error can rollback a restore that has already run for many hours. However, that might still be preferable to manually cleaning up a complex database after a partially restored dump.

无论指定或不指定这个选项，如果备份时出现错误，你得到的都是一个不完整的数据库.相反，你可以在运行的时候指定-l或--single-transaction来将整个恢复过程看做是一个事物，这样如果在恢复的过程中遇到错误时，会自动回滚.

The ability of pg_dump and psql to write to or read from pipes makes it possible to dump a database directly from one server to another, for example:

pg_dump -h host1 dbname | psql -h host2 dbname

pg_dump以及psql命令的可以读写管道的能力可以直接通过数据库来备份和恢复，命令如上所示。