PostgreSQL 提供強大的命令列工具 psql，讓開發者能有效管理資料函式庫。psql 不僅具備 SQL 查詢功能，還提供 meta-commands 查詢資料函式庫物件資訊，例如 \dt 可列出所有資料表、\d 顯示物件細節。此外，psql 的 \copy 命令支援資料匯入匯出，方便資料函式庫與外部檔案互動。本文將詳述 psql 的使用方法，並示範如何結合 \copy 命令與 CSV 檔案進行資料操作，提升 PostgreSQL 資料函式倉管理效率。

PostgreSQL 資料函式倉管理與資料匯入匯出技術解析

在進行 PostgreSQL 資料函式倉管理工作時，熟悉使用 psql 命令列工具是至關重要的。psql 不僅提供了豐富的 meta-commands 來管理資料函式庫物件，如資料表、檢視表等，還支援資料的匯入與匯出操作。本文將探討 psql 的使用方法，包括其 meta-commands、資料匯入匯出技術等。

使用 `psql` 查詢資料函式庫資訊

psql 提供了一系列的 meta-commands，讓使用者能夠方便地查詢資料函式庫物件的資訊。例如，使用 \dt 命令可以列出目前資料函式庫中的所有資料表。如果需要更詳細的資訊，可以在命令後面加上 + 符號，如 \dt+。此外，還可以透過指定模式（pattern）來過濾輸出結果，例如 \dt us* 將只顯示名稱以 “us” 開頭的資料表。

常用的 `psql` meta-commands

命令	描述
`\d [pattern]`	顯示物件的欄位、資料型別等資訊
`\di [pattern]`	顯示索引及其相關的資料表
`\dt [pattern]`	顯示資料表及其擁有者
`\du [pattern]`	顯示使用者帳號及其屬性
`\dv [pattern]`	顯示檢視表及其擁有者
`\dx [pattern]`	顯示已安裝的擴充套件
`\l [pattern]`	顯示資料函式庫

資料匯入與匯出

在進行資料函式倉管理工作時，經常需要將資料從一個檔案匯入到資料函式庫中，或者將資料函式庫中的資料匯出到檔案中。psql 提供了 \copy 命令來實作這兩個功能。

使用 `\copy` 命令

\copy 命令與 PostgreSQL 的 COPY 命令類別似，但它允許使用者在本地機器與遠端伺服器之間傳輸資料。例如，要將資料從一個 CSV 檔案匯入到 state_regions 資料表中，可以使用以下命令：

\copy state_regions FROM 'C:\YourDirectory\state_regions.csv' WITH (FORMAT CSV, HEADER);

同樣地，也可以使用 \copy TO 命令將資料表中的資料匯出到檔案中。

使用 `psql` 命令列工具匯入資料

除了在 psql 互動式環境中使用 \copy 命令外，也可以直接在命令列中使用 psql 工具來執行 SQL 命令或 meta-commands。例如，以下命令將從一個 CSV 檔案中匯入資料到 state_regions 資料表中：

psql -d analysis -U postgres -c 'COPY state_regions FROM STDIN WITH (FORMAT CSV, HEADER);' < C:\YourDirectory\state_regions.csv

這個命令首先連線到 analysis 資料函式庫，然後執行 COPY 命令將資料從指定的 CSV 檔案匯入到 state_regions 資料表中。

結語

本文介紹瞭如何使用 psql 工具來管理 PostgreSQL 資料函式庫，包括查詢資料函式庫物件資訊、匯入與匯出資料等。熟練掌握這些技巧對於高效地進行資料函式倉管理工作具有重要意義。無論是使用 psql 的 meta-commands 還是直接在命令列中執行 SQL 命令，都能夠幫助使用者更好地管理和維護 PostgreSQL 資料函式庫。

內容解密：

psql 工具的重要性：作為 PostgreSQL 的命令列工具，psql 提供了一系列強大的功能來幫助使用者管理資料函式庫。
meta-commands 的使用：透過使用 \dt、\di 等 meta-commands，可以方便地查詢資料函式庫物件的資訊。
\copy 命令的使用：\copy 命令允許使用者在本地機器與遠端伺服器之間傳輸資料，是進行資料匯入匯出的有力工具。
在命令列中使用 psql：直接在命令列中使用 psql 工具執行 SQL 命令或 meta-commands，能夠實作自動化管理任務。

未來趨勢與實務應用評估

隨著雲端運算和大資料技術的發展，PostgreSQL 資料函式庫的管理和維護工作變得越來越重要。未來，我們可以預見 psql 工具將繼續扮演關鍵角色，幫助開發者和 DBA 高效地管理 PostgreSQL 資料函式庫。同時，結合自動化指令碼和工具，將進一步簡化資料函式倉管理任務，提高工作效率。

使用psql進行資料函式庫操作與管理

匯入CSV檔案至資料函式庫

在進行資料分析時，經常需要將外部資料匯入至資料函式庫中。psql提供了簡便的方式來匯入CSV檔案。使用\copy meta-command可以將CSV檔案匯入至指定的資料表中。

\copy state_regions FROM 'state_regions.csv' WITH (FORMAT csv, HEADER);

內容解密：

\copy：用於將外部檔案匯入至資料表中的meta-command。
state_regions：目標資料表的名稱。
FROM 'state_regions.csv'：指定要匯入的CSV檔案路徑。
WITH (FORMAT csv, HEADER)：指設定檔案格式為CSV，並包含標頭列。

值得注意的是，這裡使用的是相對路徑，如果檔案不在當前目錄下，需要提供完整的路徑。此外，也可以使用<符號直接將檔案內容導向至psql，但這需要提供完整的檔案路徑。

將查詢結果儲存至檔案

在某些情況下，我們需要將查詢結果儲存至檔案中，以便進一步分析或存檔。psql提供了\o meta-command來實作這一功能。

\pset format csv
SELECT * FROM grades ORDER BY student_id, course_id;
\o 'C:/YourDirectory/query_output.csv'
SELECT * FROM grades ORDER BY student_id, course_id;

內容解密：

\pset format csv：設定輸出格式為CSV。
SELECT * FROM grades ORDER BY student_id, course_id;：執行查詢並輸出結果。
\o 'C:/YourDirectory/query_output.csv'：指定將接下來的查詢結果輸出至指定的CSV檔案中。
在Windows系統中，檔案路徑需要使用正斜線（/）或雙反斜線（\）。

從檔案執行SQL查詢

將SQL查詢儲存至檔案中，可以方便地重複執行查詢或進行自動化任務。使用psql命令列工具，可以直接執行儲存的SQL檔案。

psql -d analysis -U postgres -f C:\YourDirectory\display-grades.sql

內容解密：

psql：PostgreSQL的命令列工具。
-d analysis：指定要連線的資料函式庫名稱。
-U postgres：指定連線的使用者名稱。
-f C:\YourDirectory\display-grades.sql：指定要執行的SQL檔案路徑。

使用命令列工具進行資料函式倉管理

PostgreSQL提供了多種命令列工具，以方便管理資料函式庫。其中，createdb用於建立新的資料函式庫，而shp2pgsql則用於將Shapefile匯入至支援PostGIS的資料函式庫中。

使用createdb建立資料函式庫

createdb -U postgres -e box_office

內容解密：

createdb：建立新資料函式庫的命令列工具。
-U postgres：指定連線的使用者名稱。
-e：輸出建立資料函式庫的SQL命令。
box_office：新資料函式庫的名稱。

使用shp2pgsql匯入Shapefile

shp2pgsql -I -s 4269 -W Latin1 tl_2019_us_county.shp us_counties_2019_shp | psql -d analysis -U postgres

內容解密：

shp2pgsql：將Shapefile匯入至PostGIS資料函式庫的工具。
-I：在幾何欄位上建立GiST索引。
-s 4269：指定SRID（空間參考系統識別碼）。
-W Latin1：指定編碼方式。
tl_2019_us_county.shp：Shapefile的檔案名稱。
us_counties_2019_shp：目標資料表的名稱。
|：將shp2pgsql的輸出導向至psql。
psql -d analysis -U postgres：指定目標資料函式庫和使用者名稱。

資料函式庫維護與自訂 PostgreSQL

在結束 SQL 的探索之前，我們將介紹關鍵的資料函式庫維護任務和自訂 PostgreSQL 的選項。在本章中，您將學習如何追蹤和節省資料函式庫空間、更改系統設定，以及如何備份和還原資料函式庫。這些任務的執行頻率取決於您目前的角色和興趣。如果您想成為資料函式倉管理員或後端開發人員，本章涵蓋的主題至關重要。

值得注意的是，資料函式庫維護和效能調校是龐大的主題，通常佔據了整本文的篇幅，本章主要作為幾個基本要點的介紹。如果您想了解更多，可以從附錄中的資源開始。

使用 VACUUM 還原未使用的空間

PostgreSQL 的 VACUUM 命令有助於管理資料函式庫的大小，正如第 10 章「更新大型表格時的效能改進」中討論的那樣，資料函式庫可能會因日常操作而增長。

例如，當您更新一個行的值時，資料函式庫會建立該行的新版本，並保留（但隱藏）舊版本的行。PostgreSQL 檔案將這些不可見的行稱為「死元組」（dead tuples），而元組（tuples）是 PostgreSQL 資料函式庫中行的內部實作名稱。當您刪除一個行時，也會發生同樣的情況。儘管該行不再對您可見，但它仍然作為死行存在於表格中。

這是設計上的安排，以便資料函式庫在多個事務同時發生的環境中提供某些功能，並且可能需要舊版本的行用於當前事務以外的其他事務。

VACUUM 命令會清理這些死行。單獨執行 VACUUM 會將死行佔用的空間標記為可供資料函式庫再次使用（假設使用這些行的任何事務都已完成）。在大多數情況下，VACUUM 不會將空間傳回給系統磁碟；它只是將該空間標記為可用於新資料。要實際縮小資料檔案的大小，可以執行 VACUUM FULL，它會將表格重寫為不包含死行空間的新版本，並丟棄舊版本。

雖然 VACUUM FULL 可以釋放系統磁碟上的空間，但有幾點需要注意。首先，VACUUM FULL 比 VACUUM 需要更多時間來完成。其次，它必須在重寫表格時對表格具有獨佔存取許可權，這意味著在操作期間無法更新資料。普通的 VACUUM 命令可以在更新和其他操作正在進行時執行。最後，並非表格中的所有死空間都是壞事。在許多情況下，擁有可用空間來放置新元組，而不是需要向作業系統請求更多磁碟空間，可以提高效能。

您可以按需執行 VACUUM 或 VACUUM FULL，但預設情況下，PostgreSQL 會執行自動清理背景程式，監視資料函式庫並根據需要執行 VACUUM。本章稍後將介紹如何監視自動清理以及手動執行 VACUUM 命令。

追蹤表格大小

我們將建立一個小型測試表格，並在填充資料和執行更新時監視其增長。本練習的程式碼與本文的所有資源一樣，可在 https://nostarch.com/practical-sql-2nd-edition/ 上找到。

建立表格並檢查其大小

清單 19-1 建立了一個具有單一列的 vacuum_test 表格，用於儲存整數。執行程式碼，然後我們將測量表格的大小。

CREATE TABLE vacuum_test (
    integer_column integer
);

清單 19-1：建立一個表格來測試清理

在填充表格測試資料之前，讓我們檢查它在磁碟上佔用的空間，以建立參考點。我們可以透過兩種方式做到這一點：透過 pgAdmin 介面檢查表格屬性，或使用 PostgreSQL 管理功能執行查詢。在 pgAdmin 中，按一下表格以突出顯示，然後按一下「統計」標籤。表格大小是清單中的約二十多個指標之一。

這裡我將重點介紹執行查詢的技術，因為如果 pgAdmin 不可用或您正在使用其他圖形使用者介面（GUI），瞭解這些查詢會很有幫助。清單 19-2 顯示瞭如何使用 PostgreSQL 函式檢查 vacuum_test 表格的大小。

SELECT 
    pg_size_pretty(
        pg_total_relation_size('vacuum_test')
    );

清單 19-2：確定 vacuum_test 的大小

最外層的函式 pg_size_pretty() 將位元組轉換為更容易理解的格式，如千位元組、兆位元組或吉位元組。內部的 pg_total_relation_size() 函式報告表格、其索引和任何離線壓縮資料所佔用的位元組數。#### 內容解密：

pg_size_pretty()：此函式將位元組大小轉換為更易讀的格式，例如 KB、MB 或 GB，讓使用者更容易理解目前的資料大小。
pg_total_relation_size('vacuum_test')：此函式計算 vacuum_test 表格及其相關索引所佔用的總空間，單位為位元組。傳回的結果再由 pg_size_pretty() 處理，以更友善的方式顯示大小。

#### VACUUM 操作流程

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title PostgreSQL資料函式倉管理與資料匯入匯出

package "資料庫架構" {
    package "應用層" {
        component [連線池] as pool
        component [ORM 框架] as orm
    }

    package "資料庫引擎" {
        component [查詢解析器] as parser
        component [優化器] as optimizer
        component [執行引擎] as executor
    }

    package "儲存層" {
        database [主資料庫] as master
        database [讀取副本] as replica
        database [快取層] as cache
    }
}

pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中

master --> replica : 資料同步

note right of cache
  Redis/Memcached
  減少資料庫負載
end note

@enduml

此圖示說明瞭 VACUUM 的基本操作流程，包括檢查死元組、標記空間以及決定是否需要執行 VACUUM FULL。

追蹤表格成長與管理

為了進一步理解 VACUUM 的作用，我們建立了一個測試表格並監控其在資料填充和更新過程中的變化。這讓我們能夠觀察到 PostgreSQL 如何處理資料變更並管理儲存空間。

為什麼需要 VACUUM

當資料被更新或刪除時，PostgreSQL 並不會立即釋放舊資料所佔用的空間，而是將其標記為「死元組」。這些「死元組」佔據了儲存空間，並且在某些情況下可能會導致效能下降。因此，定期執行 VACUUM 操作對於保持資料函式庫的高效運作至關重要。

如何使用 VACUUM

您可以手動執行 VACUUM 或設定自動清理程式來管理資料函式庫大小。手動執行 VACUUM 可以清理死元組，而 VACUUM FULL 則能夠進一步回收空間，但需要更多的時間和獨佔存取許可權。

PostgreSQL 資料表大小與 VACUUM 指令的關聯

在 PostgreSQL 中，瞭解資料表的大小以及如何管理它對於資料函式倉管理員來說是非常重要的。本篇文章將探討當資料表被更新時，其大小會如何變化，以及如何使用 VACUUM 指令來管理這些變化。

檢查資料表的初始大小

首先，我們需要建立一個測試用的資料表 vacuum_test，並檢查其初始大小。執行以下 SQL 指令：

CREATE TABLE vacuum_test (
    integer_column integer
);
SELECT pg_size_pretty(pg_table_size('vacuum_test'));

內容解密：

CREATE TABLE vacuum_test 建立了一個名為 vacuum_test 的新資料表，其中包含一個整數欄位 integer_column。
pg_table_size('vacuum_test') 函式用於取得 vacuum_test 資料表的大小（以 bytes 為單位）。
pg_size_pretty() 函式將大小轉換為易讀的格式，如 KB、MB 或 GB。

執行後，應該會顯示 0 bytes，因為此時資料表是空的。

插入資料後檢查大小

接下來，我們使用 generate_series() 函式向 vacuum_test 資料表中插入 500,000 筆資料：

INSERT INTO vacuum_test
SELECT * FROM generate_series(1,500000);
SELECT pg_size_pretty(pg_table_size('vacuum_test'));

內容解密：

generate_series(1,500000) 產生一個包含 1 到 500,000 的整數序列。
將這個序列插入到 vacuum_test 的 integer_column 中。
再次檢查資料表的大小。

執行後，應該會看到類別似 17 MB 的輸出，表示資料表現在佔用了約 17MB 的磁碟空間。

更新資料後的大小變化

現在，讓我們更新 vacuum_test 中的所有資料，將 integer_column 的值加 1：

UPDATE vacuum_test
SET integer_column = integer_column + 1;
SELECT pg_size_pretty(pg_table_size('vacuum_test'));

內容解密：

UPDATE vacuum_test 更新了資料表中的所有列。
將 integer_column 的值替換為原值加 1。
再次檢查資料表的大小。

執行後，你會發現資料表的大小增加到了約 35 MB，幾乎是之前的兩倍。這個現象的原因是 PostgreSQL 在更新資料時，並不會直接覆寫原有的資料，而是寫入新的資料版本，而舊的版本則變成所謂的「死元組（dead tuples）」。這些死元組仍然佔用磁碟空間，直到被清理。

自動 VACUUM 程式

PostgreSQL 有一個自動執行的 VACUUM 程式，稱為 autovacuum，它會在背景監控資料函式庫，並在發現大量死元組時自動執行 VACUUM 清理操作。我們可以查詢 PostgreSQL 的統計資訊來檢視 autovacuum 的活動：

SELECT relname, last_vacuum, last_autovacuum, vacuum_count, autovacuum_count
FROM pg_stat_all_tables
WHERE relname = 'vacuum_test';

內容解密：

pg_stat_all_tables 是一個系統檢視，提供了各個資料表的統計資訊。
relname 是資料表的名稱。
last_vacuum 和 last_autovacuum 分別記錄了最後一次手動和自動 VACUUM 的時間。
vacuum_count 和 autovacuum_count 統計了手動和自動 VACUUM 的執行次數。

執行後，你應該會看到 autovacuum 的執行時間和次數。

手動執行 VACUUM

除了依賴 autovacuum 外，我們也可以手動執行 VACUUM：

VACUUM vacuum_test;

內容解密：

手動執行 VACUUM 清理指定的資料表。
這將標記死元組為可重用空間，但不一定會立即減少資料表的大小。

執行後，可以再次查詢資料表的大小和 VACUUM 統計資訊，以觀察變化。

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。

PostgreSQL資料函式倉管理與資料匯入匯出

PostgreSQL 資料函式倉管理與資料匯入匯出技術解析

使用 psql 查詢資料函式庫資訊

常用的 psql meta-commands

資料匯入與匯出

使用 \copy 命令

使用 psql 命令列工具匯入資料

結語

內容解密：

未來趨勢與實務應用評估

使用psql進行資料函式庫操作與管理

匯入CSV檔案至資料函式庫

內容解密：

將查詢結果儲存至檔案

內容解密：

從檔案執行SQL查詢

內容解密：

使用命令列工具進行資料函式倉管理

使用createdb建立資料函式庫

內容解密：

使用shp2pgsql匯入Shapefile

內容解密：

資料函式庫維護與自訂 PostgreSQL

使用 VACUUM 還原未使用的空間

追蹤表格大小

建立表格並檢查其大小

#### VACUUM 操作流程

追蹤表格成長與管理

為什麼需要 VACUUM

如何使用 VACUUM

PostgreSQL 資料表大小與 VACUUM 指令的關聯

檢查資料表的初始大小

內容解密：

插入資料後檢查大小

內容解密：

更新資料後的大小變化

內容解密：

自動 VACUUM 程式

內容解密：

手動執行 VACUUM

內容解密：

玄貓 BlackCat

使用 `psql` 查詢資料函式庫資訊

常用的 `psql` meta-commands

使用 `\copy` 命令

使用 `psql` 命令列工具匯入資料