如何在 pgAdmin 中使用 pgAgent 为 PostgreSQL 安排自动备份

介绍

pgAgent 是一种用于 PostgreSQL 数据库的日程安排工作的工具,它还具有比常用的cron更强大的日程安排功能,因为它是专门用于处理 Postgres 任务的工具。例如,pgAgent 可以计划多个步骤而无需批量脚本或不重复命令。重要的是要注意,即使您安装了 pgAdmin,pgAgent 必须独立下载,特别是作为扩展到 pgAdmin。

在本教程中,您将安装 pgAgent,配置您的数据库以在 pgAdmin 图形用户界面 (GUI) 上使用 pgAgent,设置 pgAgent 作为一个 DAEMON,然后使用 pgAgent 来安排一个任务,以备份您的数据库。

前提条件

要遵循本教程,您将需要:

您可以通过阅读我们的 初始服务器设置指南

• PostgreSQL 安装在您的服务器上。您可以通过遵循我们在 Ubuntu 22.04 上如何安装和使用 PostgreSQL 上的指南来设置此设置。(https://andsky.com/tech/tutorials/how-to-install-and-use-postgresql-on-ubuntu-22-04)您可以通过阅读我们的 初始服务器设置指南
• PostgreSQL 安装在您的服务器上。

步骤 1 - 安装 pgAgent

如前所述,pgAgent在安装pgAdmin时不会自动配置,您可以通过运行apt install和包名称pgagent来从终端安装pgAgent,如下命令所示:

1sudo apt install pgagent

安装 pgAgent 后,转到下一步来配置您的数据库以在 pgAdmin 中使用 pgAgent。

步骤 2 — 配置您的数据库为 pgAgent

您可以通过 pgAdmin 配置您的数据库以使用 pgAdmin. 打开您的 Web 浏览器并导航到 pgAdmin 应用程序在 http://your_domain. 一旦您登录到您的帐户,请导航到左侧面板上的树控制。 查找您创建的数据库,名为 sammy,并扩展列表。 从此列表中,将有一个名为 Extensions的选项。 一旦您找到它,右键单击它并选择选项 Query Tool:

pgAgent 需要将一个扩展加载到您的数据库,才能在 pgAdmin 中使用. 要做到这一点,请写下面的查询并点击侧向箭头 ▶ 表示 执行 来执行命令:

1CREATE EXTENSION pgagent;

使用 Query Tool 函数的此步骤在下面的示例中表示:

在消息选项卡下,会出现一个输出,返回请求在300毫秒内成功返回,这确认了pgAgent扩展已成功创建。

<$>[注] 注: 如果您没有在数据库中加载适当的 plpgsql 语言,您将收到以下错误消息:

1[secondary_label Output]
2ERROR:  language "plpgsql" does not exist
3HINT:  Use CREATE EXTENSION to load the language into the database.
4SQL state: 42704

如果发生这种情况,您需要运行CREATE LANGUAGE来安装所需的pl/pgsql程序语言。

1CREATE LANGUAGE plpgsql;

安裝 pl/pgsql 語言後,底部的訊息會表示類似的訊息: **Query 在 231 msc 內成功返回。

运行这些查询后,在扩展下,将有两个项目列为pgagent和plpgsql:

左侧的树控制中会出现一个名为 pgAgent Jobs的新项目,这意味着 pgAgent 已成功安装在您的 pgAdmin 帐户中。

<$>[注] 注: 如果这些项目不立即显示给您,请刷新浏览器页面,如果您的查询成功,它们应该显示。

步骤 3 — 设置 pgAgent 作为一个 Daemon

现在你已经安装了pgAgent并通过pgAdmin配置了你的数据库,接下来你需要在你的服务器上设置pgAgent作为一个戴蒙。

要将 pgAgent 设置为 DAEMON,您需要一个 PostgreSQL 连接字符串,以便在安排工作时,该过程会正常执行。在这种情况下,您正在设置一个 PostgreSQL libpq 连接字符串,这意味着一个用户特定的字符串,并连接到您设置的某些参数。

您的连接字符串将提供您的主机名、数据库名和用户名。在我们的示例中,主机将使用Unix域接口,数据库名称为sammy,用户名为sammy。

1pgagent host=/var/run/postgresql dbname=sammy user=sammy

如果输出中没有返回,并且您没有收到连接错误消息,则连接字符串设置成功。

创建连接字符串后,您已经准备好与 pgAgent 计划工作。

步骤 4 — 与 pgAgent 计划工作

pgAgent 作为一个编程代理,可以运行和管理任务,并可以创建一个或多个步骤或时间表的工作。例如,一个步骤可能由壳脚本上的多个 SQL 陈述组成,并连续执行。

对于本教程的目的,您将使用pgAgent创建一个工作,将每分钟在每周的每一天备份您的 sammy数据库. 您可以从右键单击 pgAgent Jobs开始,选择 Create,然后选择 **pgAgent Job...**如下:

一旦你这样做,会出现一个名为创建 - pgAgent 工作的提示,你可以开始填写在 ** 一般** 选项卡中所需的信息. 在本示例中,我们将使用名称为 sammy_backup,不会指定一个 ** 主机代理**,因为我们希望能够在任何主机上运行这个工作。

接下来,导航到步骤选项卡。在右上角创建一个步骤时,点击+符号。在本示例中,我们将这个步骤命名为step1**。然后,为了扩展您的选项,点击垃圾桶图标左侧的铅笔。

对于 Kind 选项,您可以选择 SQL 或 Batch,在这里我们选择了 Batch。 在本示例中,您想要选择 Batch 的理由是因为这是您将为您的数据库设置的相应的 PostgreSQL 命令运行的原因。 SQL 选项可用于计划执行原始 SQL 的任务。在这种情况下,我们选择了 Local for Connection type,以便该步骤在本地服务器上执行,但如果您喜欢,您也可以选择 Remote 用于您选择的远程主机。

对于 Database字段,请确保您选择了正确的数据库,在这里我们已指定了 sammy。 使用 On error 选项,您可以自定义 pgAgent 响应,如果在执行步骤时出现错误。

在相同的 步骤选项卡中,还有一个 代码选项卡. 如果您选择了 Batch,就像我们在本例中所做的,然后导航到那个 代码选项卡.一旦您在这个选项卡中,有一个空行,您可以插入您的PostgreSQL命令。

本教程将使用pg_dump命令来备份您的 Postgres 数据库 sammy. 在此命令中,包括您的特定用户名、数据库名称和--clean旗帜,这有助于pg_dump通过下载或清理数据库对象,然后输出正在创建的任何命令。对于--file旗帜,您正在指定备份文件将被保存的确切位置。这个声明的最后部分date%Y-%m-d%H-%M-%S是动态地生成一个日期和每个备份的多个文件。否则,备份文件将不断翻译并保存到现有的一个。这样,您可以跟踪每个备份文件的任何指定时间或日期。您的完整命令将如下:

1pg_dump --username=sammy --dbname=sammy --clean --file=/home/sammy/backup-`date +%Y-%m-%d-%H-%M-%S`.sql

<$>[注] 注: 如果您选择将备份文件保存到不同的位置,请确保使用到所选目录的绝对路径。例如,虽然 ~/ 通常指向 /home/sammy/ 的首页目录,但在这种情况下, pg_dump 需要 /home/sammy/ 的绝对路径。

一旦你添加了备份命令,你可以导航到标记为 日程表的选项卡。类似于设置 步骤时,点击 **+**符号添加日程表,然后提供你喜爱的名称,然后点击垃圾箱图标旁边的铅笔图标来扩展你的选项。在 一般选项卡下,你写的 名称将会出现,在本示例中,它是 日程表1。 再次,对于 启用,这是默认的开关,以确保日程表正确执行。 对于 开始和 结束选项,请指定你计划的工作的开始和结束日期和时间。 因为你将测试你的计划工作,请确保当前的时间在 开始和 **结束范围内。

在这里,您可以定制您想要执行这个计划的工作频率。您可以尽可能准确地指定周、月、日期、小时或分钟。请注意,如果您不进行选择,这与选择选择所有是一样的。因此,如果您将 Week Days空白,您的日程将考虑到所有周日。同样,使用 Times,您可以将小时或分钟空白,这与选择所有是一样的。** 请记住,时间是以 cron 格式进行的,所以对于本示例,要创建每分钟的备份,您必须在一个小时(00 到 59)中选择每分钟。

如果有天或时间,您不希望执行任务,您可以创建一个更详细的时间表,或者您可以通过导航到 例外卡来设置此时间表。

<$>[注] 注: 一个任务也根据时间表执行,因此每当它被更改时,计划运行时间将被重新计算。当这种情况发生时,pgAgent会对过去计划运行时间值的数据库进行调查,从那里,通常会在指定开始时间的一分钟内开始。

当您完成设置和自定义所要执行的日程安排时,请按 ** 保存 ** 按钮. 一个新的 pgAgent 工作将出现在左侧的树控制器上,并显示您的工作名称。

现在你已经成功创建了一个 pgAgent 工作,在下一步,你将学习如何验证你的 pgAgent 工作正在成功运行。

步骤5 – 验证你的pgAgent工作

您可以检查您每分钟创建数据库备份文件的计划工作是否以几种方式工作. 在 pgAdmin 中,您可以导航到左侧的树控制器,然后点击 sammy_backup。

请注意,统计数据可能不会立即显示或更新,因此您可能需要退出浏览器或更新浏览器。

如果你喜欢从命令行验证你的工作,你可以运行ls ~命令列出你的家庭目录文件. 在本教程中,当你在 Step 4中写下pg_dump命令时,你会设置这个家庭目录的确切位置:

1ls ~

输出将列出所保存的备份文件的每个实例。

步骤 6 – 修改、禁用、删除和手动执行 pgAgent 工作(可选)

pgAgent 在修改或禁用工作时提供灵活性. 使用 pgAdmin,您可以导航到特定 pgAgent 工作,在这种情况下, sammy_backup. 然后右键单击并从列表中选择 ** Properties** 选项。

如果你在 ** 属性 ** 并按下铅笔图标,你可以导航到 ** 时间表** 选项卡以禁用你的工作如下:

此外,从左侧的树控制,如果您右键单击 pgAgent 任务,您可以选择 DELETE/DROP选项完全删除任务. 如果您想手动执行任务,您可以重复相同的步骤,但这次从列表中选择 RUN NOW选项:

虽然这些仅仅是使用 pgAgent 来安排工作的一些功能,但还有许多其他可能性,例如,您可能想要安排一个工作,为您的数据库中的所有用户和角色创建备份,特别是使用 pg_dumpall --globals only 命令。

结论

pgAgent 有助于安排各种任务,这可能有助于减少一些更平凡但必要的日常任务,例如持续地备份您的数据库。一旦您更舒适地使用该工具,您甚至可以尝试创建一个多步任务或SQL任务,需要复杂的日程安排。