在Node.js上為PostgreSQL設定資料庫維護計劃

隨著應用程式開發的發展，資料庫成為大多數應用程式的核心，儲存和管理著對數字業務至關重要的資料。隨著資料的增長和日益複雜，確保資料庫的效率對於滿足應用程式的需求至關重要。

這就是資料庫維護的意義所在。資料庫維護包括清理、備份和優化索引以提高效能等任務。

本文提供了有關維護觸發器的寶貴見解，並介紹了實用的設定說明。它解釋了使用 PostgreSQL 實施各種資料庫維護任務（如備份資料、重建索引、歸檔和資料清理）的過程，並在 Node.js 應用程式中整合了 API 觸發器。

瞭解觸發器

在為資料庫構建維護操作之前，瞭解觸發操作的各種方式非常重要。每種觸發器在促進維護任務方面都有不同的作用。常用的三種主要觸發器是：

• 手動，基於 API：這種觸發器可讓您使用 API 呼叫執行一次性操作。它在效能突然下降時恢復資料庫備份或重建索引等情況下非常有用。
• 計劃（如 CRON）：此觸發器可讓您在使用者流量較低時自動執行計劃維護活動。它非常適合執行歸檔和清理等資源密集型操作。您可以使用 node-schedule 等軟體包在 Node.js 中設定時間表，以便在需要時自動觸發操作。
• 資料庫通知：此觸發器可讓您根據資料庫變化執行維護操作。例如，當使用者在平臺上發表評論時，儲存的資料會立即觸發對不規則字元、冒犯性語言或表情符號的檢查。使用 pg-listen 等軟體包可以在 Node.js 中實現這一功能。

前提條件

要學習本指南，您的本地計算機上應安裝以下工具：

• Git：用於管理應用程式原始碼的版本控制
• Node.js：用於構建後端應用程式
• psql：使用終端與遠端 PostgreSQL 資料庫互動
• PGAdmin（可選）：使用圖形使用者介面（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}`);
});

上面的程式碼初始化 Express 伺服器，如果不在生產模式下，則使用 dotenv 軟體包配置環境變數。它還設定了一個 /health 路由，返回一個 JSON 物件 {status: "UP"}。最後，它會使用 app.listen() 函式啟動應用程式，以監聽指定的埠，如果沒有通過環境變數提供埠，則預設為 3000 。

現在，基本的應用程式已經就緒，請使用首選的 git 提供商（BitBucket、GitHub 或 GitLab）初始化一個新的 git 倉庫，然後推送程式碼。Kinsta 支援從所有這些 git 提供商部署應用程式。本文將使用 GitHub。

倉庫準備就緒後，請按照以下步驟將應用程式部署到 Kinsta：

1. 登入或建立賬戶，檢視 MyKinsta 面板。
2. 使用 Git 提供商授權 Kinsta。
3. 在左側邊欄單擊 Applications，然後單擊 Add application。
4. 選擇要部署的版本庫和分支。
5. 從 35 個選項列表中選擇一個可用的資料中心位置。Kinsta 會通過 Nixpacks 自動檢測應用程式的構建設定。
6. 選擇應用程式資源，如 RAM 和磁碟空間。
7. 單擊 Create application。

注：不同伺服器提供商的部署步驟不盡相同。

部署完成後，複製已部署應用程式的連結並導航到 /health。您應該會在瀏覽器中看到以下 JSON：

{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 時，需要將這些值作為環境變數新增到應用程式部署中。

要準備資料庫操作，請下載並執行此 SQL 指令碼以建立表格（使用者、帖子、評論）並插入示例資料。使用下面的命令，將佔位符替換為具體內容，將資料新增到新建立的 PostgreSQL 資料庫中：

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

請務必在上述命令中輸入準確的檔名和路徑。執行該命令時會提示輸入資料庫密碼以獲得授權。

該命令執行完成後，您就可以開始編寫資料庫維護操作了。完成每個操作後，請隨時將程式碼推送到 Git 倉庫，以便在 Kinsta 平臺上檢視其執行情況。

編寫維護例程

本節介紹維護 PostgreSQL 資料庫的多種常用操作。

1. 建立備份
2. 從備份恢復
3. 重建索引
4. 資料歸檔和清除
5. 資料清理
6. 資料操作

1. 建立備份

定期備份資料庫是一項常見的基本操作。它包括建立整個資料庫內容的副本，並將其儲存在安全位置。這些備份對於在資料意外丟失或出現影響資料完整性的錯誤時恢復資料至關重要。

雖然大部分伺服器雲平臺將自動備份作為其服務的一部分，但在需要時，瞭解如何設定自定義備份例程也很重要。

PostgreSQL 提供用於建立資料庫備份的工具 pg_dump。不過，它需要直接從命令列執行，而且沒有 npm 軟體包。因此，您需要使用 @getvim/execute 軟體包在 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 mismatch ），這意味著資料庫版本與本地 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 為此提供了 REINDEX 命令。你可以使用 node-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 檔案是資料儲存和傳輸的通用標準。使用 ParquetJS 庫，你可以從 Postgres 資料中建立 Parquet 檔案，並使用 AWS Athena 等服務直接讀取它們，而無需在將來將它們載入回資料庫。

執行以下命令安裝 ParquetJS 庫：

yarn add parquetjs

建立歸檔時，需要從表中查詢大量記錄。在應用程式記憶體中儲存如此大量的資料可能會耗費大量資源，成本高昂，而且容易出錯。

因此，使用 cursors 從資料庫中載入大塊資料並對其進行處理是合理的。執行以下命令，安裝 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"})
})

您可以在此 GitHub 程式碼庫中找到本教程的完整程式碼。

瞭解 PostgreSQL 的 Vacuuming 及其目的

除了設定如上文討論的自定義維護例程外，您還可以使用 PostgreSQL 提供的本機維護功能之一來確保資料庫的持續健康和效能：Vacuum 程序。

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 的選項：Autovacuum 和 Manual Vacuum。

自動真空是大多數情況下的推薦選擇，因為它會根據預定義設定和資料庫活動自動管理真空過程。另一方面，手動真空可提供更多控制，但需要對資料庫維護有更深入的瞭解。

兩者之間的選擇取決於資料庫規模、工作量和可用資源等因素。中小型資料庫通常可以依賴自動真空，而大型或更復雜的資料庫可能需要手動干預。

小結

資料庫維護不僅僅是例行的內務管理，它還是一個健康和高效能應用程式的基礎。通過定期優化、清理和整理資料，可以確保 PostgreSQL 資料庫持續提供最高效能、不受損壞並高效執行，即使在應用程式擴充套件時也是如此。

在本綜合指南中，我們探討了在使用 Node.js 和 Express 時為 PostgreSQL 建立結構合理的資料庫維護計劃的重要性。

我們是否遺漏了您為資料庫實施的任何例行資料庫維護操作？或者您知道實施上述操作的更好方法嗎？歡迎在評論中告訴我們！