rockyLinux 初體驗（教程）PostgreSQL15

文正耕耘發表於2023-04-26
LinuxSQL

目錄

彼時，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 國內映象源地址：

  1. 浙江大學開源軟體映象站：https://mirrors.zju.edu.cn/postgresql/
  2. 中國科學技術大學映象站：https://mirrors.ustc.edu.cn/postgresql/
  3. 清華大學開源軟體映象站：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包居多。

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

  1. Linux
  2. macOS
  3. Windows
  4. BSD
  5. Solaris

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

  1. Packages：發行安裝包形式，難易度較低，不靈活。
  2. 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 <pgsql-pkg-yum@postgresql.org>"
 指紋: 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

引數含義：

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

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

su wzgy
sudo systemctl status postgresql-15.service

引數含義：

  1. su wzgy：su 命令用切換使用者身份。
  2. 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

開放配置，只演示最基礎的：

  1. listen_addresses = 'localhost' ：監聽地址，重啟資料庫軟體服務生效；
  2. defaults to 'localhost'; use '*' for all：預設為localhost，*代表開放所以ip進行訪問。
  3. port = 5432 ： 監聽埠，重啟資料庫軟體服務生效；
  4. 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。

引數含義：

  1. PostgreSQL 15.2：資料庫軟體版本 15.2。
  2. x86_64-pc-linux-gnu：使用Linux平臺x86_64架構。
  3. compiled by gcc (GCC) 11.3.1 20220421 (Red Hat 11.3.1-2)：編譯gcc版本。
  4. 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（提交）：

  1. begin：手動開啟顯示事務。
  2. delete：執行刪除語句。
  3. rollback：執行回滾操作。
  4. 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 幫助命令：

  1. ? [commands] ：顯示反斜線命令的幫助；
  2. ? options ：顯示 psql 命令列選項的幫助；
  3. ? variables ： 顯示特殊變數的幫助；
  4. \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

初始化連線引數：

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

修改密碼語句：

postgres=# ALTER USER postgres PASSWORD '123456';

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

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

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

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

使用SQL編輯器查詢：

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

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

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

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

以上總結，僅供參考！

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

參考資料：

—END—

相關文章