postgresql windows 自动数据库备份 psql备份表

转载

小咪咪 2024-08-21 20:18:32

文章标签 单元测试功能测试学习数据库 sql 文章分类 云原生云计算

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的文件，该文件叫做备份标号文件。备份标号文件在备份操作结束以后会被系统自动删除。在执行上面的第三步操作的过程中，必须同时复制备份标号文件，因为恢复数据库的时候需要使用备份标号文件中的信息。