在Node.js上为PostgreSQL设置数据库维护计划

随着应用程序开发的发展，数据库成为大多数应用程序的核心，存储和管理着对数字业务至关重要的数据。随着数据的增长和日益复杂，确保数据库的效率对于满足应用程序的需求至关重要。

这就是数据库维护的意义所在。数据库维护包括清理、备份和优化索引以提高性能等任务。

本文提供了有关维护触发器的宝贵见解，并介绍了实用的设置说明。它解释了使用 实施各种数据库维护任务（如备份数据、重建索引、归档和数据清理）的过程，并在 应用程序中集成了 API 触发器。

了解触发器

在为数据库构建维护操作之前，了解触发操作的各种方式非常重要。每种触发器在促进维护任务方面都有不同的作用。常用的三种主要触发器是：

• 手动，基于 API：这种触发器可让您使用 API 调用运行一次性操作。它在性能突然下降时恢复数据库备份或重建索引等情况下非常有用。
• 计划（如 CRON）：此触发器可让您在用户流量较低时自动执行计划维护活动。它非常适合运行归档和清理等资源密集型操作。您可以使用 等软件包在 Node.js 中设置时间表，以便在需要时自动触发操作。
• 数据库通知：此触发器可让您根据数据库变化执行维护操作。例如，当用户在平台上发表评论时，保存的数据会立即触发对不规则字符、冒犯性语言或表情符号的检查。使用 等软件包可以在 Node.js 中实现这一功能。

前提条件

要学习本指南，您的本地计算机上应安装以下工具：

• ：用于管理应用程序源代码的版本控制
• ：用于构建后端应用程序
• ：使用终端与远程 PostgreSQL 数据库交互
• （可选）：使用图形用户界面（GUI）与远程 PostgreSQL 数据库交互。

创建和托管 Node.js 应用程序

让我们建立一个 Node.js 项目，将其提交到 GitHub，并设置自动部署管道到服务器。您还需要在服务器上配置 PostgreSQL 数据库，以便在其中测试您的维护例程。

首先，使用以下命令在本地系统上创建一个新目录：

mkdir node-db-maintenance

然后，切换到新创建的文件夹，运行以下命令创建一个新项目：

cd node-db-maintenance
yarn init -y # or npm init -y

这会使用默认配置为您初始化一个 Node.js 项目。现在，您可以运行以下命令安装必要的依赖项：

yarn add express pg nodemon dotenv

下面是每个软件包的简要说明：

• express：允许您设置基于 Express 的 REST API。
• pg：允许您通过 Node.js 应用程序与 PostgreSQL 数据库交互。
• nodemon：允许在开发应用程序的同时更新开发构建，使你不必在每次更改时都要停止和启动应用程序。
• dotenv：允许将 .env 文件中的环境变量加载到 process.env 对象中。

接下来，在 package.json 文件中添加以下脚本，以便轻松启动开发服务器，并在生产环境中运行服务器：

{
// ... 
"scripts": {
"start-dev": "nodemon index.js",
"start": "NODE_ENV=production node index.js"
},
// …
}

现在，您可以创建一个包含应用程序源代码的 index.js 文件。将以下代码粘贴到文件中：

const express = require("express")
const dotenv = require('dotenv');
if (process.env.NODE_ENV !== 'production') dotenv.config();
const app = express()
const port = process.env.PORT || 3000
app.get("/health", (req, res) => res.json({status: "UP"}))
app.listen(port, () => {
console.log(`Server running at port: ${port}`);
});

上面的代码初始化 服务器，如果不在生产模式下，则使用 dotenv 软件包配置环境变量。它还设置了一个 /health 路由，返回一个 *** ON 对象 {status: "UP"}。最后，它会使用 app.listen() 函数启动应用程序，以监听指定的端口，如果没有通过环境变量提供端口，则默认为 3000 。

现在，基本的应用程序已经就绪，请使用首选的 git 提供商（、 或 ）初始化一个新的 git 仓库，然后推送代码。Kinsta 支持从所有这些 git 提供商部署应用程序。本文将使用 GitHub。

仓库准备就绪后，请按照以下步骤将应用程序部署到 Kinsta：

1. 登录或创建账户，查看 面板。
2. 使用 Git 提供商授权 Kinsta。
3. 在左侧边栏单击 Applications，然后单击 Add application。
4. 选择要部署的版本库和分支。
5. 从 35 个选项列表中选择一个可用的数据中心位置。Kinsta 会通过 Nixpacks 自动检测应用程序的构建设置。
6. 选择应用程序资源，如 RAM 和磁盘空间。
7. 单击 Create application。

注：不同服务器提供商的部署步骤不尽相同。

部署完成后，复制已部署应用程序的链接并导航到 /health。您应该会在浏览器中看到以下 *** ON：

{status: "UP"}

这表明应用程序已正确设置。

在 Kinsta 上设置 PostgreSQL 实例

Kinsta 提供了配置数据库实例的简单接口。如果还没有 Kinsta 账户，请先创建一个新账户。然后，按照下面的步骤操作：

1. 登录 MyKinsta 面板。
2. 在左侧边栏单击 Databases，然后单击 Add database。
3. 选择 PostgreSQL 作为数据库类型，并选择您喜欢的版本。为数据库选择一个名称，并根据需要修改用户名和密码。
4. 从 35 个选项列表中选择一个数据中心位置。
5. 选择数据库大小。
6. 单击 Create database。

创建数据库后，确保检索数据库主机、端口、用户名和密码。

Kinsta 生成的数据库凭证

然后，你就可以在 psql CLI（或 PGAdmin GUI）中插入这些值来管理数据库。要在本地测试代码，请在项目根目录下创建一个 .env 文件，并在其中存储以下私钥：

DB_USER_NAME=your database user name
DB_HOST=your database host
DB_DATABASE_NAME=your database’s name
DB_PORT=your database port
PGPASS=your database password

部署到 Kinsta 时，需要将这些值作为环境变量添加到应用程序部署中。

要准备数据库操作，请下载并执行以创建表格（用户、帖子、评论）并插入示例数据。使用下面的命令，将占位符替换为具体内容，将数据添加到新创建的 PostgreSQL 数据库中：

psql -h <host> -p <port> -U <username> -d <db_name> -a -f <sql file e.g. test-data.sql>

请务必在上述命令中输入准确的文件名和路径。执行该命令时会提示输入数据库密码以获得授权。

该命令运行完成后，您就可以开始编写数据库维护操作了。完成每个操作后，请随时将代码推送到 Git 仓库，以便在 Kinsta 平台上查看其运行情况。

编写维护例程

本节介绍维护 PostgreSQL 数据库的多种常用操作。

1. 创建备份

定期备份数据库是一项常见的基本操作。它包括创建整个数据库内容的副本，并将其存储在安全位置。这些备份对于在数据意外丢失或出现影响数据完整性的错误时恢复数据至关重要。

虽然大部分服务器云平台将自动备份作为其服务的一部分，但在需要时，了解如何设置自定义备份例程也很重要。

PostgreSQL 提供用于创建数据库备份的工具 。不过，它需要直接从命令行运行，而且没有 npm 软件包。因此，您需要使用 软件包在 Node 应用程序的本地环境中运行 pg_dump 命令。

运行以下命令安装该软件包：

yarn add @getvim/execute

接下来，在 index.js 文件顶部添加这行代码，导入软件包：

const {execute} = require('@getvim/execute');

备份会以文件形式在 Node 应用程序的本地文件系统中生成。因此，更好在项目根目录中为备份创建一个专用目录，命名为 backup。

现在，您可以使用以下路径在需要时生成和下载数据库备份：

app.get('/backup', async (req, res) => {
// Create a name for the backup file
const fileName = "database-backup-" + new Date().valueOf() + ".tar";
// Execute the pg_dump command to generate the backup file
execute("PGPASSWORD=" + process.env.PGPASS  + " pg_dump -U " + process.env.DB_USER_NAME 
+ " -d " + process.env.DB_DATABASE_NAME 
+ " -h " + process.env.DB_HOST
+ " -p " + process.env.DB_PORT
+ " -f backup/" + fileName + " -F t"
).then(async () => {
console.log("Backup created");
res.redirect("/backup/" + fileName)
}).catch(err => {
console.log(err);
res.json({message: "Something went wrong"})
})
})

另外，在初始化 Express 应用程序后，需要在 index.js 文件的开头添加以下一行：

app.use('/backup', express.static('backup'))

这样就可以使用 express.static 中间件函数静态提供 backup 文件夹，让用户从 Node 应用程序中下载生成的备份文件。

提示：如果遇到 PostgreSQL 服务器版本不匹配错误（ server version mi *** atch ），这意味着数据库版本与本地 pg_dump 工具不同。请检查您的 pg_dump 版本，并更新工具或数据库以匹配，从而解决这个问题。

2. 从备份恢复

Postgres 允许使用 pg_restore 命令行工具从备份中还原。不过，你必须像使用 pg_dump 命令那样，通过 execute 包来使用它。下面是路径代码：

app.get('/restore', async (req, res) => {
const dir = 'backup'
// Sort the backup files according to when they were created
const files = fs.readdirSync(dir)
.filter((file) => fs.lstatSync(path.join(dir, file)).isFile())
.map((file) => ({ file, mtime: fs.lstatSync(path.join(dir, file)).mtime }))
.sort((a, b) => b.mtime.getTime() - a.mtime.getTime());
if (!files.length){
res.json({message: "No backups available to restore from"})
}
const fileName = files[0].file
// Restore the database from the chosen backup file
execute("PGPASSWORD=" + process.env.PGPASS  + " pg_restore -cC "
+ "-U " + process.env.DB_USER_NAME
+ " -h " + process.env.DB_HOST
+ " -p " + process.env.DB_PORT
+ " -d postgres backup/" + fileName
)
.then(async ()=> {
console.log("Restored");
res.json({message: "Backup restored"})
}).catch(err=> {
console.log(err);
res.json({message: "Something went wrong"})
})
})

上面的代码段首先查找存储在本地 backup 目录中的文件。然后，按创建日期排序，找到最新的备份文件。最后，使用 execute 包还原所选的备份文件。

请确保在 index.js 文件中添加以下导入，以便导入访问本地文件系统所需的模块，使函数能够正确运行：

const fs = require('fs')
const path = require('path')

3. 重建索引

Postgres 表的索引有时会损坏，导致数据库性能下降。这可能是由于软件缺陷或错误造成的。有时，由于空页或接近空页过多，索引也会变得臃肿。

在这种情况下，需要重建索引，以确保从 Postgres 实例中获得更佳性能。

Postgres 为此提供了 命令。你可以使用 软件包运行该命令（稍后还可以运行其他一些操作），因此请先运行以下命令安装该软件包：

yarn add pg

接下来，在 index.js 文件顶部的导入下面添加以下几行，以正确初始化数据库连接：

const {Client} = require('pg')
const client = new Client({
user: process.env.DB_USER_NAME,
host: process.env.DB_HOST,
database: process.env.DB_DATABASE_NAME,
password: process.env.PGPASS,
port: process.env.DB_PORT
})
client.connect(err => {
if (err) throw err;
console.log("Connected!")
})

该操作的实现非常简单：

app.get("/reindex", async (req, res) => {
// Run the REINDEX command as needed
await client.query("REINDEX TABLE Users;")
res.json({message: "Reindexed table successfully"})
})

上面显示的命令会重新索引整个用户表。您可以根据需要自定义该命令，以重建特定索引，甚至重新索引整个数据库。

4. 数据归档和清理

对于随着时间推移而不断扩大的数据库（历史数据很少被访问）来说，设置一些例程将旧数据卸载到数据湖中，以便更方便地存储和处理，可能是有意义的。

在许多数据湖中，Parquet 文件是数据存储和传输的通用标准。使用 库，你可以从 Postgres 数据中创建 Parquet 文件，并使用 等服务直接读取它们，而无需在将来将它们加载回数据库。

运行以下命令安装 Parquet *** 库：

yarn add parquetjs

创建归档时，需要从表中查询大量记录。在应用程序内存中存储如此大量的数据可能会耗费大量资源，成本高昂，而且容易出错。

因此，使用 从数据库中加载大块数据并对其进行处理是合理的。运行以下命令，安装 node-postgres 软件包中的 cursors 模块：

yarn add pg-cursor

接下来，确保将这两个库导入到你的 index.js 文件中：

const Cursor = require('pg-cursor')
const parquet = require('parquetjs')

现在，你可以使用下面的代码片段从数据库中创建 parquet 文件：

app.get('/archive', async (req, res) => {
// Query all comments through a cursor, reading only 10 at a time
// You can change the query here to meet your requirements, such as archiving records older than at least a month, or only archiving records from inactive users, etc.
const queryString = "SELECT * FROM COMMENTS;"
const cursor = client.query(new Cursor(queryString))
// Define the schema for the parquet file
let schema = new parquet.ParquetSchema({
comment_id: { type: 'INT64' },
post_id: { type: 'INT64' },
user_id: { type: 'INT64' },
comment_text: { type: 'UTF8' },
timestamp: { type: 'TIMESTAMP_MILLIS' }
});
// Open a parquet file writer
let writer = await parquet.ParquetWriter.openFile(schema, 'archive/archive.parquet');
let rows = await cursor.read(10)
while (rows.length > 0) {
for (let i = 0; i < rows.length; i++) {
// Write each row from table to the parquet file
await writer.appendRow(rows[i])
}
rows = await cursor.read(10)
}
await writer.close()
// Once the parquet file is generated, you can consider deleting the records from the table at this point to free up some space
// Redirect user to the file path to allow them to download the file
res.redirect("/archive/archive.parquet")
})

接下来，在 Express 应用程序初始化后，将以下代码添加到 index.js 文件的开头：

app.use('/archive', express.static('archive'))

这样，archive 文件夹就可以静态提供服务，让你可以从服务器下载生成的 parquet 文件。

别忘了在项目目录中创建一个 archive 文件目录来存储归档文件。

您可以进一步自定义此代码片段，以自动将镶块文件上传到 AWS S3 存储桶，并使用 CRON 作业自动触发例行操作。

5. 数据清理

运行数据库维护操作的一个常见目的是清理随着时间推移而变得陈旧或不相关的数据。本节将讨论在维护过程中进行数据清理的两种常见情况。

实际上，您可以根据应用程序数据模型的需要设置自己的数据清理例程。以下示例仅供参考。

按时间（上次修改或上次访问）删除记录

与本列表中的其他操作相比，根据记录年龄清理记录相对简单。你可以编写一个删除查询，删除比设定日期更早的记录。

下面是一个删除 2023 年 10 月 9 日之前的评论的示例：

app.get("/clean-by-age", async (req, res) => {
// Filter and delete all comments that were made on or before 9th October, 2023
const result = await client.query("DELETE FROM COMMENTS WHERE timestamp < '09-10-2023 00:00:00'")
if (result.rowCount > 0) {
res.json({message: "Cleaned up " + result.rowCount + " rows successfully!"})
} else {
res.json({message: "Nothing to clean up!"})
}
})

你可以通过向 /clean-by-age 路由发送 GET 请求来尝试一下。

根据自定义条件删除记录

您还可以根据其他条件设置清理，例如删除与系统中其他活动记录没有关联的记录（创建orphan情况）。

例如，您可以设置一个清理操作，查找与已删除的帖子相链接的评论，并将其删除，因为这些评论可能永远不会再出现在应用程序中：

app.get('/conditional',  async (req, res) => {
// Filter and delete all comments that are not linked to any active posts
const result = await client.query("DELETE FROM COMMENTS WHERE post_id NOT IN (SELECT post_id from Posts);")
if (result.rowCount > 0) {
res.json({message: "Cleaned up " + result.rowCount + " rows successfully!"})
} else {
res.json({message: "Nothing to clean up!"})
}
})

您可以根据自己的使用情况提出特定条件。

6. 数据库维护操作

数据库维护操作还可用于进行数据操作和转换，例如审查淫秽语言或将文本组合转换为表情符号。

与大多数其他操作不同，这些操作更好在数据库更新时运行（而不是在每周或每月的固定时间对所有行运行）。

本节列出了两个此类操作，但任何其他自定义操作的实现都与这些操作非常相似。

将文本转换为表情符号

您可以考虑将”:) “和 “xD “等文本组合转换为实际的表情符号，以提供更好的用户体验并保持信息的一致性。下面的代码片段可以帮助你实现这一目标：

app.get("/emoji", async (req, res) => {
// Define a list of emojis that need to be converted
const emojiMap = {
xD: '',
':)': '',
':-)': '',
':jack_o_lantern:': '',
':ghost:': '',
':santa:': '',
':christmas_tree:': '',
':gift:': '',
':bell:': '',
':no_bell:': '',
':tanabata_tree:': '',
':tada:': '',
':confetti_ball:': '',
':balloon:': ''
}
// Build the SQL query adding conditional checks for all emojis from the map
let queryString = "SELECT * FROM COMMENTS WHERE"
queryString += " COMMENT_TEXT LIKE '%" + Object.keys(emojiMap)[0] + "%' "
if (Object.keys(emojiMap).length > 1) {
for (let i = 1; i < Object.keys(emojiMap).length; i++) {
queryString += " OR COMMENT_TEXT LIKE '%" + Object.keys(emojiMap)[i] + "%' "
}
}
queryString += ";"
const result = await client.query(queryString)
if (result.rowCount === 0) {
res.json({message: "No rows to clean up!"})
} else {
for (let i = 0; i < result.rows.length; i++) {
const currentRow = result.rows[i]
let emoji
// Identify each row that contains an emoji along with which emoji it contains
for (let j = 0; j < Object.keys(emojiMap).length; j++) {
if (currentRow.comment_text.includes(Object.keys(emojiMap)[j])) {
emoji = Object.keys(emojiMap)[j]
break
}
}
// Replace the emoji in the text and update the row before moving on to the next row
const updateQuery = "UPDATE COMMENTS SET COMMENT_TEXT = '" + currentRow.comment_text.replace(emoji, emojiMap[emoji]) + "' WHERE COMMENT_ID = " + currentRow.comment_id + ";"
await client.query(updateQuery)
}
res.json({message: "All emojis cleaned up successfully!"})
}
})

该代码片段首先要求您定义一个表情符号及其文本表示的列表。然后，它会查询数据库以查找这些文字组合，并将其替换为表情符号。

审查淫秽语言

在允许用户生成内容的应用程序中，一个相当常见的操作是审查任何不雅语言。这里的 *** 与此类似–识别淫秽语言的实例并用星号字符替换它们。您可以使用 bad-words 软件包来轻松检查和审查亵渎语言。

运行以下命令安装该软件包：

yarn add bad-words

然后在 index.js 文件中初始化该软件包：

const Filter = require('bad-words');
filter = new Filter();

现在，使用以下代码段审查评论表中的淫秽内容：

app.get('/obscene', async (req, res) => {
// Query all comments using a cursor, reading only 10 at a time
const queryString = "SELECT * FROM COMMENTS;"
const cursor = client.query(new Cursor(queryString))
let rows = await cursor.read(10)
const affectedRows = []
while (rows.length > 0) {
for (let i = 0; i < rows.length; i++) {
// Check each comment for profane content
if (filter.isProfane(rows[i].comment_text)) {
affectedRows.push(rows[i])
}
}
rows = await cursor.read(10)
}
cursor.close()
// Update each comment that has profane content with a censored version of the text
for (let i = 0; i < affectedRows.length; i++) {
const row = affectedRows[i]
const updateQuery = "UPDATE COMMENTS SET COMMENT_TEXT = '" + filter.clean(row.comment_text) + "' WHERE COMMENT_ID = " + row.comment_id + ";"
await client.query(updateQuery)
}
res.json({message: "Cleanup complete"})
})

您可以在中找到本教程的完整代码。

了解 PostgreSQL 的 Vacuuming 及其目的

除了设置如上文讨论的自定义维护例程外，您还可以使用 PostgreSQL 提供的本机维护功能之一来确保数据库的持续健康和性能：。

Vacuum 进程有助于优化数据库性能和回收磁盘空间。PostgreSQL 使用自动 Vacuum 守护进程按计划运行 Vacuum 操作，但如果需要，也可以手动触发。以下是频繁吸尘的几种帮助方式：

• 恢复阻塞的磁盘空间：Vacuum 的主要目标之一是恢复数据库中被阻塞的磁盘空间。随着数据的不断插入、更新和删除，PostgreSQL可能会被仍占用磁盘空间的 “dead” 行或过时行所干扰。Vacuum 可以识别并删除这些死行，从而为新数据腾出空间。如果没有 Vacuum，磁盘空间将逐渐耗尽，可能导致性能下降甚至系统崩溃。
• 更新查询规划器指标：Vacuuming 还能帮助 PostgreSQL 保持查询规划器使用的最新统计数据和指标。查询规划器依靠准确的数据分布和统计信息来生成高效的执行计划。通过定期运行 Vacuum，PostgreSQL 可以确保这些指标是最新的，使其能够就如何检索数据和优化查询做出更好的决策。
• 更新可见性地图：可见性映射（Visibility Map）是PostgreSQL Vacuum过程的另一个重要方面。它有助于确定表中哪些数据块对所有事务完全可见，从而让 Vacuum 只针对必要的数据块进行清理。这可以更大限度地减少不必要的 I/O 操作，从而提高 Vacuum 进程的效率。
• 防止事务 ID 包络失败：Vacuum 在防止事务 ID 包络失败方面也发挥了关键作用。PostgreSQL 使用一个 32 位事务 ID 计数器，当它达到更大值时，就会导致缠绕。Vacuum 会将旧事务标记为 “frozen”，防止 ID 计数器缠绕并导致数据损坏。忽视这一点可能会导致灾难性的数据库故障。

如前所述，PostgreSQL 提供了两种执行 Vacuum 的选项： 和 。

自动真空是大多数情况下的推荐选择，因为它会根据预定义设置和数据库活动自动管理真空过程。另一方面，手动真空可提供更多控制，但需要对数据库维护有更深入的了解。

两者之间的选择取决于数据库规模、工作量和可用资源等因素。中小型数据库通常可以依赖自动真空，而大型或更复杂的数据库可能需要手动干预。

小结

数据库维护不仅仅是例行的内务管理，它还是一个健康和高性能应用程序的基础。通过定期优化、清理和整理数据，可以确保 PostgreSQL 数据库持续提供更高性能、不受损坏并高效运行，即使在应用程序扩展时也是如此。

在本综合指南中，我们探讨了在使用 Node.js 和 Express 时为 PostgreSQL 建立结构合理的数据库维护计划的重要性。

我们是否遗漏了您为数据库实施的任何例行数据库维护操作？或者您知道实施上述操作的更好 *** 吗？欢迎在评论中告诉我们！