PostgreSQL备份与恢复（全）

你的名字 2022-06-09 13:56 346阅读 0赞

好久没有上线，看到有很多朋友mark了博客文章。没能及时回复，还请见谅！

大家平时经常用到数据库备份，关于Postgresql的备份恢复看到一个比较全面的文章，今天就再次转载一下，方便大家以后参考。

**PostgreSQL**提供了三种**备份和恢复**的方式:SQL dump、文件系统复制和联机热备份。 每一种备份方式都有自己的优点和缺点，下面将详细介绍。  
  
9.1 SQL Dump  
     这种备份方式产生一个文本文件，里面包含创建各种数据库对象的SQL语句和每个表中的数据。另外，表上创建的索引中的数据不会被导出，只会导出索引的定义信息。在恢复数据库的时候，索引会被重建。可以使用数据库提供的工具pg\_dumpall和pg\_dump来进行备份。pg\_dumpall会备份一个数据库集群中的所有信息和数据。pg\_dump只备份数据库集群中的某个数据库的数据，它不会导出角色和表空间相关的信息，因为这些信息是整个数据库集群共用的，不属于某个单独的数据库。pg\_dump的基本用法如下：  
  
pg\_dump 数据库名 > 备份文件名  
  
pg\_dump将结果写到标准输出中，可以用操作系统的重定向命令将结果写到文件中。  
  
   
  
    可以在运行数据库的机器上执行pg\_dump命令，也可以在其它的机器上执行pg\_dump命令。 可以使用选项-h和-p来指定运行数据库的主机名和数据库监听的端口。例如：  
  
pg\_dump -h db\_server1 -p 5432  product > backup\_file   
  
该命令连接机器db\_server1上在端口5432处监听的数据库，将数据库product的数据备份到文件backup\_file中。如果pg\_dump命令没有使用-h和-p选项，将使用环境变量PGHOST的值作为机器名，使用环境变量PGPORT的值作为数据库的端口。如果用户没有定义环境变量PGHOST，默认使用本机名作为运行数据库的机器名。  
  
   
  
     默认的情况下，pg\_dump使用当前的操作系统用户作为连接数据库时使用的用户。可以使用选项-U或者设置环境变量PGUSER来指定连接数据库时使用的用户名。例如：  
  
pg\_dump -U liming -h db\_server1 -p 5432  product > backup\_file   
  
该命令使用用户liming连接机器db\_server1上在端口5432处监听的数据库，将数据库product的数据备份到文件backup\_file中。  
  
   
  
一般情况下，应该使用超级用户连接数据库进行备份操作，因为超级用户可以访问数据库中的任何信息。使用普通数据用户连接数据库，有些表可能无法访问。  
  
   
  
运行pg\_dump时，数据可以正常地执行其它操作。但ALTER TABLE这类修改数据库对象定义的操作会受到影响，可能会长时间处于等待状态而无法执行，所以在运行pg\_dump命令时，不要在数据库中运行修改数据库对象定义的操作。  
  
   
  
    另外要注意的是，如果数据库中有些表使用OID来实现外键约束，应当在备份数据库时同时备份表的OID信息，使用pg\_dump时加上选项–o即可达到这个目的。  
  
9.1.1 恢复数据库  
    pg\_dump创建的备份文件可以被工具psql识别。因此可以使用psql来读取pg\_dump创建的备份文件，实现恢复数据库的功能。例如：  
  
psql dbname < backup\_file  
  
psql后面的参数dbname指定的数据库必须已经存在。如果不存在，用户应当先创建dbname指定的数据库，然后再执行恢复数据的命令。psql也支持和pg\_dump一样的命令行选项，如-h和-p等。创建数据库dbname时，必须使用template0作为模板数据库，可以使用工具createdb创建数据库，也可以在psql中执行SQL命令create database来创建数据库。下面是两个实例：  
  
（1）createdb -T template0 dbname  
  
（2）create database dbname template=template0  
  
   
  
    另外，在执行恢复数据的操作以前，那些拥有数据库备份中的数据库对象或则对这些对象有访问权限的数据库的用户必须已经在数据库中存在，否则，恢复数据库以后，数据库备份中的数据库对象的所有者会发生改变。  
  
   
  
    默认的情况下，psql命令会一直执行下去直到结束，即使中间遇到SQL错误，恢复操作也会继续执行。如果想让psql在执行过程中遇到错误以后，停止恢复操作，可以在执行恢复操作以前，在psql中运行下面的命令：  
  
\\set ON\_ERROR\_STOP  
  
   
  
    如果psql在执行过程中遇到错误，则只有一部分数据被正确地恢复，这时被恢复数据库中的数据是不完整的。psql提供了另外一种恢复模式，在这种模式下，一旦恢复操作执行过程中遇到任何错误，已经恢复的数据都会自动从数据库中被删除。可以使用psql的命令行选项-l或--single-transaction来打开这种模式。  
  
在恢复操作结束以后，应该使用ANALYZE命令来重新收集查询优化器统计数据。  
  
9.1.2 使用pg\_dumpall  
    pg\_dump只备份数据库集群中的某个数据库的数据，它不会导出角色和表空间相关的信息。pg\_dumpall则可以导出整个数据库集群中所有的数据库中的数据，同时也会导出角色、用户和表空间的定义信息。使用pg\_dumpall的一般命令格式如下：  
  
pg\_dumpall > backup\_file  
  
   
  
pg\_dumpall也支持和pg\_dump一样的命令行选项，如-h和-p等。同样可以使用psql来从pg\_dumpall创建的备份文件中恢复数据库。应该使用数据库超级用户来进行恢复数据库的操作。命令格式如下：  
  
psql -f backup\_file postgres  
  
   
  
     pg\_dumpall在执行的过程中，用postgres作为用户名来连接数据库。系统自动创建的数据库postgres中的内容也会被导出来，数据库template0和template1中的内容不会被导出来。  
  
   
  
9.1.3 大型数据库的备份和恢复  
     如果数据库的规模比较大，产生的备份文件的大小超级了操作系统能够允许的单个文件的大小的最大值，可以使用压缩和将备份文件分成对个部分这两个方法来解决这个问题。  
  
（1）采用压缩的方法，可以采用操作系统提供的任何一种压缩工具来实现，常用的是gzip。例如：  
  
pg\_dump dbname | gzip > filename.gz  
  
恢复时，使用下面的命令：  
  
gunzip -c filename.gz | psql dbname  
  
也可以使用下面的命令来恢复数据库：  
  
cat filename.gz | gunzip | psql dbname  
  
   
  
（2）将备份文件分成多个部分。使用操作系统的工具split来实现。例如:  
  
pg\_dump dbname | split -b 1m - filename  
  
在这个例子中，数据库备份被分成多个大小为1MB的文件。  
  
使用下面的命令进行恢复操作：  
  
cat filename\* | psql dbname  
  
   
  
（3）使用pg\_dump自带的压缩功能。这种方法产生的备份文件也是被压缩的，同第一种方法相比，它有一个优点，就是可以只恢复备份文件中的某个表的数据。这种方法的命令格式如下，就是增加了选项-Fc：  
  
pg\_dump -Fc dbname > filename  
  
不能使用psql命令恢复用这种方法备份的数据，必须使用pg\_restore来进行恢复操作。命令格式如下：  
  
pg\_restore -d dbname filename  
  
   
  
     对于非常大的数据库，可以将压缩与分割的方法同时使用（同时使用第一种和第二种方法，或者同时使用第二种和第三种方法）。  
  
9.2文件系统复制  
    文件系统复制这种方法是直接复制所有的数据库文件，存放到其它的存储介质上。这是最简单的备份数据库的方法。可以使用操作系统的命令来完成备份，例如：  
  
tar -cf backup.tar /usr/local/pgsql/data  
  
   
  
    复制数据文件以前，必须关闭数据库。这种备份方法产生的备份文件比较大，因为索引数据也会被备份。恢复数据库时只要把备份文件复制到存放数据文件的目录中即可。  
  
   
  
9.3 联机热备份与归档恢复  
9.3.1 联机热备份  
     进行联机热备份时，不用关闭数据库。数据库可以正常地执行其它操作。如果要使联机热备份，数据库必须运行在归档模式下，将参数数据库archive\_mode设为on，然后再将参数archive\_dir设成一个启动数据库的操作系统用户有读写权限的目录，数据库就会运行在归档模式。要使这两个参数生效，必须重新启动数据库。  
  
   
  
    进行联机热备份的步骤如下：  
  
（1）检查数据库是否运行在归档模式下。  
  
（2）用超级用户连接数据库（推荐使用psql），然后执行下面的命令：  
SELECT pg\_start\_backup('label');  
  
label是一个字符串，用来确定创建的备份，可以选取一个有明显的含义的名字作为label。  
  
pg\_start\_backup命令可能会执行比较长的时间才会结束，因为数据库会自动开始一个检查点操作。  
  
（3）使用操作系统命令(如cp)，将所有的数据库文件复制到其它的存储介质上。  
  
（4）执行下面的命令结束备份操作：  
  
SELECT pg\_stop\_backup();  
  
   
  
    备份操作结束以后，会在pg\_xlog子目录下产生一个备份描述文件，该文件以“.backup”结尾，例如000000010000000000000000.004535C0.backup。注意数据库归档进程会自动将备份操作产生的备份描述文件从pg\_xlog子目录复制到存放归档事务日志的目录中（参数archive\_dir指定的目录），如果在pg\_xlog目录中找不到备份描述文件，应该在存放归档事务日志的目录中去寻找它。恢复数据库的时候需要使用备份描述文件中的信息。备份描述文件中存放有下列信息：  
  
（1）开始事务日志文件名。  
  
（2）结束事务日志文件名。  
  
（3）检查点位置。  
  
（4）备份操作开始的时间。  
  
（5）备份操作结束的时间。  
  
（6）备份的名字（就是pg\_start\_backup命令中指定的名字）。  
  
   
  
    下面是一个备份描述文件的实例：  
  
START WAL LOCATION: 0/4535C0 (file 000000010000000000000000)  
  
STOP WAL LOCATION: 0/453A98 (file 000000010000000000000000)  
  
CHECKPOINT LOCATION: 0/4535C0  
  
START TIME: 2009-03-28 23:02:34 CST  
  
LABEL: b1  
  
STOP TIME: 2009-03-28 23:04:05 CST  
  
   
  
从该文件中可以看出备份操作开始的时间是2009-03-28 23:02:34，结束的时间是2009-03-28 23:04:05，备份的名字是b1，开始事务日志的名字是 000000010000000000000000，结束事务日志的名字也是 000000010000000000000000，检查点的位置是0/4535C0。  
  
   
  
从开始事务日志文件到结束事务日志文件之间的所有事务日志文件（包括这两个事务日志文件）必须被保存好，不能丢失，否则创建的数据库备份将是无效的，不能将数据库恢复到一个一致的状态。  
  
   
  
     备份操作在执行的过程中会在数据文件目下产生一个名为backup\_label的文件，该文件叫做备份标号文件。备份标号文件在备份操作结束以后会被系统自动删除。在执行上面的第三步操作的过程中，必须同时复制备份标号文件，因为恢复数据库的时候需要使用备份标号文件中的信息。  
  
   
  
   
  
9.3.2 归档恢复  
    进行归档恢复以前，应该准备好一个名为recovery.conf的文件，该文件中包含一些恢复操作的配置参数，这些参数决定恢复操作如何进行。下面详细介绍这些参数：  
  
   
  
（1）archive\_log\_dir  
  
该参数指定存放归事务日志的目录，所有需要的归档事务日志都应该存放在该目录中，系统在进行恢复操作时会自动从该目录中读取需要的事务日志文件。  
  
   
  
（2）recovery\_target\_time  
  
该参数指定一个时间，恢复操作进行到该时间时会自动停止。该参数用来实现时间点恢复（point-In-Time Recovery）。recovery\_target\_time和下面的recovery\_target\_xid只能指定一个。  
  
   
  
（3）recovery\_target\_xid  
  
该参数指定一个事务id，恢复操作进行到该事务时会自动停止。recovery\_target\_xid和上面的recovery\_target\_time只能指定一个。  
  
   
  
（4）recovery\_target\_inclusive  
  
该参数的值是true或false。默认值是true。它影响参数recovery\_target\_time和recovery\_target\_xid，如果它的值为true，恢复操作在指定的目标（时间或事务ID）以后停止，如果它的值为false，恢复操作在指定的目标以后停止。  
  
   
  
    参数archive\_log\_dir必须出现在recovery.conf的文件中，其它的参数则是可选的，如果recovery\_target\_xid和recovery\_target\_time都没有被指定，则默认恢复到最后一个事务日志文件确定的数据库的最近的状态。如果想进行时间点恢复，应该指定参数recovery\_target\_time。  
  
   
  
    下面是一个recovery.conf文件的实例，所有的参数的值都必须用两个单引号引起来：  
  
   
  
archive\_log\_dir = '/home/yan/archive\_log'  
  
recovery\_target\_time = '2004-07-14 22:39:00 EST'  
  
recovery\_target\_xid = '1100842'  
  
recovery\_target\_inclusive = 'true'          
  
   
  
   
  
    下面介绍进行归档恢复的具体步骤：  
  
（１）停止数据库服务器。将当前数据库备份到其它目录中。  
  
（２）准备好recovery.conf文件，将所有恢复操作需要的归档事务日志都存放在参数archive\_log\_dir指定的目录中，备份描述文件也必须被存放在参数archive\_log\_dir指定的目录中。  
  
（３）将以前创建的数据库备份复制到数据文件目录中（必须与以前的数据文件目录相同）。如果数据库使用了表空间，请验证pg\_tblspc子目录下面的每个符号链接是否有效。将准备好的recovery.conf文件存放到数据文件目录中。编辑文件pg\_hba.conf，不允许任何用户在恢复的过程中连接数据库。  
  
（４） 确保数据文件目录中存在一个名为backup\_label的文件。删除pg\_xlog子目录中的所有文件，重新在pg\_xlog中创建一个名为archive\_status的子目录。  
  
（５）启动数据库，数据库在启动以后将自动进行恢复操作，恢复操作成功完成以后，数据库将自动打开，进入正常的工作状态。恢复操作成功以后，系统会将文件recovery.conf重命名为recovery.done。  
  
（６）检查数据库中的内容是否正确。  
  
   
  
     归档恢复成功结束以后，数据库会自动打开，进入正常的工作状态，可以开始响应用户的连接请求，应该修改pg\_hba.conf文件，允许用户连接数据库。归档恢复成功结束以后，在数据库的运行日志中会有下面的提示信息：  
  
……  
  
日志： 00000： 归档恢复结束。  
  
      
  
    文件backup\_label在恢复操作执行结束以后会被自动重命名为backup\_label.old。确定归档恢复成功以后，应该删除backup\_label.old，因为它已经没有任何作用。  
  
9.3.3 注意事项  
    进行归档恢复时，有下面几个注意事项：  
  
（１）哈希索引上面的操作没有被记录到事务日志中，归档恢复完成以后，必须对每个哈希索引执行REINDEX操作。  
  
（２）在创建数据库备份时不要修改模板数据库。  
  
   
  
9.3.4 时间线（timeline）  
     时间线是[**PostgreSQL**][PostgreSQL]独有的概念。它是一个整数值，与归档恢复有关。在用initdb创建一个初始的数据库集群以后，该数据库集群的时间线是１。每进行一次归档恢复，就会产生一个新的时间线，新的时间线的值在上一个时间线的值的基础上加一。每次归档恢复完成以后，都会产生一个时间线历史文件，该文件以“.history”结尾，例如00000002.history。时间线历史文件首先被存放在pg\_xlog目录中，数据库归档进程以后会自动将时间线历史文件从pg\_xlog子目录复制到存放归档事务日志的目录中（参数archive\_dir指定的目录）。

[PostgreSQL]: http://blog.csdn.net/shanzhizi/