65.9K
CodeProject 正在变化。 阅读更多。
Home

在 PostgreSQL 中备份表（非数据库）

Darren G441
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.07/5 (5投票s)

2017年4月26日

CPOL

7分钟阅读

viewsIcon

18612

downloadIcon

191

在 Windows 安装的 PostgreSQL 数据库上备份整个架构的表的有效解决方案。

背景

我的一位客户有一个小型员工团队，需要将地理数据存储在一个“适当”的数据库中，以改善他们的业务流程。然而，由于公司团队规模小，预算紧张，没有人能为全面的 SQL Server 找到合理的商业理由。

Express 版本不是一个选择，因为数据库需要包含大量参考数据集，例如建筑物位置、河流路线等，这些数据在定义任何“用户”数据表之前就已超出了 SQL Express 的 10GB 限制。唯一的真正选择是安装了 PostGIS 扩展的 PostgreSQL。

这是一个很好的选择。该平台得到了很好的支持，并且有大量的社区用户提供帮助和建议。问题出在备份上。Postgresql 开箱即用地提供了一些命令行工具来执行备份，但远不及我习惯的 SQL Server 那样丰富的备份选项。

尽管开箱即用的工具可以轻松备份数据库，但数据量非常大。Postgres 不像 SQL Server 那样允许跨数据库查询，因此您应用程序所需的所有数据都必须驻留在同一个“数据库”中。我是否提到了 20GB 以上的静态参考数据！在通信链路不佳的情况下备份和异地传输独特变化的增值数据需要一种精细的方法。我的解决方案是一个批处理文件和一些信息架构查询！

在批处理文件中使用 pg_dump 和 psql

运行 Postgres 实用程序时遇到的第一个障碍是传递密码。命令行实用程序使用起来足够简单，并且都共享一些通用开关来指定特定的数据库连接，但它们没有用于指定密码的命令行开关。

标准连接开关是

  -d, --dbname=DBNAME      database to dump
  -h, --host=HOSTNAME      database server host or socket directory
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user

文档指示可以设置一个环境变量来保存密码，但这并不是我真正想做的事情。此外，如果您需要连接到具有不同密码的不同服务器数据库，这无法轻松使用。然而，实用程序还查找一个非常容易配置的密码文件。

设置密码文件

确保您已设置了一个可用于运行任务的用户帐户，即该帐户具有“作为批处理登录”的“权限”。然后，您只需在以下位置创建一个文件

%appdata%\postgresql\pgpass.conf

实际上，这会像这样

C:\Users\postgresBackup\AppData\Roaming\pgpass.conf

文件的内容由连接详细信息行组成，PSQL 和 PG_DUMP 工具将使用这些连接详细信息。每行包含五个由冒号分隔的设置

host_name:host_port:your_db_name:your_db_user_name:secret_password

鉴于您可以在实用程序命令行上指定其中四个参数，实用程序查找此文件并扫描其内容以匹配第一个字段以查找包含密码的第五个字段并不困难！

注意：确保其他用户无法读取此文件！

提示：确保“`postgresBackup`”用户帐户的密码强度高，并且最好设置为永不过期！备份脚本最不希望发生的事情是，它在运行 3 个月后停止运行，因为底层帐户密码已过期！

使用批处理文件

该脚本旨在每天运行，它将维护 30 天的表备份历史记录。

如果您下载了本文随附的批处理文件，您将能够从计划任务中使用它。但是，您需要修改脚本顶部的连接详细信息和各种路径。

您最好直接查看批处理文件，但我将在下面讨论一些不同的部分

setlocal ENABLEDELAYEDEXPANSION

REM executable tools specified here
SET qTool=C:\Program Files\postgreSQL\9.6\bin\psql.exe
SET dumpTool=C:\Program Files\postgreSQL\9.6\bin\pg_dump.exe
SET zipTool=C:\Program Files\7-Zip\7z.exe

REM define connection variables here
SET cUser=postgres
SET cHost=localhost
SET cPort=5432
SET cDB=your_data_base
SET qSchema=your_schema

REM determine a path for the  Daily extract directory structure
SET DRota=C:\DBBackups\dailyRotation

REM Save typing later, these can be combined to the connect options required
SET cOptions=--username=%cUser% --host=%cHost% --port=%cPort% --dbname=%cDB%

REM Check directory structure for rotations
IF NOT EXIST %DRota% GOTO message_no_rotation

我已经完全确定了我将要调用的每个命令行实用程序的路径。它们很可能在您的 PATH 中，但我没有冒险！请注意，除了 Postgresql 工具之外，我还使用来自此处的 7-zip，因为我计划将所有提取的表备份压缩到一个每日存档文件中。

脚本的下一部分是关于轮换的。我选择使用 30 天的文件夹集，而不是玩弄日期标记存档。只要`DRota`路径对运行脚本的帐户有效且可写入，脚本就会创建并轮换所需内容。

REM prep and rotate the backup dirs
REM temporarily change directory to the backup rotation area
pushd %DRota%

IF EXIST D-30 (
    DEL /Q /S D-30\*.*
    RmDir D-30
)
REM Rename D-29 to D-1 to D-30 to D-2
FOR /L %%i IN (29,-1,1) DO (

    SET /A "j=%%i + 1"

    REM Rename D-29 to D-30 etc.
    IF EXIST D-%%i RENAME D-%%i D-!j!

    REM if the D-29 did not exist then a "D-30" will not, so make an empty D-30
    IF NOT EXIST D-!j! MkDir D-!j!
)

REM Always create a D-1 to hold the current backup
MkDir D-1
REM return to the previous directory  when the last PUSHD command was run from 
popd

这里最重要的是要注意已启用延迟扩展。这意味着变量`**j**`使用`**!j!**`而不是`**%j%**`访问。在使用`**FOR**`命令在批处理脚本中循环时，这一点需要记住。

我在这里所做的就是重命名目录，而不是浪费磁盘活动将备份从一个文件夹复制到下一个文件夹。

我还使用`PUSHD`和`POPD`来移动当前工作目录，以方便和保持脚本可读性。

重命名 1 到 29 后，我重新创建“D-1”文件夹，为今天的备份做准备。

REM create WDXD (Working Data eXtract Directory) if not already present in the current directory
IF NOT EXIST .\WDXD mkdir WDXD

REM clear out the WXDX (using /Q to suppress the "Are you sure prompt" 
                        and /S to include subdirectories)
DEL /Q /S .\WDXD\*.* > NUL

REM Build a list of tables using psql
"%qtool%" %cOptions% -t -o ".\WDXD\tables.txt" -A -c "SELECT table_schema, table_name   
FROM information_schema.tables WHERE table_schema = '%qSchema%';"

作为另一个方便之处，我创建了一个工作文件夹`WDXD`。我可以使用`%TMP%`，但这同样有效。

使用`pg_dump`获取一组表的关键是知道您需要备份哪些表。在这种情况下，我想要的所有表都保存在特定的模式中。以下查询将以两列`recordset`的形式给出所需的结果。

SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema = '%qSchema%';

此查询嵌入到 PSQL 实用程序的命令中。请注意，`WHERE`子句中用于过滤结果集的值由环境变量替换。如果配置的`**qSchema**`值中包含嵌入的单引号，则此处可能生成错误的 SQL。

此查询的输出使用`**-o**`开关定向到一个文件。您将得到一个包含以下内容的文本文件

  • schema1|table1
  • schema1|table2

由于使用了`-A`开关，结果数据不会有任何空格填充。这会抑制结果文本中数据的“对齐”。

有了这个文本文件，我们就可以进行实际的工作阶段。

pushd WDXD

FOR /F "tokens=1,2 delims=|" %%s in (tables.txt) DO     (

echo Dump %%s.%%t to %%s_%%t.sql

"%dumpTool%"  %cOptions% -c --format=plain --schema=\"%%s\" 
                         --table=\"%%s\".\"%%t\" > %%s_%%t.sql
echo.
echo add %%s_%%t.sql to archive %qSchema%_backup.7z
"%zipTool%" a -tzip %qSchema%_backup.zip %%s_%%t.sql
echo ---------------
)

FOR /F 命令逐行读取输入文件。/F 开关后面的引号中的选项控制每行如何解释。delims=| 告诉命令在遇到管道符号时将行拆分为不同的字段。tokens=1,2 指令告诉命令将第一个和第二个字段返回到变量中。因此，第一个字段将放入声明的变量 %%s 中，第二个字段将放入下一个可用变量 %%t 中。

“`pg_dump`”命令将“`--table`”参数解释为查找表的正则表达式。它还可以接受多个“`--table`”开关。本来可以在一个命令中生成所有需要的表转储，但由于我们不预先知道将来会找到多少个表，所以一次处理一个表似乎更安全。

注意：正则表达式的解释可能与您的预期不同。请参阅 PostgreSQL 文档中有关正则表达式规则和评估的内容！

数据提取后，立即将其添加到工作目录中的存档 ZIP 文件中。

REM Move the 7z archive file to the D-1 extract folder
Move %qSchema%_backup.zip "%DRota%\D-1\%qSchema%_backup.zip"

REM back to the starting directory
popd

最后，处理完所有表后，存档文件会移动到 D-1 目录，并将当前目录返回到脚本首次运行时我们开始的位置。我总是将任何更改的目录返回到您开始的位置，因为随着时间的推移，您永远不知道您的脚本是否会被其他脚本调用，而这些脚本可能无法很好地处理意外的工作目录更改。

所有这些操作完成后，您将拥有一个简单易用的 30 天表数据历史记录。

这可能不如日志传送等备份解决方案优雅，但当不可避免的那一天到来，有人告诉您他们在度假前删除了错误的记录时，它会帮您摆脱困境！

在您的生产环境中，我建议运行一个每月进程来复制 D-1 归档文件，以便您拥有表的“快照”的长期备份。

历史

  • V1：首次发布（2017 年 4 月 26 日）
  • V2：更正类型和格式（2017 年 4 月 27 日）
© . All rights reserved.