(转)Windows下导出postgres数据

 

这里介绍的方法适用于Microsoft Windows环境下的Postgres Plus Standard Server或Postgres Plus Advanced Server数据库。在此，我们假设您已经下载并安装了Postgres Plus Standard Server或Postgres Plus Advanced Server。通过本文，您将了解下列内容：

1. 辨别不同的备份格式；
2. 选择各种的备份和恢复选项；
3. 生成纯文本式的备份，并用其进行恢复操作；
4. 为备份和恢复创建一个定制的归档。

实际上，用来备份和恢复Postgres Plus数据库的方法和选项有很多，但是本文介绍的是使用Postgres Plus实用程序pg_dump和pg_restore来进行数据库的备份和恢复。这两个程序用于命令行，所以可以通过脚本的形式使用他们。

一、备份文件格式

pg_dump生成的备份文件共三种格式，如下所示：

1. 纯文本格式：纯文本脚本文件存放的是一些结构化查询语言语句和命令，可以供psql命令行终端程序用以重建数据库对象并载入表数据。使用psql程序可以通过纯文本备份文件来进行数据库的恢复操作。
2. 定制的归档格式：一个二进制文件可以用来从备份文件中恢复全部或者指定的数据库对象。使用pg_restore程序，可以利用定制的编档格式备份文件来恢复数据库。
3. Tar归档格式：利用tar归档文件，我们可以从备份文件中恢复全部数据库对象或者指定的数据库对象。使用pg_restore程序，可以利用tar归档格式的备份文件来恢复数据库。

在使用psql程序恢复数据库对象之前，我们可以使用文字编辑器编辑纯文本格式的备份文件。纯文本格式通常用于较小的数据库。定制的归档格式的备份文件则无法编辑。不过，我们可以使用pg_restore程序从备份文件中选择恢复哪些数据库对象。定制的归档格式通常用于中到大型的，希望从备份文件中指定需要选择性恢复数据库对象的数据库。

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

利用pg_dump程序，我们可以对纯文本或者定制的归档格式的备份文件进行压缩处理，这样能够有效减小备份文件的尺寸。当生成纯文本格式的备份文件时，默认情况下是不进行压缩的。当生成定制的归档格式的备份文件时，默认配置为进行中等水平的压缩处理。然而，pg_dump程序无法对Tar归档格式的备份文件进行压缩处理。

二、备份和恢复选项

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

1. 仅转储或者恢复模式 (表、视图和序列定义、约束、触发器与函数)，不转储或恢复表中数据(如果您当前使用的是Postgres Plus Advanced Server的话，SPL函数、过程、触发器和软件包也是能够进行备份和恢复的。)；
2. 仅转储或恢复表中数据，而不包括模式；
3. 转储或恢复属于选中的模式的数据库对象；
4. 创建备份时，排除选中的方案；
5. 转储或恢复选中的表；
6. 创建备份时，排除选中的表；
7. 允许恢复操作使用要备份的数据库的名称来新建数据库，然后从这个新创的数据库中恢复数据库对象；
8. 将数据库对象恢复到所有的现有数据库；
9. 令被恢复的数据库对象的属主与制作备份时这些对象的属主同名；
10. 将进行恢复操作的用户的角色指定为所有被恢复的数据库对象的属主。

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

三、具体操作步骤

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

1. 我们的数据库要恢复到其中的数据库集群包含的角色名称(用户名和组名)就是制作备份时数据库对象的属主。也就是说我们要制作备份(当制作备份时没有删除任何数据库对象的属主的角色)的数据库集群与恢复的目的地是同一个数据库集群，或者我们要恢复到一个新的数据库集群，并且在其中添加的一组角色名称与制作备份的数据库集群中的角色名称完全相同。
2. 用来恢复我们的数据库的数据库集群中没有与制作备份的数据库同名的数据库。 (如果制作备份文件的数据库集群正好是用来恢复数据库的数据库集群，则数据库制作备份之后，需要将其删除或者改名)

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

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

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

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

第二步：下面的操作需要利用Windows命令行，所以它们都是在命令提示符窗口中键入的命令。

为了打开命令提示符窗口，在屏幕底部的Windows任务栏中点击“开始”按钮， 然后依次选择“所有程序”、“附件”子菜单，并单击“命令提示符”选项即可。这样就会打开命令提示符窗口了。

 
▲图 1

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

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

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

pg_dump -U postgres -C -f C:\Temp\sample_backup sample 

上面的命令就会为数据库sample建立一个备份文件，名为sample_backup。您可以利用Microsoft记事本程序察看sample_backup：

 
▲图 2

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

下面我们详细介绍如何使用psql程序从一个纯文本格式的备份文件恢复数据库。注意：如果您使用的是Postgres Plus Advanced Server的话，那么可以使用edb-psql程序。

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

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

第二步：下面的操作需要利用Windows命令行，所以它们都是在命令提示符窗口中键入的命令。

为了打开命令提示符窗口，在屏幕底部的Windows任务栏中点击“开始”按钮， 然后依次选择“所有程序”、“附件”子菜单，并单击“命令提示符”选项即可。这样就会打开命令提示符窗口了。

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

注意：如果制作备份文件时不知道数据库对象属于哪些角色，您可以使用文本编辑程序搜索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 C:\Program Files\PostgresPlus\8.4SS\bin  
>psql -d postgres -U postgres  
Password for user postgres:  
psql (8.4.1)  
WARNING: Console code page (437) differs from Windows code page (1252)  
8-bit characters might not work correctly. See psql reference  
page "Notes for Windows users" for details.  
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 
= 'English_United States.1252' LC_CTYPE = 'English_United States.1252';  

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

>cd C:\Program Files\PostgresPlus\8.4SS\bin  
>psql -d postgres -U postgres  
Password for user postgres:  
psql (8.4.1)  
WARNING: Console code page (437) differs from Windows code page (1252)  
8-bit characters might not work correctly. See psql reference  
page "Notes for Windows users" for details.  
Type "help" for help.  
postgres=# \l  
List of databases  
Name | Owner | Encoding | Collation | Ctype  
-----------+----------+----------+----------------------------+------------------------  
postgres | postgres | UTF8 | English_United States.1252 | English_United States  
template0 | postgres | UTF8 | English_United States.1252 | English_United States  
template1 | postgres | UTF8 | English_United States.1252 | English_United States  
(3 rows)  

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

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

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

cd C:\Program Files\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 C:\Temp\sample_backup  

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

>psql -d postgres -U postgres -f C:\Temp\sample_backup  
Password for user postgres:  
SET  
SET  
SET  
SET  
SET  
SET  
CREATE DATABASE  
ALTER DATABASE  
psql (8.4.1)  
WARNING: Console code page (437) differs from Windows code page (1252)  
8-bit characters might not work correctly. See psql reference  
page "Notes for Windows users" for details.  
You are now connected to database "sample".  

六、使用定制的归档格式的备份文件备份数据库

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

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

第二步：下面的操作需要利用Windows命令行，所以它们都是在命令提示符窗口中键入的命令。

为了打开命令提示符窗口，在屏幕底部的Windows任务栏中点击“开始”按钮， 然后依次选择“所有程序”、“附件”子菜单，并单击“命令提示符”选项即可。这样就会打开命令提示符窗口了。

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

cd C:\Program Files\PostgresPlus\8.4SS\bin  

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

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

pg_dump -U postgres -Fc -f C:\Temp\sample_backup sample  

 上面的命令就会为数据库sample建立一个备份文件，名为sample_backup。我们不能直接观察定制的归档格式的备份文件，不过可以使用pg_restore程序的- l选项来生成该备份文件的目录：

>pg_restore -l C:\Temp\sample_backup  
;  
; Archive created at Mon Nov 23 13:07:50 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 16767 PROCEDURAL LANGUAGE - plpgsql postgres  
312; 1247 16770 TYPE public emp_query_type postgres  
19; 1255 16771 FUNCTION public emp_comp(numeric, numeric) postgres  
20; 1255 16772 FUNCTION public emp_query(numeric, numeric, character varying) postgres  
21; 1255 16773 FUNCTION public emp_query_caller() postgres  
22; 1255 16774 FUNCTION public emp_sal_trig() postgres  

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

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

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

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

第二步：下面的操作需要利用Windows命令行，所以它们都是在命令提示符窗口中键入的命令。

为了打开命令提示符窗口，在屏幕底部的Windows任务栏中点击“开始”按钮， 然后依次选择“所有程序”、“附件”子菜单，并单击“命令提示符”选项即可。这样就会打开命令提示符窗口了。

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

注意，如果建立数据库对象备份文件的时候不知道数据库对象属主的角色的话，可以从定制的归档格式的备份文件生成该备份的SQL文字版本。为此目的，可以使用pg_restore程序，唯一的参数就是该备份文件。然后您可以在文本中搜索ALTER object OWNER TO role语句。

具体如下所示：

>cd C:\Program Files\PostgresPlus\8.4SS\bin  
>pg_restore C:\Temp\sample_backup | find "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 C:\Program Files\PostgresPlus\8.4SS\bin  
>psql -d postgres -U postgres  
Password for user postgres:  
psql (8.4.1)  
WARNING: Console code page (437) differs from Windows code page (1252)  
8-bit characters might not work correctly. See psql reference  
page "Notes for Windows users" for details.  
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 C:\Program Files\PostgresPlus\8.4SS\bin  
>pg_restore -l C:\Temp\sample_backup  
;  
; Archive created at Mon Nov 23 13:07:50 2009  
; dbname: sample  
.  
.  
.  

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

>cd C:\Program Files\PostgresPlus\8.4SS\bin  
>psql -d postgres -U postgres  
Password for user postgres:  
psql (8.4.1)  
WARNING: Console code page (437) differs from Windows code page (1252)  
8-bit characters might not work correctly. See psql reference  
page "Notes for Windows users" for details.  
Type "help" for help.  
postgres=# \l  
List of databases  
Name | Owner | Encoding | Collation | Ctype  
-----------+----------+----------+----------------------------+------------------------  
postgres | postgres | UTF8 | English_United States.1252 | English_United States  
template0 | postgres | UTF8 | English_United States.1252 | English_United States  
template1 | postgres | UTF8 | English_United States.1252 | English_United States  
(3 rows)  

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

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

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

cd C:\Program Files\PostgresPlus\8.4SS\bin  

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

第六步：运行pg_restore程序，并给出要连接(使用-d选项)的数据库的名称、具有超级用户特权的角色(使用-U选项)，并用-C选项表明该恢复操作时建立一个与生成备份文件的数据库同名的新数据库，以及备份文件的目录路径。

注意：如果同选项- C一起使用的话，那么用-d选项规定的数据库对恢复操作没有影响。pg_restore程序需要通过数据库连接建立一个会话，然后才能从备份文件中创建新的数据库并恢复数据库对象。

pg_restore -d postgres -U postgres -C C:\Temp\sample_backup  

 这样就会从备份文件sample_backup中重新创建数据库sample。

八、小结

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