來架個網站吧-22.網站環境建置-安裝資料庫
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 /data/backup/pgarch
sudo mkdir -p /var/log/postgresql/
sudo chown -R postgres:postgres /db/postgresql
sudo chown -R postgres:postgres /var/log/postgresql/
sudo chown -R postgres: /data/backup/
systemctl stop postgresql
sudo su postgres
/usr/lib/postgresql/15/bin/initdb -E utf8 -D /db/postgresql/instqnce-1
systemctl restart postgresql
- redhat 路徑
/var/lib/pgsql/15/data/
在 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
# archive log
archive_mode = on
archive_command='cp %p /data/backup/pgarch'
設定資料庫
設定完成之後，接下來就是開資料庫啦～
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"
備份
mkdir -p /var/log/pg-backup
chown postgres: /var/log/pg-backup
開完資料庫記得做備份啦，以下是我自己的腳本。
- /var/lib/postgresql/scripts/db_backup.sh
- /usr/local/bin/pg-backup.sh
#!/bin/bash
export NOW_TIME=`date +%Y%m%d%H%M%S`
export DATANASE_NAME="<DATANASE_NAME>"
export FILE_NAME="pg.bk.${DATANASE_NAME}."${NOW_TIME}
export BACKUP_PATH=/data/backup
export LOG_PATH=/var/log/pg-backup
echo "$(date +%Y%m%d%H%M%S): backup database start" >> ${LOG_PATH}/pg-backup-${NOW_TIME}.log
/usr/bin/pg_dump -Fc -c ${DATANASE_NAME} > ${BACKUP_PATH}/${FILE_NAME}.dump -O
echo "$(date +%Y%m%d%H%M%S): remove backup database after 3 days" >> ${LOG_PATH}/pg-backup-${NOW_TIME}.log
/usr/bin/find ${BACKUP_PATH}/*.dump -type f -mtime +3 -exec rm -f {} \;
echo "$(date +%Y%m%d%H%M%S): backup database end" >> ${LOG_PATH}/pg-backup-${NOW_TIME}.log
crontab -u postgres -e
# chmod 0755 /usr/local/bin/pg-backup.sh
# crontab -u postgres -e
# su - postgres
0 0 * * * /usr/local/bin/pg-backup.sh
防火牆設定
確認都沒有問題之後，最後就是設定防火牆。
sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.1" port protocol="tcp" port="5432" accept'
postgresql 常用命令
在 PostgreSQL 中列出所有資料庫
su - postgres
psql -U postgres
\l