tags: 來架個網站吧 postgresql

我是目錄

昨天安裝完虛擬機範本之後，接下來就是安裝資料庫啦～

安裝資料庫可以說是一向大工程。小弟我本人三生有幸，天地父母都很寵我～讓我在目前短短職涯6年多一點點可以碰到

平價

奢華

古董

這三個資料庫，資料庫對我來說是一項深水莫測，頂點遙不可及的領域，因此我還是碰了一兩下子。

在我的目前職涯來說，平價的選項是: MySQL、Postgresql。這兩個資料庫部屬可以很快，連上網路 sudo apt install mysql 等速成建立完成，立即在本機開發(H2說我更快！)。

奢華莫非 Oracle 莫屬，之前碰了 Fujitsu SPARC M12 外加 Oracle 當時最新版的，當時一張光碟在手宛如北市套房在我一樣。(有更貴的聽說還有)也是我目前碰過性能最好的資料庫，他的 RAC (Real Application Clusters)架構真的可以讓資料庫可連線數量近乎無限擴張，可以不用擔心資料庫連線不足問題。 有可能會造成程式越寫越爛，佔用調超多資料庫連線

古董目前就碰到 DB2 5.5 而已。

上述畢竟是工作經驗而已，其實也沒有什麼好炫耀的，因為還只是小小菜鳥而已。轉化成自己的經驗才是寶貴。

以下是我針對本期的平價菜單，希望大家可以飽食滿意。

環境規劃

• OS:Debian 12
• DB: Postgresql-15

安裝指令

安裝指令原則上，我是直接採取官方的說明。

sudo apt install gnupg gnupg2 gnupg1

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt -y install postgresql-15

配置注意事項

在正式環境配置方面，大致上需要處理以下：

• 資料庫獨立磁碟: 資料庫通常部屬之後，短則一年、長則數十年(我遇過16年未有關機紀錄的磁碟陣列)，為了確保資料庫存放空間不被作業系統因素影響，會將資料獨立磁碟存放。目前看過最好的是硬碟櫃專屬存放(未來不希望看到更好的，我的手會抖)。
• 日誌調整: 近年資安事件頻傳，在資安稽核也加重力道在資料庫安全上。在日誌的保存及細節以倍受關注。
• 備份: 備份一定要有全量備份，盡量保存三代，本機盡可能保留最新一代。
• 防火牆設定: 千外不要把服務直接對外，要開白名單限制存取。

資料庫獨立磁碟

在這邊我就不多做說明，可以參考這篇的作法: 我的Linux生活日記 08-LVM-新增硬碟-LVM。

假設都已經做完了，我這邊把資料硬碟放到 /db/postgresql目錄中，以下是指令操作及初始化資料庫指令:

sudo mkdir -p /db/postgresql
sudo mkdir -p /var/log/postgresql/
sudo chown -R postgres:postgres /db/postgresql
sudo chown -R postgres:postgres /var/log/postgresql/
systemctl stop postgresql
sudo su postgres
/usr/lib/postgresql/15/bin/initdb -E utf8 -D /db/postgresql/instqnce-1
systemctl restart postgresql

在 Debian 中因為安裝完後會直接先初始化，所以要記得改一下配置: postgresql.conf 、 pg_hba.conf

• /etc/postgresql/15/main/postgresql.conf

data_directory = '/db/postgresql/instqnce-1'

• /etc/postgresql/15/main/pg_hba.conf

如果找不到 pg_hba.conf 檔案，可以用 CLI 查一下配置檔的實體路徑。

psql -c "SHOW hba_file;"

在這邊建議依實際狀況做處理，以最小開放為原則。

host    all             all             172.18.17.0/16            scram-sha-256

日誌調整

postgresql 可以開啟以下設定，適度的紀錄連線資訊及執行歷程。

# 日誌調整
log_directory = '/var/log/postgresql/' # 預設為: log
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern, redhat 預設為 postgresql-%a.log
log_line_prefix = '%m [%p] %q%u@%d-%a '            # special values 預設為: '%m [%p] ' 
log_checkpoints = on # special values 預設為: off
log_connections = on # 記錄連線建立，預設為: off
log_disconnections = on # 記錄連線摧毀，預設為: off
log_duration = off # 記錄SQL query的執行時間，預設為: off，核心系統再開
log_statement = 'ddl' # option: none, ddl, mod, all，預設為: none，可以紀錄執行哪個錯誤SQL,DDL 操作SQL

設定資料庫

設定完成之後，接下來就是開資料庫啦～

sudo -u postgres createuser --pwprompt dict_ap  # pwd: enterYourPwd
sudo -u postgres createdb -O dict_ap ironman_dict_db
sudo -u postgres psql -c "GRANT ALL ON DATABASE ironman_dict_db to dict_ap"

備份

開完資料庫記得做備份啦，以下是我自己的腳本。

• /var/lib/postgresql/scripts/db_backup.sh

#!/bin/bash  
DATE=$(date '+%Y%m%d');
PG_DATA=/db/postgresql/
PG_LOCAL_BK=/var/lib/postgresql/backup
PG_MNT_BK=/mnt/backup/postgresql

#START BACKUP
echo "START BACKUP..............." 
sudo -u postgres psql postgres -c "select pg_start_backup('hot_back')"
cd $PG_HOME
tar zcvf $PG_LOCAL_BK/pg_hotbackup_$DATE.tar.gz $PG_DATA
sudo -u postgres psql postgres -c "select pg_stop_backup()"
sudo cp $PG_LOCAL_BK/pg_hotbackup_$DATE.tar.gz $PG_MNT_BK
sudo -u postgres psql "BACKUP  END"

防火牆設定

確認都沒有問題之後，最後就是設定防火牆。

sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.1" port protocol="tcp" port="5432" accept'