rockyLinux 初體驗（教學）PostgreSQL15

資料庫軟體 PostgreSQL 安裝
資料庫軟體 PostgreSQL 設定
資料庫軟體 PostgreSQL 互動
通用資料庫管理軟體 DBeaver

彼時，PostgreSQL 已經更新到了15.2。

距離我上一次寫 PostgreSQL 教學 2022-03-20，已經過去一年多了。Linux篇 PostgreSQL 教學很久之前就想寫了，一直停留在想法上面，沒有付諸實際行動。那會我的主要環境還在centos-7上，因為 centos-7快要停止維護了，目前已經轉移到 rockyLinux-9平臺。

當時只是簡單的在 Windows 平臺介紹如何安裝和簡單使用，甚至沒有過多參考官方檔案。

也是對前段時間總結的《SQL 基礎知識掃盲》的補充。

如今，我為什麼又寫起了 postgreSQL 相關檔案呢？
答：目前市面postgreSQL檔案相對較少，官方檔案純英文，上手有一定的難度。像MySQL（MariaDB）、Oracle之類的檔案已經爛大街了，無非是新版本釋出，閒暇時間部署嚐嚐鮮。

初體驗，第一次嘛，姿勢、動作難度不能太高，容易勸退，所以比較簡單。

資料庫軟體 PostgreSQL 安裝

如果獲取軟體比較緩慢，可以在公眾號回覆blog，進入站點搜尋：rockyLinux映象源下載地址。如下所示，列出部分 postgresql 國內映象源地址：

浙江大學開源軟體映象站：https://mirrors.zju.edu.cn/postgresql/
中國科學技術大學映象站：https://mirrors.ustc.edu.cn/postgresql/
清華大學開源軟體映象站：https://mirrors.tuna.tsinghua.edu.cn/postgresql/

最新版本（PDF）檔案地址：https://www.postgresql.org/files/documentation/pdf/15/postgresql-15-A4.pdf

如果還是下載緩慢，這是正常現象，建議使用迅雷（打錢）等BT工具下載，或者在Linux平臺使用 wget 獲取，然後使用 scp 命令傳到Windows平臺瀏覽。

目前所有版本，最新版本為15.2，9th February 2023: PostgreSQL 15.2, 14.7, 13.10, 12.14, and 11.19 Released!

安裝方式：Packages and Installers

使用 Installers 安裝包形式進行安裝，RHEL系列使用rpm包居多。

選擇適合你的作業系統，支援的作業系統比較豐富：

Linux
macOS
Windows
BSD
Solaris

postgreSQL 下載目前支援兩種方式：

Packages：發行安裝包形式，難易度較低，不靈活。
Source：原始碼包形式，難易度相對較高，比較靈活。

如下，將演示Linux發行版Rocky-9平臺PostgreSQL的部署。

切換到普通用進行安裝：

su wzgy

如果安裝rockyLinux-9之後，預設提示安裝的版本是 postgresql-13.10，使用TAB鍵進行補全會提示。

[wzgy@localhost ~]$ dnf -y install postgresql-server-13.10-1.el9_1.x86_64
postgresql-contrib-13.10-1.el9_1.x86_64   
...
postgresql-server-13.10-1.el9_1.x86_64	postgresql-upgrade-13.10-1.el9_1.x86_64

如果想安裝比較新的版本，可以前往postgresql官網找到對應的Linux發行版選擇對應版本進行安裝。

postgreSQL 下載地址： https://www.postgresql.org/download/

範例選擇 Linux 發行版Red Hat/Rocky/CentOS version 9（版本），PostgreSQL Yum源倉庫：
https://www.postgresql.org/download/linux/redhat/

PostgreSQL 快速安裝

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql15-server
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15

PostgreSQL 詳細安裝步驟

PostgreSQL 下載所需要 rpm 依賴包，用於更新（版本庫）到最新版本：

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

PostgreSQL 匯入公鑰，驗證過程，由於網路等原因可能會失敗：

sudo dnf -qy module disable postgresql
匯入 GPG 公鑰 0x442DF0F8:
 Userid: "PostgreSQL RPM Building Project <[email protected]>"
 指紋: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
 來自: /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

PostgreSQL 安裝 postgresql-15 服務

sudo dnf install -y postgresql15-server

由於網路等原因，可能安裝會比較緩慢，這裡建議更換為國內yum & dnf 源，比如阿里、網易等等都可以。

比如更換阿里源：

sed -e 's|^mirrorlist=|#mirrorlist=|g' \
    -e 's|^#baseurl=http://dl.rockylinux.org/$contentdir|baseurl=https://mirrors.aliyun.com/rockylinux|g' \
    -i.bak \
    /etc/yum.repos.d/rocky-*.repo
dnf makecache

注意：替換映象源，建議先備份，然後驗證路徑是否正確。

範例，注意大小寫，可能存在無法讀取正確路徑，Linux下對大小寫敏感：

ls /etc/yum.repos.d/rocky-*.repo

輸出資訊：/etc/yum.repos.d/rocky-addons.repo /etc/yum.repos.d/rocky-devel.repo /etc/yum.repos.d/rocky-extras.repo，證明路徑真實存在。

備份，sed命令接 -i 屬性已經加入備份到當前目錄：

sed -i .bak \
/etc/yum.repos.d/rocky-*.repo

最後使用 dnf makecache 更新快取：

dnf makecache

如果使用 RHEL8 之前，請使用 yum makecache 更新。

PostgreSQL 初始化：

/usr/pgsql-15/bin/postgresql-15-setup initdb

看到：Initializing database ... OK，證明初始化完成。

為什麼這樣執行？
答：postgresql-15-setup 指令碼所在絕對路徑位置 /usr/pgsql-15/bin/，通過 initdb 引數進行初始化。

如果你有過使用MySQL（MariaDB）或者Oracle以及其它關係型資料庫經驗，也存在初始化的過程，執行命令略有不同。在MySQL（MariaDB）中，可以使用如下命令初始化：bin\mysqld –initialize-insecure 或者 bin\mysqld –initialize-insecure –console。

PostgreSQL 設定開機自啟

systemctl enable postgresql-15

看到輸出資訊：Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-15.service → /usr/lib/systemd/system/postgresql-15.service. 代表設定開機自啟完成。

PostgreSQL 服務啟動

systemctl start postgresql-15

查詢當前使用者身份：

bash-5.1$ whoami
postgres

可以看到當前使用者已經切換到了 postgres。

管理 PostgreSQL 服務另一種方式，使用 pg_ctl 指令碼

pg_ctl 命令啟動服務：

bash-5.1$ /usr/pgsql-15/bin/pg_ctl -D /var/lib/pgsql/15/data/ start

輸出資訊：

等待伺服器程序啟動 ....2023-04-25 18:47:50.476 CST [3505] 紀錄檔: 紀錄檔輸出重定向到紀錄檔收集程序
2023-04-25 18:47:50.476 CST [3505] 提示: 後續的紀錄檔輸出將出現在目錄 "log"中.
完成
伺服器程序已經啟動

pg_ctl 命令重啟服務：

bash-5.1$ /usr/pgsql-15/bin/pg_ctl -D /var/lib/pgsql/15/data/ restart

輸出資訊：

等待伺服器程序關閉 .... 完成
伺服器程序已經關閉
等待伺服器程序啟動 ....2023-04-25 18:48:34.629 CST [3521] 紀錄檔: 紀錄檔輸出重定向到紀錄檔收集程序
2023-04-25 18:48:34.629 CST [3521] 提示: 後續的紀錄檔輸出將出現在目錄 "log"中.
完成
伺服器程序已經啟動

pg_ctl 命令停止服務：

bash-5.1$ /usr/pgsql-15/bin/pg_ctl -D /var/lib/pgsql/15/data/ stop

輸出資訊：

等待伺服器程序關閉 .... 完成
伺服器程序已經關閉

注意：需要以非 root 使用者身份執行命令。

pg_ctl: 無法以 root 使用者執行
請以伺服器程序所屬使用者 (非特權使用者) 登入 (或使用 "su")

資料庫軟體 PostgreSQL 設定

postgreSQL 檢視狀態以及驗證是否自啟

systemctl enable postgresql-15

當你看到 active (running)，代表服務（活躍）正常啟動狀態，看到 /usr/lib/systemd/system/postgresql-15.service; enabled;代表開機自啟，如果想開機禁用，使用命令 systemctl disable postgresql-15 即可。

到此為止，postgresql-15 安裝過程以及服務啟動演示完成。

postgresql-15 初步使用

rockyLinux建立普通使用者，需要root（建立使用者）許可權：

useradd wzgy
passwd wzgy

引數含義：

useradd wzgy：useradd 命令用於新增使用者，後面接使用者名稱。
passwd wzgy：passwd 命令用於修改新增使用者密碼。

切換到普使用者，如果沒有普通使用者，可以建立一個使用者用於安裝管理postgresql-15：

su wzgy
sudo systemctl status postgresql-15.service

引數含義：

su wzgy：su 命令用切換使用者身份。
sudo：用於提取許可權，是一個很有意思的命令。

netstat 監控 5432 埠，輸出資訊如下：

[wzgy@localhost ~]$ sudo netstat -tlunp | grep 5432
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      9117/postmaster
tcp6       0      0 ::1:5432                :::*                    LISTEN      9117/postmaster

ps 監控服務命令監控 postmaster服務：

ps -aux | grep postmaster

通過監控服務命令可以看出，初始化的data目錄在：/var/lib/pgsql/15/data/。

使用 yum & dnf 命令安裝，預設組態檔所在路徑：

/var/lib/pgsql/15/data/postgresql.conf

注意：如果 data 目錄不存在，大概率初始化階段出現問題，也就是初始化失敗，需要檢查紀錄檔檔案：/var/lib/pgsql/15/initdb.log。

伺服器環境可以使用 vim 或者 nvim（需要安裝 neovim ）進行編輯組態檔：postgresql.conf。

修改設定：sudo vim /var/lib/pgsql/15/data/postgresql.conf

開放設定，只演示最基礎的：

listen_addresses = 'localhost' ：監聽地址，重啟資料庫軟體服務生效；
defaults to 'localhost'; use '*' for all：預設為localhost，*代表開放所以ip進行存取。
port = 5432 ：監聽埠，重啟資料庫軟體服務生效；
max_connections = 100 ：最大連線數，重啟資料庫軟體服務生效。

# - 組態檔：連線設定 -
listen_addresses = 'localhost'          # 監聽地址，重啟資料庫軟體服務生效;
port = 5432                             # 監聽埠，重啟資料庫軟體服務生效;
max_connections = 100                   # 最大連線數，重啟資料庫軟體服務生效;

注意：如果需要使用通用資料庫管理工具遠端連線，還需要做如下修改，授予相應許可權，由於測試，我直接設定 all（所有ip）：

編輯pg_hba.conf組態檔，vim /var/lib/pgsql/15/data/pg_hba.conf，找到 IPv4 local connections，官方檔案有詳細設定說明。理論上，應該可以通過 GRANT 命令形式授權，在MySQL（MariaDB）是支援的。

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             all                     scram-sha-256

如果安裝了 firewalld 防火牆管理工具，需要開放相應的埠：

firewall-cmd --zone=public --add-port=5432/tcp --permanent
firewall-cmd --reload

修改組態檔後，記得重啟 postgresql 服務。

如果你使用其它普通使用者建立使用者、角色、登入等等操作，會出現如下錯誤：
createuser: 錯誤: 連線到通訊端"/var/run/postgresql/.s.PGSQL.5432"上的伺服器失敗:致命錯誤: 角色 "root" 不存在。

如果你不確定有哪些使用者，可以使用命令檢視：

[root@localhost ~]# cat /etc/passwd | grep postgres
postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash

請注意：有時候為了方便，我直接使用root使用者操作，比如我用 cat 和 grep 命令檢視postgres使用者。

解決方案，切換使用者為postgres：su postgres，如果你仔細閱讀了官方檔案，其實會有啟發的。

登入字元命令操作介面 psql：

psql

資料庫軟體 PostgreSQL 互動

引數 postgres=#：登入 PostgreSQL 預設使用者字首名稱。

輸入 select version(); 查詢版本：

postgres=# select version();

輸出資訊：PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.3.1 20220421 (Red Hat 11.3.1-2), 64-bit。

引數含義：

PostgreSQL 15.2：資料庫軟體版本 15.2。
x86_64-pc-linux-gnu：使用Linux平臺x86_64架構。
compiled by gcc (GCC) 11.3.1 20220421 (Red Hat 11.3.1-2)：編譯gcc版本。
64-bit：64位元作業系統。

輸入測試驗證：postgres=# select 1\g
輸出結果： ?column? 1，這是正常狀態。

建立表 books

postgres=# create table books(id varchar(64) primary key,name varchar(64));
CREATE TABLE

新增操作，插入一條資料：

postgres=# insert into books values('1001','絕世武功祕籍');
INSERT 0 1

查詢操作，查詢表 books ：

postgres=# select * from books\g
  id  |     name
------+--------------
 1001 | 絕世武功祕籍
(1 行記錄)

修改操作，修改表 books ：

postgres-# update books set name='PostgreSQL-15.2'  where id='1001'
UPDATE 1

再次查詢：

postgres=# select * from books;
  id  |      name
------+-----------------
 1001 | PostgreSQL-15.2
(1 行記錄)

輸出資訊：發現 name 值已經變成了 PostgreSQL-15.2，證明修改成功。

刪除 books 錶行記錄，這是個人操作重要資料時有備份的習慣，開啟顯示事務，驗證完後手動commit（提交）：

begin：手動開啟顯示事務。
delete：執行刪除語句。
rollback：執行回滾操作。
commit：最後提交。

postgres=*# delete from books where id='1001';
DELETE 1

無論是修改或者刪除（統稱更新操作），建議加上條件 where 語句。

無圖無真相，如下所示為時間線操作步驟：

試一試 postgreSQL 使用 explain 分析SQL執行效率，和MySQL（MariaDB）差不多，引數顯示更少：

postgres=# explain select * from books where id='1001'\g
                                QUERY PLAN
--------------------------------------------------------------------------
 Index Scan using books_pkey on books  (cost=0.14..8.16 rows=1 width=292)
   Index Cond: ((id)::text = '1001'::text)
(2 行記錄)

因為建立表時，事先已經指定 id 屬性為主鍵（primary key），所以執行計劃掃描表使用到索引（using books_pkey）。

psql 幫助命令：

? [commands] ：顯示反斜線命令的幫助；
? options ：顯示 psql 命令列選項的幫助；
? variables ：顯示特殊變數的幫助；
\h [NAME] ： SQL命令語法上的說明，用*顯示全部命令的語法說明。

最後，善用幫助檔案，有助於你快速定位元運算命令：

postgres=# \h
postgres=# \?

範例，我要查詢alter具體用法：postgres-# \h alter 。如果你和我一樣安裝選擇語言是中文版的rockyLinux，那麼將有友好的中文翻譯。

輸出資訊比較多：比如alter、create、drop等等DDL（Data Definition Language）語句。

簡單科普：
DDL（Data Definition Language）語句，資料定義語句。主要用於對索引、資料表結構、欄位等進行建立、刪除以及修改。比如我們常用的關鍵字主要有：CREATE、DROP、ALTER等等。一般是DBA管理員使用的比較頻繁。

DML（Data Manipulation Language）語句，資料操縱語句。主要用於對資料庫表中記錄進行增刪改查。比如我們常用的關鍵字主要有：INSERT、DELTE、UPDATE以及SELECT等。一般是開發人員使用的比較頻繁。

DCL（Data Control Language）語句，資料控制語句。主要用於對使用者、表、欄位的存取許可權進行控制授權。比如我們常用的關鍵字有：grant（授權）、revoke（撤回授權）等。

退出 psql 終端管理命令： postgres=# \q

再次進入 psql 終端管理，執行：psql

通用資料庫管理軟體 DBeaver

初始化連線引數：

新增資料庫連線，選擇PostgreSQL。
主機（host）：ip地址，localhost或者遠端ip。
資料庫（database）：不填預設使用 postgres。
埠（port）：預設為5432，實際工作中，建議修改，儘量避免被惡意掃描軟體攻擊。
使用者名稱（username）：初始化安裝會存在一個超級使用者 postgres。
密碼：可以使用 alter 語句修改，預設可能是空。

修改密碼語句：

postgres=# ALTER USER postgres PASSWORD '123456';

測試連線，連線成功（正常）顯示輸出資訊：

選擇頂部選單欄：資料庫。
新建資料庫連線。
選擇PostgreSQL，設定連線引數。
測試連線。

資料庫目錄導航，查詢表 books：

定位資料庫導航，選擇postgres，依次展開public、表；
右鍵（F4）檢視表 books；
選項：屬性（顯示錶結構等等）、資料（行記錄），ER圖。

使用SQL編輯器查詢：

選擇選擇頂部選單欄：SQL編輯器；
新建SQL編輯器；
輸入SQL語句：select * from books b 。

看完整篇教學後，有同學可能有疑問，你這張表的欄位為什麼這麼少？沒錯，就是這麼少。

初體驗，第一次嘛，姿勢動作難度不能太高，容易勸退，所以比較簡單。建立使用者、建立角色以及許可權相關等等知識沒有具體介紹，也許會在下一篇介紹喲。

至此，在 Linux 發行版 rockyLinux-9上初步體驗 postgresql最新版本postgresql-15。

以上總結，僅供參考！

如需轉載，請標明出處和原作者。

參考資料：

PostgreSQL-15官方檔案：https://www.postgresql.org/docs/15/index.html

—END—