PostgreSQL 16 Administration Cookbook 讀書筆記：第7章 Database Administration

編寫一個要么完全成功要么完全失敗的腳本

事務（transaction）可以實現all or nothing。不過這里指的是psql的-和--single-transaction選項。可以實現transaction wrapper：

此選項只能與一個或多個 -c 和/或 -f 選項組合使用。它會導致 psql 在第一個此類選項之前發出 BEGIN 命令，并在最后一個此類選項之后發出 COMMIT 命令，從而將所有命令包裝到單個事務中。這確保所有命令要么成功完成，要么不應用任何更改。

還有一些子句可以保證創建和刪除時避免報錯：

DROP … IF EXISTS
CREATE OR REPLACE … ：無需刪除依賴對象的對象，如function，procedure，view，rule，language，aggregate，type，materialized view
- CREATE … IF NOT EXISTS：CREATE OR REPLACE不支持的對象，基本就在這一類中

💡 索引不包含在SQL標準中，這是因為SQL 標準側重于數據定義、操作、約束和事務，即邏輯層面。而索引屬于物理實現。

PG中的事務是平（flat）的，不支持嵌套事務。

下例中，最終t1和t2成功創建，原因是COMMIT與第一個BEGIN匹配第二個COMMIT被拋棄。

BEGIN;create table t1(a int);BEGIN;create table t2(a int);COMMIT;
ROLLBACK;

嵌套事務可以間接的用SAVEPOINT實現：

BEGIN;
SAVEPOINT sp1;
-- Do something
ROLLBACK TO SAVEPOINT sp1;  -- if `Do something` failed, rollback to sp1 without rolling back the entire transaction
RELEASE SAVEPOINT sp1;      -- if `Do something` succeed, optional, remove the savepoint marker
COMMIT;

編寫一個在第一次出錯時退出的 psql 腳本

這里指的是psql的ON_ERROR_STOP選項：

默認情況下，命令處理在發生錯誤后會繼續。當此變量設置為 on 時，處理將立即停止。在交互模式下，psql 將返回到命令提示符；否則，psql 將退出并返回錯誤代碼 3，以區分此情況與致命錯誤情況（使用錯誤代碼 1 報告）。無論哪種情況，任何當前正在運行的腳本（頂級腳本（如果有）以及它可能調用的任何其他腳本）都將立即終止。

postgres=# \set
AUTOCOMMIT = 'on'
...
ON_ERROR_STOP = 'off'
...

使用 psql 變量

參見這里。

postgres=# \set table_name t1
postgres=# \echo :table_name
t1
postgres=# \set tabname :table_name
postgres=# \echo :tabname
t1^
postgres=# create table :tabname(a int);
CREATE TABLE

通過命令行設置，-v表示variable：

$ psql -v tabname=t1
psql (16.9)
Type "help" for help.postgres=# \echo :tabname
t1

將查詢輸出放入 psql 變量

利用psql的gset元命令。

\gset [PREFIX]
execute query and store result in psql variables

例：

sampledb=> select * from employees limit 1 \gset
sampledb=> \set
...
department_id = '90'
email = 'SKING'
employee_id = '100'
first_name = 'Steven'
hire_date = '2003-06-17'
job_id = 'AD_PRES'
last_name = 'King'
phone_number = '515.123.4567'
salary = '24000.00'

編寫一個條件 psql 腳本

Conditional\if EXPR               begin conditional block\elif EXPR             alternative within current conditional block\else                  final alternative within current conditional block\endif                 end conditional block

官網示例：

-- check for the existence of two separate records in the database and store
-- the results in separate psql variables
SELECTEXISTS(SELECT 1 FROM customer WHERE customer_id = 123) as is_customer,EXISTS(SELECT 1 FROM employee WHERE employee_id = 456) as is_employee
\gset
\if :is_customerSELECT * FROM customer WHERE customer_id = 123;
\elif :is_employee\echo 'is not a customer but is an employee'SELECT * FROM employee WHERE employee_id = 456;
\else\if yes\echo 'not a customer or employee'\else\echo 'this will never print'\endif
\endif

我的例子：

sampledb=> select (count(*)>100) as emp_gt_100 from employees \gset
sampledb=> \echo :emp_gt_100
t
\if :emp_gt_100
\echo Employee count is greater than 100
\else
\echo Employee count is not greater than 100
\endif

💡 psql 條件表達式后只接受布爾值。

調查 psql 錯誤

psql元命令errverbose：

以最大詳細程度重復最新的服務器錯誤消息，就像將 VERBOSITY 設置為verbose并將 SHOW_CONTEXT 設置為always一樣。

sampledb=> select abc from employees;
ERROR:  column "abc" does not exist
LINE 1: select abc from employees;^
sampledb=> \errverbose
ERROR:  42703: column "abc" does not exist
LINE 1: select abc from employees;^
LOCATION:  errorMissingColumn, parse_relation.c:3722

VERBOSITY 和 SHOW_CONTEXT 也可以單獨設置，以下為默認值：

sampledb=> \set
SHOW_CONTEXT = 'errors'
VERBOSITY = 'verbose'

SHOW_CONTEXT

此變量可以設置為 never、errors 或 always 來控制是否在服務器消息中顯示 CONTEXT 字段。默認值為 error（意味著上下文會顯示在錯誤消息中，但不會在通知或警告消息中顯示）。當 VERBOSITY 設置為 terse 或 sqlstate 時，這個設置無效。（另見 \errverbose，當你想得到剛剛收到的錯誤的詳細版本時使用。）

VERBOSITY

此變量可以設置為 default、verbose、terse 或 sqlstate 值，以控制錯誤報告的詳細程度。（另請參閱 \errverbose，用于獲取剛剛收到的錯誤的詳細版本。）

另請參見：PostgreSQL Error Codes 和 PostgreSQL source code。

使用有用的信息設置 psql 提示符

psql 發出的提示符可以根據您的偏好進行自定義。PROMPT1、PROMPT2 和 PROMPT3 三個變量包含描述提示符外觀的字符串和特殊轉義序列。PROMPT1 是 psql 請求新命令時發出的常規提示符。當在命令輸入過程中需要更多輸入時，例如，由于命令未以分號結尾或引號未閉合，則會發出PROMPT2 。當您運行 SQL COPY FROM STDIN 命令并且需要在終端上輸入行值時，會發出PROMPT3 。

所選提示符變量的值將按原樣打印，除非遇到百分號 (%)。詳見這里。

sampledb=> \set
...
PROMPT1 = '%/%R%x%# '
PROMPT2 = '%/%R%x%# '
PROMPT3 = '>> '
...

使用 pgAdmin 執行 DBA 任務

略。

定期后臺任務執行

pgAgent 是 PostgreSQL 的作業調度程序。pgAgent 使用 pgAdmin 進行管理。pgAdmin 文檔包含如何在 PostgreSQL 系統中設置和使用 pgAgent 的詳細信息。

簡單來說，pgAgent是運行在unix上的一個daemon，pgAdmin可以與其交互。詳見這里。

安裝：

sudo yum install pgagent_16
psql -c "create extension pgagent"

其中的函數如下：

postgres=# \dx+ pgagentObjects in extension "pgagent"Object description
-------------------------------------------------------------------------------------------------------------------------------------------------function pgagent.pga_exception_trigger()function pgagent.pgagent_schema_version()function pgagent.pga_is_leap_year(smallint)function pgagent.pga_job_trigger()function pgagent.pga_next_schedule(integer,timestamp with time zone,timestamp with time zone,boolean[],boolean[],boolean[],boolean[],boolean[])function pgagent.pga_schedule_trigger()sequence pgagent.pga_exception_jexid_seqsequence pgagent.pga_jobclass_jclid_seqsequence pgagent.pga_job_jobid_seqsequence pgagent.pga_joblog_jlgid_seqsequence pgagent.pga_jobstep_jstid_seqsequence pgagent.pga_jobsteplog_jslid_seqsequence pgagent.pga_schedule_jscid_seqtable pgagent.pga_exceptiontable pgagent.pga_jobtable pgagent.pga_jobagenttable pgagent.pga_jobclasstable pgagent.pga_joblogtable pgagent.pga_jobsteptable pgagent.pga_jobsteplogtable pgagent.pga_schedule
(21 rows)

對多個表執行操作

其實就是循環+動態腳本。

使用psql元命令gexec和格式化函數format。

format類似于C語言函數sprintf。

示例參見gexec in psql: PostgreSQL poweruser practice。

在表上添加/刪除列

sampledb=> create table t1(a int);
CREATE TABLE
sampledb=> insert into t1 values(1);
INSERT 0 1
sampledb=> alter table t1 add column b varchar(10);
ALTER TABLE
sampledb=> alter table t1 add column c varchar(10) default 'abc';
ALTER TABLE
sampledb=> \d t1Table "hr.t1"Column |         Type          | Collation | Nullable |         Default
--------+-----------------------+-----------+----------+--------------------------a      | integer               |           |          |b      | character varying(10) |           |          |c      | character varying(10) |           |          | 'abc'::character varyingsampledb=> select * from t1;a | b |  c
---+---+-----1 |   | abc
(1 row)sampledb=> alter table t1 drop column c;
ALTER TABLE
sampledb=> alter table t1 drop column b;
ALTER TABLE

更改列的數據類型

基本語法：

ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE

此形式更改表列的類型。涉及該列的索引和簡單表約束將通過重新解析原始提供的表達式自動轉換為使用新的列類型。可選的 COLLATE 子句指定新列的排序規則；如果省略，則排序規則為新列類型的默認排序規則。可選的 USING 子句指定如何根據舊列值計算新列值；如果省略，則默認轉換與從舊數據類型到新類型的賦值轉換相同。如果沒有從舊類型到新類型的隱式轉換或賦值轉換，則必須提供 USING 子句。
?
使用此形式時，列的統計信息將被刪除，因此建議隨后在表上運行 ANALYZE。

示例參見PostgreSQL - Change Column Type 和 PostgreSQL Change Column Type。

更改枚舉數據類型的定義

講的是ALTER TYPE命令。

示例針對Enumerated Types 。Composite Type也適用。

示例：

sampledb=> CREATE TYPE weekdays AS ENUM ('sun', 'mon', 'tue','wes', 'Fri', 'sat');
CREATE TYPE
sampledb=> ALTER TYPE weekdays RENAME VALUE 'wes' to 'wed';
ALTER TYPE
sampledb=> ALTER TYPE weekdays ADD VALUE 'thu' after 'wed';
ALTER TYPE
sampledb=> ALTER TYPE weekdays RENAME VALUE 'Fri' to 'fri';
ALTER TYPEsampledb=> \dT+ weekdaysList of data typesSchema |   Name   | Internal name | Size | Elements | Owner | Access privileges | Description
--------+----------+---------------+------+----------+-------+-------------------+-------------hr     | weekdays | weekdays      | 4    | sun     +| hr    |                   ||          |               |      | mon     +|       |                   ||          |               |      | tue     +|       |                   ||          |               |      | wed     +|       |                   ||          |               |      | thu     +|       |                   ||          |               |      | fri     +|       |                   ||          |               |      | sat      |       |                   |
(1 row)sampledb=> SELECT enumlabel
FROM pg_enum
WHERE enumtypid = 'weekdays'::regtype
ORDER BY enumsortorder;enumlabel
-----------sunmontuewedthufrisat
(7 rows)

異步添加約束以增加并發度

原理見這里。

語法為：ADD table_constraint [ NOT VALID ]

此形式使用與 CREATE TABLE 相同的約束語法，并附加 NOT VALID 選項（目前僅適用于外鍵約束和 CHECK 約束），向表添加新的約束。
通常，此形式將掃描表以驗證表中所有現有行是否滿足新約束。但如果使用 NOT VALID 選項，則會跳過此可能耗時的掃描。該約束仍將強制執行后續插入或更新操作（也就是說，除非引用表中存在匹配的行（對于外鍵），否則插入或更新操作將失敗；除非新行符合指定的檢查條件，否則插入或更新操作將失敗）。但是，數據庫不會假定該約束適用于表中的所有行，直到使用 VALIDATE CONSTRAINT 選項進行驗證為止。
?
雖然大多數形式的 ADD table_constraint 都需要 ACCESS EXCLUSIVE 鎖，但 ADD FOREIGN KEY 只需要 SHARE ROW EXCLUSIVE 鎖。請注意，ADD FOREIGN KEY 除了獲取聲明約束的表上的鎖之外，還會獲取被引用表上的 SHARE ROW EXCLUSIVE 鎖。
?
在分區表中添加唯一鍵或主鍵約束時，會受到其他限制；請參閱 CREATE TABLE。此外，目前分區表上的外鍵約束可能無法聲明為 NOT VALID。

ADD table_constraint NOT VALID 的目的如下：

掃描大型表以驗證新的外鍵或檢查約束可能需要很長時間，并且對該表的其他更新將被鎖定，直到提交 ALTER TABLE ADD CONSTRAINT 命令。NOT VALID 約束選項的主要目的是減少添加約束對并發更新的影響。使用 NOT VALID，ADD CONSTRAINT 命令不會掃描表并可以立即提交。之后，可以發出 VALIDATE CONSTRAINT 命令來驗證現有行是否滿足約束。驗證步驟不需要鎖定并發更新，因為它知道其他事務將對其插入或更新的行強制執行約束；只需要檢查預先存在的行。因此，驗證僅獲取正在修改的表上的 SHARE UPDATE EXCLUSIVE 鎖。（如果約束是外鍵，則約束引用的表上也需要行共享鎖。）除了提高并發性之外，在已知表包含預先存在的違規情況的情況下，使用 NOT VALID 和 VALIDATE CONSTRAINT 也很有用。一旦約束到位，就不能插入新的違規，并且可以在 VALIDATE CONSTRAINT 最終成功之前輕松地糾正現有問題。

添加/刪除模式

postgres=# create schema if not exists test;
CREATE SCHEMA
postgres=# grant select on all tables in schema test to public;
GRANT
postgres=# drop schema test;
DROP SCHEMA

在模式之間移動對象

語法為：

ALTER TABLE ... SET SCHEMA
-- ALTER TABLE myschema.distributors SET SCHEMA yourschema;

此形式將表移動到另一個模式。表列所擁有的關聯索引、約束和序列也將被移動。

添加/刪除表空間

在PG中，表空間就是一個目錄：

postgres=# \db+List of tablespacesName    |  Owner   |    Location    | Access privileges | Options |  Size   | Description
------------+----------+----------------+-------------------+---------+---------+-------------pg_default | postgres |                |                   |         | 484 MB  |pg_global  | postgres |                |                   |         | 637 kB  |ts_a       | postgres | /data/tbs/ts_a |                   |         | 6 bytes |ts_b       | postgres | /data/tbs/ts_b |                   |         | 6 bytes |
(4 rows)

詳見CREATE TABLESPACE 和 DROP TABLESPACE。

修改表空間的位置其實就是使用符號鏈接指向新位置即可。

還可以修改表空間的owner，修改數據庫或用戶的默認表空間。

在表空間之間移動對象

語法為：

ALTER TABLE ... SET TABLESPACE ...
-- 索引和表可以位于不同的表空間
ALTER INDEX ... SET TABLESPACE ...
REINDEX ... TABLESPACE ...

訪問其他 PostgreSQL 數據庫中的對象

使用postgres_fdw擴展。

或者定義函數，然后在函數中直接連接另一個庫，然后操作。

訪問其他外部數據庫中的對象

參見Foreign data wrappers中的Specific SQL Database Wrappers，如oracle_fdw，mysql_fdw等。

使視圖可更新

PG中的視圖不用特別聲明，如果足夠簡單，本身就是支持插入，更新和刪除的。

所謂足夠簡單的具體含義，請參見Updatable views以及CREATE VIEW中的Updatable Views，特別是后者，包含具體的定義和示例。

具體要求為：

視圖的 FROM 列表中必須只有一個條目，該條目必須是表或其他可更新視圖。
視圖定義頂層不得包含 WITH、DISTINCT、GROUP BY、HAVING、LIMIT 或 OFFSET 子句。
視圖定義頂層不得包含集合運算（UNION、INTERSECT 或 EXCEPT）。
視圖的選擇列表不得包含任何聚合函數、窗口函數或集合返回函數。

其他示例參考Creating PostgreSQL Updatable Views 和 PostgreSQL - Create updatable Views。

使用物化視圖

略。

使用 GENERATED 數據列

詳見Generated Columns。

生成列是一種特殊的列，它總是根據其他列計算得出。因此，它對于列的作用就如同視圖對于表的作用一樣。生成列有兩種：存儲列和虛擬列。存儲列在寫入（插入或更新）時進行計算，并像普通列一樣占用存儲空間。虛擬生成列不占用存儲空間，在讀取時進行計算。因此，虛擬生成列類似于視圖，而存儲生成列類似于物化視圖（除了它總是自動更新之外）。PostgreSQL 目前僅實現了存儲生成列。

要創建生成列，請在 CREATE TABLE 中使用 GENERATED ALWAYS AS 子句，例如：

CREATE TABLE people (...,height_cm numeric,height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

PG還支持Identity Columns。

標識列是一種由隱式序列自動生成的特殊列。它可用于生成鍵值。

要創建標識列，請在 CREATE TABLE 語句中使用 GENERATED … AS IDENTITY 子句，例如：

CREATE TABLE people (id bigint GENERATED ALWAYS AS IDENTITY,...,
);

使用數據壓縮

PostgreSQL 提供多種類型的數據壓縮：

長數據值自動壓縮 (TOAST)
提供壓縮數據類型的擴展（例如 JSON）
WAL 文件壓縮
轉儲文件壓縮
基礎備份壓縮
SSL 壓縮（SSL 壓縮被認為是不安全的，因此僅在私有網絡中使用）
GiST 和 SP-GiST 索引壓縮
Btree 索引壓縮（也稱為重復數據刪除）

postgres=# create table t1(a varchar(512) compression lz4);
CREATE TABLEpostgres=# show default_toast_compression;default_toast_compression
---------------------------pglz
(1 row)

來看下 TOAST（The Oversized-Attribute Storage Technique）的概念。

PostgreSQL 使用固定的頁面大小（通常為 8 KB），并且不允許元組跨越多個頁面。因此，無法直接存儲非常大的字段值。為了克服這一限制，大字段值會被壓縮和/或拆分成多個物理行。這個過程對用戶來說是透明的，對大多數后端代碼的影響很小。這項技術被親切地稱為 TOAST（意為“自切片面包以來最棒的東西”）。TOAST 基礎架構還用于改進內存中大數據值的處理。
?
只有某些數據類型支持 TOAST — 無需對無法生成大字段值的數據類型施加開銷。要支持 TOAST，數據類型必須具有可變長度 (varlena) 表示，其中，通常，任何存儲值的第一個四字節字包含該值的總長度（以字節為單位）（包括其自身）。
?
TOAST 占用了 varlena 長度字的兩位（大端機器上的高位，小端機器上的低位），從而將可 TOAST 數據類型的任何值的邏輯大小限制為 1 GB（2³⁰ - 1 字節）。

TOAST 管理代碼支持四種不同的策略，用于在磁盤上存儲可 TOAST 列，詳見這里：

PLAIN 策略禁止壓縮或行外存儲。對于不可 TOAST 數據類型的列，這是唯一可行的策略。
EXTENDED 策略允許壓縮和行外存儲。這是大多數可 TOAST 數據類型的默認策略。系統將首先嘗試壓縮，如果行仍然過大，則嘗試行外存儲。
EXTERNAL 策略允許行外存儲，但不允許壓縮。使用 EXTERNAL 策略可以加快對寬文本和 bytea 列的子字符串操作速度（但會增加存儲空間），因為這些操作經過優化，在行外值未壓縮的情況下，僅獲取所需的部分。
MAIN 策略允許壓縮，但不允許行外存儲。（實際上，此類列仍會進行行外存儲，但只有在沒有其他方法使行足夠小以適應頁面大小時，才會將其作為最后的手段。）