Linux上Postgres 数据库备份和恢复

本文将为读者介绍如何使用pg_dump和pg_restore来保护Postgres Plus数据库。我们会向您介绍如何构建数据库和应用程序，怎样周期性创建数据库备份的方法，并在需要的时候修复它们。

一、引言

本文将为读者详解在linux环境中快速玩转Postgres Plus Standard Server或者Postgres Plus Advanced Server数据库产品的必要知识，在此之前，我们假定您已经下载并且安装好了Postgres Plus Standard Server 或者Postgres Plus Advanced Server。这将有助于您加快对于Postgres Plus Standard Server或者Postgres Plus Advanced Server的技术评估。

通过本文，您将能够掌握下列技能：

•熟悉不同的备份格式

•选择不同的备份和恢复选项

•使用纯文本进行备份和恢复

•为备份和恢复创建一个定制的归档

二、功能描述

在备份和恢复Postgres Plus数据库的时候，我们有多种不同的方法和选项可用，本文将向您展示如何利用两个Postgres Plus实用程序，即pg_dump和pg_restore来完成数据库的备份和恢复。这两个程序可以在命令行中使用，所以只要您喜欢的话，完全可以将它们集成到您自己的脚本之中。

注意：有关使用pg_dump创建一个备份文件更详细的说明，请参阅Postgres Plus web文档页面中的“PostgreSQL Client Applications”一章。有关使用pg_restore恢复一个备份文件更详细的说明，请参阅Postgres Plus web文档页面中的“PostgreSQL Client Applications”一章。有关Postgres Plus数据库所有备份和恢复策略的说明，请参阅Postgres Plus web文档页面中的“backup and restore”一章。备份文件的格式

利用pg_dump，我们可以创建三种不同的备份文件格式，如下所示：

•纯文本格式。 纯文本格式的脚本文件，可以包含SQL语句和命令，然后用命令行终端程序psql运行该文件，来重新创建数据库对象并装入表中数据。我们可以使用psql程序从纯文本格式的备份文件中进行恢复操作。

•定制的归档格式。 它是一个二进制文件，能够用来从备份文件恢复选定的数据库对象或者全部对象。我们可以使用pg_restore程序从定制的归档格式的备份文件中完成恢复。

•Tar归档格式。 Tar归档文件可以从备份文件中恢复选取的数据库对象或全部数据库对象。我们可以使用pg_restore程序从Tar归档格式的备份文件中进行恢复。

如果您需要的话，可以在用psql程序恢复纯文本格式的备份文件的数据库对象之前，使用文字编辑器编辑该纯文本文件。纯文本格式适用于小型的数据库。

定制的归档备份文件无法进行编辑，不过我们可以使用pg_restore程序从备份文件中选择需要恢复的数据库对象。定制的归档格式通常用于中到大型的数据库并且希望从备份文件中有选择性地恢复数据库对象的情形。

Tar归档备份文件可以使用诸如tar之类的标准Linux工具进行操作。就像定制的归档格式一样，我们也可以使用pg_restore程序从备份文件中选择需要恢复的数据库对象。

您可以使用pg_dump程序来压缩纯文本或者定制的归档格式的备份文件，这样能够缩小备份文件的尺寸。当生成纯文本格式的备份文件时，默认的配置为不进行压缩。当生成定制的归档格式的备份文件时，默认配置为进行中等水平的压缩处理。pg_dump程序无法对Tar归档格式的备份文件进行压缩处理。

三、与备份和恢复有关的选项

Pg_dump和pg_restore程序为我们提供了许多选项，通过它们可以控制将哪些数据库对象保存到备份文件，从备份文件中恢复哪些数据库对象，以及如何进行恢复。下面列出了一些选项：

•仅转储或者恢复模式 (表、视图和序列定义、约束、触发器与函数)，不转储或恢复表中数据。(如果您当前使用的是Postgres Plus Advanced Server的话，SPL函数、过程、触发器和软件包也是能够进行备份和恢复的。)

•仅转储或恢复表中数据，而不包括模式。

•转储或恢复属于选中的模式的数据库对象。

•创建备份时，排除选中的方案。

•转储或恢复选中的表。

•创建备份时，排除选中的表。

•允许恢复操作使用要备份的数据库的名称来新建数据库，然后从这个新创的数据库中恢复数据库对象。

•将数据库对象恢复到所有的现有数据库。

•令被恢复的数据库对象的属主与制作备份时这些对象的属主同名。

•将进行恢复操作的用户的角色指定为所有被恢复的数据库对象的属主。

注意：有关创建一个备份文件更详细的说明，请参阅Postgres Plus web文档页面中的“PostgreSQL Client Applications”一章中关于pg_dump的内容。有关恢复定制的归档格式的备份文件的详细过程，请参阅Postgres Plus web文档页面中的“PostgreSQL Client Applications”一章中关于pg_restore的内容。

假设我们想要备份一个数据库的全部内容，并在随后某个时间从备份文件中重建整个数据库，下面我们就介绍用纯文本格式的备份文件和定制的归档格式的备份文件来完成上述过程。

四、指导步骤

假定您的数据库环境如下所示：

•我们的数据库要恢复到其中的数据库集群包含的角色名称(用户名和组名)就是制作备份时数据库对象的属主。换句话说，我们要制作备份(当制作备份时没有删除任何数据库对象的属主的角色)的数据库集群与恢复的目的地是同一个数据库集群，或者我们要恢复到一个新的数据库集群，并且在其中添加的一组角色名称与制作备份的数据库集群中的角色名称完全相同。

•用来恢复我们的数据库的数据库集群中没有与制作备份的数据库同名的数据库。 (如果制作备份文件的数据库集群正好是用来恢复数据库的数据库集群，则数据库制作备份之后，需要将其删除或者改名。)

注意：一个数据库集群就是由同一个Postgres Plus 实例运行的一组数据库。一个数据库集群通过它的ip地址和端口号唯一标识。

五、使用纯文本格式的备份文件备份数据库

下面我们详细介绍如何使用pg_dump程序创建数据库的纯文本格式的备份文件。

第一步：登录到Postgres Plus数据库 所在的计算机。在该计算机上任何有效的帐户都可以。

第二步：使用cd命令将当前工作目录切换至Postgres Plus所在的bin目录。

cd /opt/PostgresPlus/8.4SS/bin

注意：如果您使用的是Postgres Plus Advanced Server的话，请将当前工作目录通过cd命令切换至dbserver/bin。

第三步：使用具有超级用户权限(-U选项)的角色运行pg_dump程序，使用–C来包含备份文件中的CREATE DATABASE语句，给备份文件指定的名称(-f选项)，以及用来制作备份文件的数据库的名称(命令行的最后一个参数)。

./pg_dump -U postgres -C -f /home/user/sample_backup sample

上面的命令就会为数据库sample建立一个备份文件，名为sample_backup。下面是sample_backup文件的一部分内容：

–

–

PostgreSQL database dump

–

SET

statement_timeout

=

0

;

SET

client_encoding

=

'

UTF8

'

;

SET

standard_conforming_strings

=

off

;

SET

check_function_bodies

=

false;

SET

client_min_messages

=

warning;

SET

escape_string_warning

=

off

;

–

–

Name: sample; Type: DATABASE; Schema: -; Owner: postgres

–

CREATE

DATABASE

sample

WITH

TEMPLATE

=

template0 ENCODING

=

'

UTF8

'

LC_COLLATE

=

'

en_US.UTF-8

'

LC_CTYPE

=

'

en_US.UTF-8

'

;

ALTER

DATABASE

sample OWNER

TO

postgres;

\connect sample

SET

statement_timeout

=

0

;

SET

client_encoding

=

'

UTF8

'

;

SET

standard_conforming_strings

=

off

;

SET

check_function_bodies

=

false;

SET

client_min_messages

=

warning;

SET

escape_string_warning

=

off

;

–

–

Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres

–

CREATE

PROCEDURAL LANGUAGE plpgsql;

ALTER

PROCEDURAL LANGUAGE plpgsql OWNER

TO

postgres;

SET

search_path

=

public

, pg_catalog;

六、从纯文本格式的备份文件恢复数据库

下面我们详细介绍如何使用psql程序从一个纯文本格式的备份文件恢复数据库。

注意：如果您使用的是Postgres Plus Advanced Server的话，那么可以使用edb-psql程序。

上面，我们为数据库sample建立了一个纯文本格式的备份文件，即sample_backup，之后我们要用它来恢复该示例数据库。

第一步：登录到Postgres Plus数据库 所在的计算机。在该计算机上任何有效的帐户都可以。

第二步：如果想把数据库恢复到建立备份文件的数据库集群之外的数据库集群的话，或者从数据库集群中删除了角色的话，那么要确保制作备份文件时数据库对象属主的所有的角色名称都存在于用来恢复备份文件的数据库集群中。

注意：如果制作备份文件时不知道数据库对象属于哪些角色，您可以使用文本编辑程序搜索ALTER object OWNER TO role语句，下面是些例子：

ALTER

PROCEDURAL LANGUAGE plpgsql OWNER

TO

postgres;

ALTER

TYPE

public

.emp_query_type OWNER

TO

postgres;

ALTER

FUNCTION

public

.emp_comp(p_sal numeric, p_comm numeric) OWNER

TO

postgres;

您可以通过psql程序连接到数据库集群，然后运行\dg命令列出当前存在于数据库集群中的角色，如下所示：

$ cd

/

opt

/

PostgresPlus

/

8

.4SS

/

bin

$ .

/

psql

d postgres

U postgres

Password

for

user

postgres:

psql (

8.4

.

1

)

Type “help”

for

help.

postgres

=

\dg

List

of

roles

Role name

|

Attributes

|

Member

of

–

———+————-+———–

postgres

|

Superuser

|

{}

:

Create

role

:

Create

DB

如果您需要创建角色的话，可以使用CREATE ROLE语句。

注意：如果用来恢复数据库对象的数据库集群中没有该数据库对象原属主的角色，那么当ALTER语句无法指定该属主时，就会显示一个出错信息。当执行恢复操作时，该数据库对象最终将分配给psql程序的-U选项指定的角色。

第三步：保证在用于恢复数据库的数据库集群中没有与制作备份的数据库同名的数据库。

注意：如果不知道制作备份文件的数据库的名称，您可以使用文本编辑程序搜索CREATE DATABASE语句，下面举例说明：

CREATE

DATABASE

sample

WITH

TEMPLATE

=

template0 ENCODING

=

'

UTF8

'

LC_COLLATE

=

'

en_US.UTF-8

'

LC_CTYPE

=

'

en_US.UTF-8

'

;

您可以通过psql程序连接到数据库集群，然后运行\l命令列出当前存在于数据库集群中的数据库，如下所示：

$ cd

/

opt

/

PostgresPlus

/

8

.4SS

/

bin

$ .

/

psql

d postgres

U postgres

Password

for

user

postgres:

psql (

8.4

.

1

)

Type “help”

for

help.

postgres

=

\l

List

of

databases

Name

|

Owner

|

Encoding

|

Collation

|

Ctype

|

Access

privileges

–

———+———-+———-+————-+————-+———————–

postgres

|

postgres

|

UTF8

|

en_US.UTF

8

|

en_US.UTF

8

|

template0

|

postgres

|

UTF8

|

en_US.UTF

8

|

en_US.UTF

8

|

=

c

/

postgres

postgres

=

CTc

/

postgres

template1

|

postgres

|

UTF8

|

en_US.UTF

8

|

en_US.UTF

8

|

=

c

/

postgres

postgres

=

CTc

/

postgres

(

3

rows)

如果数据库集群中的一个数据库与用于建立备份文件的数据库同名，那么您现在就可以使用ALTER DATABASE语句的RENAME TO选项给现有的数据库改名，或者使用DROP DATABASE语句删除它。

注意：在恢复操作期间，如果该数据库集群含有一个与制作备份文件的数据库同名的数据库的话，那么当CREATE DATABASE语句无法用同一名称新建数据库时，就会显示一个出错信息。这时，psql程序将从现有的数据库中的备份文件来重新创建各数据库对象。这很可能导致生成的数据库中包含不希望有的数据库对象，并且数据库设置也不正确。

第四步：:使用cd命令将当前工作目录切换至Postgres Plus所在的bin目录。

cd

/

opt

/

PostgresPlus

/

8

.4SS

/

bin

注意：如果您使用的是Postgres Plus Advanced Server的话，请将当前工作目录通过cd命令切换至dbserver/bin。

第五步：运行psql程序，并给出要连接(使用-d选项)的数据库的名称、具有超级用户特权的角色(使用-U选项)和备份文件的目录路径(使用-f选项)。

注意：用-d选项规定的数据库对恢复操作没有影响。psql程序需要先通过数据库连接建立一个会话，然后才能处理备份文件中的SQL语句和psql命令。

.

/

psql

d postgres

U postgres

f

/

home

/

user

/

sample_backup

这样就会从备份文件sample_backup中重新创建数据库sample。下面给出psql程序处理备份文件中的SQL语句和psql命令时所显示的部分消息：

$ .

/

psql

d postgres

U postgres

f

/

home

/

user

/

sample_backup

Password

for

user

postgres:

SET

SET

SET

SET

SET

SET

CREATE

DATABASE

ALTER

DATABASE

psql (

8.4

.

1

)

You are now connected

to

database

“sample”.

七、创建定制的归档格式的数据库备份文件

下面我们详细介绍如何使用pg_dump程序创建一个数据库的定制的归档格式的备份文件。

第一步：登录到Postgres Plus数据库服务器所在的计算机。在该计算机上任何有效的帐户都可以。

第二步：使用cd命令将当前工作目录切换至Postgres Plus所在的bin目录。

cd /opt/PostgresPlus/8.4SS/bin

注意：如果您使用的是Postgres Plus Advanced Server的话，请将当前工作目录通过cd命令切换至dbserver/bin。

第三步：运行pg_dump程序，使用-U选项给出具有超级用户权限的角色，使用–Fc选项规定定制的归档格式，利用-f选项给备份文件指定名称，并给出用来制作备份文件的数据库的名称(命令行的最后一个参数)。

./pg_dump -U postgres -Fc -f /home/user/sample_backup sample

上面的命令就会为数据库sample建立一个备份文件，名为sample_backup。虽然定制的归档格式的备份文件无法直查看，但是却可以通过pg_restore程序的-l选项来生成备份文件的目录，操作步骤如下：

$ .

/

pg_restore

l

/

home

/

user

/

sample_backup

;

; Archive created at Thu Nov

12

15

:

12

:

12

2009

; dbname: sample

; TOC Entries:

44

; Compression:

1

;

Dump

Version:

1.11

0

; Format: CUSTOM

;

Integer

:

4

bytes

; Offset:

8

bytes

; Dumped

from

database

version:

8.4

.

1

; Dumped

by

pg_dump version:

8.4

.

1

;;

; Selected TOC Entries:

;

6

;

2615

2200

SCHEMA

public

postgres

1818

;

0

0

COMMENT

SCHEMA

public

postgres

1819

;

0

0

ACL

public

postgres

323

;

2612

17798

PROCEDURAL LANGUAGE

plpgsql postgres

312

;

1247

17801

TYPE

public

emp_query_type postgres

19

;

1255

17802

FUNCTION

public

emp_comp(numeric, numeric) postgres

20

;

1255

17803

FUNCTION

public

emp_query(numeric, numeric,

character

varying

) postgres

21

;

1255

17804

FUNCTION

public

emp_query_caller() postgres

22

;

1255

17805

FUNCTION

public

emp_sal_trig() postgres

八、从定制的归档格式的备份文件中恢复数据库

下面我们详细介绍如何使用pg_restore程序从一个定制的归档格式的备份文件中恢复数据库。

之前，我们从数据库sample建立了一个定制的归档格式的备份文件，即sample_backup，接下来我们要用它来恢复数据库sample。

第一步：登录到Postgres Plus数据库服务器所在的计算机。在该计算机上任何有效的帐户都可以。

第二步：如果想把数据库恢复到建立备份文件的数据库集群之外的数据库集群的话，或者从数据库集群中删除了角色的话，那么要确保制作备份文件时数据库对象属主的所有的角色名称都存在于用来恢复备份文件的数据库集群中。

注意：制作备份文件时如果不知道数据库对象属于哪些角色，您就可以从定制的归档格式的备份文件中生成一个SQL文本版本的备份。为此，运行pg_restore程序，以备份文件作为其唯一的参数。然后您可以在文本中搜索ALTER object OWNER TO role语句。具体如下所示：

$ cd

/

opt

/

PostgresPlus

/

8

.4SS

/

bin

$ .

/

pg_restore

/

home

/

user

/

sample_backup

|

grep

'

OWNER TO

'

ALTER

PROCEDURAL LANGUAGE plpgsql OWNER

TO

postgres;

ALTER

TYPE

public

.emp_query_type OWNER

TO

postgres;

ALTER

FUNCTION

public

.emp_comp(p_sal numeric, p_comm numeric) OWNER

TO

postgres;

您可以通过psql程序连接到数据库集群，然后运行\dg命令列出当前存在于数据库集群中的角色，如下所示：

$ cd

/

opt

/

PostgresPlus

/

8

.4SS

/

bin

$ .

/

psql

d postgres

U postgres

Password

for

user

postgres:

psql (

8.4

.

1

)

Type “help”

for

help.

postgres

=

\dg

List

of

roles

Role name

|

Attributes

|

Member

of

–

———+————-+———–

postgres

|

Superuser

|

{}

:

Create

role

:

Create

DB

如果您需要创建角色的话，可以使用CREATE ROLE语句。注意：如果用来恢复数据库对象的数据库集群中没有该数据库对象原属主的角色，那么当ALTER语句无法指定该属主时，就会显示一个出错信息。当执行恢复操作时，该数据库对象最终将分配给pg_restore程序的-U选项指定的角色。

第三步：保证用于恢复数据库的数据库集群中，现有数据库不会与制作备份的数据库同名。注意：如果不知道制作备份文件的数据库的名称，您可以使用pg_restore程序，加上-l选项，以备份文件作为唯一的参数来列出定制的归档格式的备份文件的目录。数据库名称位于目录顶部的dbname字段。

下面是一个例子：

$ cd

/

opt

/

PostgresPlus

/

8

.4SS

/

bin

$ .

/

pg_restore

l

/

home

/

user

/

sample_backup

;

; Archive created at Wed Nov

18

12

:

34

:

10

2009

; dbname: sample

您可以通过psql程序连接到数据库集群，然后运行\l命令列出当前存在于数据库集群中的数据库，如下所示：

$ cd

/

opt

/

PostgresPlus

/

8

.4SS

/

bin

$ .

/

psql

d postgres

U postgres

Password

for

user

postgres:

psql (

8.4

.

1

)

Type “help”

for

help.

postgres

=

\l

List

of

databases

Name

|

Owner

|

Encoding

|

Collation

|

Ctype

|

Access

privileges

–

———+———-+———-+————-+————-+———————–

postgres

|

postgres

|

UTF8

|

en_US.UTF

8

|

en_US.UTF

8

|

template0

|

postgres

|

UTF8

|

en_US.UTF

8

|

en_US.UTF

8

|

=

c

/

postgres

postgres

=

CTc

/

postgres

template1

|

postgres

|

UTF8

|

en_US.UTF

8

|

en_US.UTF

8

|

=

c

/

postgres

postgres

=

CTc

/

postgres

(

3

rows)

如果数据库集群中的一个数据库与用于建立备份文件的数据库同名的话，您可以使用ALTER DATABASE语句的RENAME TO选项给现有的数据库改名，或者使用DROP DATABASE语句将其删除。

注意：在恢复操作期间，如果该数据库集群含有一个与制作备份文件的数据库同名的数据库的话，那么当CREATE DATABASE语句无法用同样的名字新建数据库时，就会显示出错信息。此后，pg_restore程序会从现有的数据库内的从备份文件中重新创建数据库对象。 这很可能导致数据库内包含不希望的数据库对象以及错误的数据库设置。

第四步：使用cd命令将当前工作目录切换至Postgres Plus所在的bin目录。

cd

/

opt

/

PostgresPlus

/

8

.4SS

/

bin

注意：如果您使用的是Postgres Plus Advanced Server的话，请将当前工作目录通过cd命令切换至dbserver/bin。

第五步：运行pg_restore程序，用-d选项给出要连接的数据库的名称，使用-U选项给出具有超级用户权限的角色，利用-C选项指出本恢复操作新建的数据库与制作备份的数据库同名，并通过命令行的最后一个参数给出备份文件的目录路径。

第六步：运行psql程序，并给出要连接(使用-d选项)的数据库的名称、具有超级用户特权的角色(使用-U选项)和备份文件的目录路径(使用-f选项)。pg_restore程序需要通过数据库连接建立一个会话，然后才能从备份文件中创建新的数据库并恢复数据库对象。

.

/

pg_restore

d postgres

U postgres

C

/

home

/

user

/

sample_backup

这样，我们就会从名为sample_backup的备份文件中重新创建数据库sample。

九、结束语

本文为读者详细介绍了在linux系统上备份和恢复Postgres Plus数据库的具体操作过程。通过备份和恢复功能，我们能够在工作的不同阶段对数据库进行备份，并在需要的时候进行相应的恢复操作。