前言：
以下內容為前幾天在備考PostgreSQL入門考試時候做的筆記，經過了全職的兩天的奮戰與實驗，並最終順利通過了PCA初級認證考試。現在把我學習的筆記分享給大家，文中有對應的思維導圖圖片可供檢視，內容與後面正文文字一致。另外，由於SQL語句部分比較基礎，基本上會一門資料庫就都會，所以此處部分省略掉不做過多記錄了。
以下，enjoy：
# PostgreSQL
## PostgreSQL的發展歷程
### 始於1986年的Postgres專案
### 1994年，新增了SQL語言直譯器，Postgres95誕生
### 1996年，更名 PostgreSQL,版本號從6.0開始
## PostgreSQL的安裝
### yum安裝
- https://www.postgresql.org/download/linux/redhat/
- yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
- yum install -y postgresql14-server
- /usr/pgsql-14/bin/postgresql-14-setup initdb
- systemctl enable postgresql-14
- systemctl start postgresql-14
- 其他操作
- 關閉selinux
- setenforce 0
- sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
- 關閉防火牆
- systemctl status firewalld.service
- systemctl stop firewalld.service
- systemctl disable firewalld.service
- 修改環境變數 /etc/profile
- export PATH=/usr/pgsql-14/bin/:$PATH
- source /etc/profile
### rpm安裝
- https://yum.postgresql.org/rpmchart/
- postgresql14
- postgresql14-contrib
- postgresql14-libs
- postgresql14-server
- rpm -ivh postgresql14*
### 原始碼的安裝
- wget https://ftp.postgresql.org/pub/source/v13.3/postgresql-13.3.tar.gz
tar xf postgresql-13.3.tar.gz
- 重要的依賴：readline,flex,bison
- yum install -y bison flex readline-devel zlib-deve1 zlib zlib-devel gcc openssl-devel
- ./configure && make && make install | install-world
- 編譯
- cd postgresql-xx/
- ./configure --prefix=/XXX/postgresql --with-openssl
- gmake world && gmake install-world
- 授權
- chown -R postgres. /XXX/postgresql
- 環境變數 /etc/profile
- export PATH=/XXX/postgresql/bin:$PATH
export PGDATA=/XXX/postgresql/data
- source /etc/profile
- 初始化
- su - postgres
initdb -D $PGDATA
- 啟動
- pg_ctl -D $PGDATA start
- 使用系統管理
- 修改: /usr/lib/systemd/system/postgresql-xx.service
- [Unit]
Description=PostgreSQL database server
After=network.target
[Service]
Type=forking
User=postgres
Group=postgres
Environment=PGPORT=5432
Environment=PGDATA=/XXX/postgresql/data/
OOMScoreAdjust=-1000
ExecStart=/XXX/postgresql/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
ExecStop=/XXX/postgresql/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/XXX/postgresql/bin/pg_ctl reload -D ${PGDATA} -s
TimeoutSec=300
[Install]
WantedBy=multi-user.target
- 重新載入：systemctl daemon-reload
- 使用systemctl啟動
- systemctl start postgresql-xx.service
systemctl enable postgresql-xx.service
### 二進位制的安裝
- EDB
## PostgreSQL的安裝(其他)
### 初始化命令
- 執行initdb建立資料庫例項：
- $ su - postgres
- $ initdb -D
- 指定資料目錄。沒有指定，將使用環境變數PGDATA
- $ initdb -U
- 指定資料庫超級使用者名稱字
- -E
- 指定資料庫字元編碼
- -n
- 錯誤後不清理檔案
- -W
- 初始化時給資料庫設定密碼
- -x
- 預寫日誌目錄位置
- --wal-segsize=XXX
- 指定 WAL 段大小（單位 M），預設是 16M，最大 1G
### 啟動和關閉
- system管理方式
- systemctl start postgresql-xx
systemctl enable postgresql-xx
systemctl status postgresql-xx
systemctl stop postgresql-xx
- postgresql自帶的命令
- pg_ctl start
pg_ctl stop
pg_ctl status
- pg_ctl -D $PGDATA stop -m smart
pg_ctl -D $PGDATA stop -m fast
pg_ctl -D $PGDATA stop -m immediate
- smart 等待客戶端斷開連線(執行之後會特別慢)
- fast 回滾未完成的事務，斷開客戶端連線（推薦用法）
- immediate 強行終止程式，資料庫沒有乾淨的關閉
- 啟動\停止有關日誌
- vim /var/lib/pgsql/xx/data/log/postgresql-Mon.log
### 注意事項
- 關閉防火牆
- systemctl status firewalld.service
- systemctl stop firewalld.service
- systemctl disable firewalld.service
- ntp時間同步
- 服務端配置
- yum -y install ntp
- 修改配置檔案 /etc/ntp.conf
- 給本機許可權:
restrict 127.0.0.1
restrict ::1
- 例如授權10.0.0.0網段上所有的機器允許從ntp伺服器上查詢和同步時間:
restrict 10.0.0.0 mask 255.255.255.0 nomodify notrap
- 增加時間伺服器列表：
0.asia.pool.ntp.org
0.cn.pool.ntp.org
time.nist.gov
server 0.asia.pool.ntp.org iburst
server 1.asia.pool.ntp.org iburst
server 2.asia.pool.ntp.org iburst
server 3.asia.pool.ntp.org iburst
- 當外部時間不可用時，使用本地時間:
server 127.127.1.0 iburst
fudge 127.127.1.0 stratum 10
- 設定開機自啟動
- systemctl enable ntpd
systemctl start ntpd
systemctl enable ntpdate
systemctl start ntpdate
- 檢視ntp情況
- ntpq -p
- remote: NTP主機的IP或主機名稱；
最左邊是 + 表示目前正在起作用的上層NTP；如果是 * 表示這個也連線上了，不過是作為次要聯機的NTP主機
- refid: 參考上一層NTP主機的地址
- st: stratum階層
- t: 連線型別
- u：單播(unicast)
- l：本地(local)
- m: 多播(multicast)
- b: 廣播(broadcast)
- when: 這個時間之前剛剛做過時間同步
- poll: 在幾秒之後進行下次更新
- reach: 已經向上層NTP伺服器要求更新的次數
- delay: 網路傳輸過程中的延遲時間
- offset: 時間補償的結果
- jitter: Linux系統時間和Bios硬體時間的差異時間
- 與硬體時間進行同步
- hwclock -w
- 測試
- ntpstat
- 客戶端配置
- yum -y install ntp ntpdate
- 方法一
- 重啟服務以使配置生效,之後大概要等10分鐘左右,才會同步成功
- echo "server ip" >/etc/ntp.conf
systemctl enable ntpd
systemctl restart ntpd
hwclock -w
- 方法二
- systemctl enable ntpdate
/usr/sbin/ntpdate -u ip
hwclock -w
crontab -e
10 23 * * * (/usr/sbin/ntpdate -u ip && /sbin/hwclock -w)&>/var/log/ntpdate.log
- 如果配置主從，儘量保持uid和gid一致
- 檢查uid和gid
- id postgres
- 通過usermod,groupmod修改OS使用者uid和gid
- groupmod -g 1000 postgres
usermod -u 1000 -g 1000 postgres
- 關閉selinux
- setenforce 0
- sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
- psql引數的使用
- 引數說明
- -U
- 以哪個使用者登入
- -W
- 強制輸入密碼
- -h
- 主機名/IP地址
- -p
- 埠號
- -d
- 登入哪個資料庫
- 檢視版本資訊
- psql –version
select version();
## PostgreSQL的配置
### 引數的修改
- 檔案位置：$ PGDATA/postgresql.conf
- include_if_exists = 'xxx.conf'
- 開啟引數，並存在可使用的 $PGDATA/xxx.conf 檔案才會進行載入
- include_dir = 'conf.d'
- 載入目錄下的引數檔案,$PGDATA/.conf.d/xxx.conf
- include = 'xxx.conf'
- 無論引數檔案是否存在都會載入 $PGDATA/xxx.conf
- 資料庫啟動時，會讀取該檔案，可手動修改
- postgresql.auto.conf
- alter system修改後的引數配置，會覆蓋postgresql.conf的值
- recovery.conf
- pg12已經不存在這個檔案，已經將此檔案的引數合併到了postgresql.conf
- pg_hba.conf 客戶端認證配置檔案(PG防火牆)
- 第一列 TYPE（連線方式）
- local
- 使用Unix域套接字的連線，如果沒有TYPE為local的條目則不允許通過Unix域套接字連線
- host
- 匹配使用TCP/IP建立的連線，同時匹配SSL和非SSL連線
- hostssl
- 匹配必須是使用SSL的TCP/IP進行連線。
- 配置hostssl的3個條件
- 戶端和服務端都安裝openssl
- 編譯時要指定 --with-openssl 開啟ssl支援
- 在postgresql.conf中配置ssl = on
- hostnossl
- 只匹配使用非SSL的TCP/IP連線
- 第二列 DATABASE（目標資料庫）
- 標識該行設定對哪個資料庫生效
- 第三列 USER (目標使用者)
- 標識該行設定對哪個資料庫使用者生效
- 第四列 ADDRESS (訪問來源)
- 標識該行設定對哪個IP地址或IP地址段生效
- 第五列 METHOD (認證方式)
- reject
- 無條件拒絕連線
- md5 或 password
- 雙重md5加密和明文加密
- scram-sha-256
- postgresql10中新增最安全的加密方式
- 檢視沒有用SCRAM加密口令的使用者
- create user foo password 'foopassword';
- select usename,passwd from pg_shadow where passwd not like 'SCRAM%' or passwd is null;
- select usename,passwd from pg_shadow ;
- trust
- 無條件的允許連線
- cert
- 使用SSL客戶端證照認證
- peer
- 本地作業系統的當前使用者名稱和資料庫的使用者名稱一致時，可以直接使用此使用者名稱登入而不需要密碼
- ident
- 使用者對映檔案
- 檢視當前的加密方法
- postgres=# show password_encryption;
- 修改加密方法
- postgres=# alter system set password_encryption = 'scram-sha-256';
postgres=# show password_encryption;
postgres=# select pg_reload_conf();
- 修改密碼
- postgres=# alter user foo password ''Xzzp2008 ';
- 遠端登入
- psql -h 192.168.1.221 -p 5433 -d postgres -U foo -W
- 加強口令複雜度管理外掛
- passwordcheck
- http://www.postgresql.org/docs/current/static/passwordcheck.html
- pg_ident.conf 客戶端認證對映檔案
### 資料庫相關命令
- 檢視引數
- 查詢pg_settings系統表
- SELECT name,setting FROM pg_settings where name ~ ‘xxx’;
- select name,setting,unit,short_desc from pg_settings where name like 'work_mem%';
- SELECT current_setting(name);
- SELECT current_setting('work_mem');
- 通過show 命令檢視
- show all
- 引數生效幾種方式
- SELECT pg_reload_conf();
- pg_ctl -D $PGDATA reload;
- /etc/init.d/postgresql-11.x reload; (el6)
- systemctl reload service.postgresql-11.x (el7)
- 子主題 3
### 資料庫管理
- 客戶端工具
- pgAdmin
- https://www.pgadmin.org
- yum / apt install pgadmin
- psql
- 連線資料庫
- psql -h localhost -p 5432 database_name
- 獲得psql的幫助
- \?
- 獲得語法的幫助
- \h STATEMENT
- 在shell中執行命令
- psql -c "STATEMENT"
- 通過psql執行sql檔案
- psql < f.sql
- 其他
- \l 檢視有哪些資料庫
- \c 用於切換資料庫
- \d 顯示每個匹配關係（表，檢視，索引，序列）的資訊
- \d 後面跟一個表名，表示顯示錶結構定義
- \d 後跟一個索引名，顯示索引的資訊
- \d 後面跟一個檢視名，顯示檢視資訊
- \timing on 顯示SQL執行的時間
- \timing off 關閉計時功能
- \dn 列出所有的schema
- \db 顯示所有的表空間
- \du\dg 列出所有的角色或者使用者
- \dp 顯示許可權分配情況
- \x 行列互換顯示
- \set AUTOCOMMIT off 將自動提交功能關閉
## 體系結構
### 記憶體結構
- shared_buffers
- 共享記憶體
- work_mem
- 當使用order by或distinct操作對元組僅從排序時會使用這部分記憶體
- wal_buffer
- wal快取
### 程式結構
- 檢視程式
- --ps –ef |grep post
- background writer
- 程式將shared buffer pool中的髒資料寫到磁碟，檢查點總能觸發這個程式
- checkpointer
- 檢查點會觸發產生這個程式
- autovacuum launcher
- autovacuum的守護程式，為vacuum process週期性的呼叫autovacuum work processes
- autovacuum的作用
- 刪除或重用無效元組的磁碟空間
- 更新資料統計資訊，保證執行計劃更優
- 更新visibility map，加速index-only scans
- 避免XID回捲造成的資料丟失
- WAL writer
- 週期性的從wal buffer重新整理資料到磁碟
- statistics collector
- 收集統計資訊程式，比如pg_stat_activity 和pg_stat_database的資料。（表和索引進行了多少次插入，更新，刪除操作，磁碟塊讀寫次數及行的讀寫次數）
- logging collector (logger)
- 將錯誤資訊寫入到日誌
- archiver
- 將日誌歸檔的程式
- postgremaster
- 監聽
### 資料庫叢集概念
- 一個資料庫集簇（database cluster）=一個資料庫例項（簡稱“例項”）
- 每個資料庫例項由資料庫目錄組成，目錄中包含了所有的資料檔案和配置檔案
- 不同的例項可以通過兩種方式引用
- 資料目錄的位置
- 埠號
- 一個伺服器可以管理多個資料庫例項
### 物理結構
- 資料目錄
- base
- 表和索引檔案存放目錄
- global
- 影響全域性的系統表存放目錄
- pg_commit_ts
- 事務提交時間戳資料存放目錄
- pg_stat
- 統計子系統資訊永久檔案
- pg_wal
- 事務日誌（預寫日誌）
- 相關檔案
- PG_VERSION
- 版本號檔案
- pg_hba.conf
- 客戶端認證控制檔案
- postgresql.conf
- 引數檔案
- postgresql.auto.conf
- 引數檔案，只儲存ALTER SYSTEM命令修改的引數
- postmaster.opts
- 記錄伺服器最後一次啟動時使用的命令列引數
- pg_ident.conf
- 控制postgresql使用者名稱對映檔案
- postmaster.pid
- 記錄資料庫程式編號、PGDATA、埠等
## 資料庫故障排查
### 檢視作業系統錯誤日誌
- /var/log/message 系統啟動後的資訊和錯誤日誌，是Red Hat Linux中最常用的日誌之一
### 檢視資料庫錯誤日誌
- 檢視檔案$PGDATA/log
預設情況下，一天產生一個日誌
## SQL入門
### DDL
- 資料定義語言，用來定義庫和表
### DML
- 資料操作語言
### DCL
- 資料控制語言
### DQL
- 資料查詢語言
### 資料型別
- 字元型別
- 數字型別
- 日期/時間型別
- 範圍型別
- 布林型別
- 相關約束
### 其他
## 資料庫備份相關
### 物理備份
- 熱備
- 全量 pg_basebackup
- 備份命令：
pg_basebackup -h ip -D /XXX/backup -P -p 5432 -U postgres
- 恢復命令:
cd /xxx/backup/
tar xf base.tar.gz -C ../data
- 結合時間點進行恢復
- 建立歸檔目錄
- mkdir -p /XXX/archive
- chown -R postgres. /XXX/
- 開啟歸檔，並重啟資料庫
- wal_level = 'replica'
- wal_level引數說明
- minimal
- 記錄wal最少，記錄資料庫異常關閉需要恢復的wal外，其它操作都不記錄
- replica
- 在minimal的基礎上還支援wal歸檔、複製和備庫中啟用只讀查詢等操作所需的wal資訊
- logical
- 記錄wal日誌資訊最多，包含了支援邏輯解析（10版本的新特性，邏輯複製使用這種模式）所需的wal，此引數包含了minimal和replica所有的記錄
- archive_mode = 'on'
- archive_command = 'cp %p /XXX/archive/%f'
- 重啟資料庫
- pg_ctl restart
- 執行全量備份
- pg_basebackup -D /xxx/backup -P -p 5432 -U postgres
- 如果此時的資料有變化，也可以執行增量備份：
pg_receivewal -D /xxx/backup/ -p 5432
- 啟動備份例項，進行資料恢復
- vim postgresql.conf
recovery_target_time = '2022-04-17 16:00:00.00000+08'
restore_command='cp /xxx/archive/%f %p'
recovery_target_action = 'promote'
- 建立備庫標籤
- touch recovery.signal
- 啟動資料庫
- pg_ctl -D /xxx/backup start
- 關閉讀模式
- select pg_wal_replay_resume();
- 結合還原點進行恢復
- 建立歸檔目錄
- mkdir -p /xxx/archive
chown -R postgres. /xxx/
- 開啟歸檔，並重啟資料庫
- wal_level = 'replica'
archive_mode = 'on'
archive_command = 'cp %p /xxx/archive/%f'
- 重啟資料庫
- pg_ctl restarat
- 執行全量備份
- pg_basebackup -h ip -D /xxx/backup -P -p 5432 -U postgres
- 如果有變化的資料，可以執行增量備份
- pg_receivewal -D /xxx/backup/ -p 5432
- 啟動備份例項，進行資料恢復
- vim postgresql.conf
recovery_target_name = 'huanyuan'
restore_command='cp /xxx/archive/%f %p'
recovery_target_action = 'promote'
- touch recovery.signal
- 啟動資料庫
- pg_ctl -D /xxx/backup start
- 關閉讀模式
- select pg_wal_replay_resume();
- 結合事務進行恢復
- 建立歸檔目錄
- mkdir -p /xxx/archive
chown -R postgres. /xxx/
- 開啟歸檔，並重啟資料庫
- wal_level = 'replica'
archive_mode = 'on'
archive_command = 'cp %p /xxx/archive/%f'
- 重啟資料庫
- pg_ctl restarat
- 執行全量備份
- pg_basebackup -h ip -D /xxx/backup -P -p 5432 -U postgres
- 如果有變化的資料，可以執行增量備份
- pg_receivewal -D /xxx/backup/ -p 5432
- 啟動備份例項，進行資料恢復
- vim postgresql.conf
recovery_target_xid = '487'
restore_command='cp /xxx/archive/%f %p'
recovery_target_action = 'promote'
touch recovery.signal
- 啟動資料庫
- pg_ctl -D /xxx/backup start
- 關閉讀模式
- select pg_wal_replay_resume();
- 結合LSN號碼進行恢復
- 建立歸檔目錄
- mkdir -p /xxx/archive
chown -R postgres. /xxx/
- 開啟歸檔，並重啟資料庫
- wal_level = 'replica'
archive_mode = 'on'
archive_command = 'cp %p /xxx/archive/%f'
- 重啟資料庫
- pg_ctl restarat
- 執行全量備份
- pg_basebackup -h ip -D /xxx/backup -P -p 5432 -U postgres
- 如果有變化的資料，可以執行增量備份
- pg_receivewal -D /xxx/backup/ -p 5432
- 啟動備份例項，進行資料恢復
- vim postgresql.conf
recovery_target_lsn = '0/4011BF8'
restore_command='cp /xxx/archive/%f %p'
recovery_target_action = 'promote'
touch recovery.signal
- 啟動資料庫
- pg_ctl -D /xxx/backup start
- 關閉讀模式
- select pg_wal_replay_resume();
- 冷備
### 邏輯備份
- 全庫備份
- pg_dump
- 庫級別備份
- 備份
- pg_dump -U postgres test >test.sql
- 恢復
- psql
create database test;
psql -U postgres test < test.sql
- 指定格式進行備份
- 備份
- pg_dump -Fc -U postgres test >test.dmp
- 恢復
- psql
create database test;
pg_restore -d test test.dmp
- 表級別備份
- 備份庫下某個模式所有的表
- 備份
- pg_dump -U postgres -t 'schema1.t*' test >test.sql
- 恢復
- psql -U postgres test < test.sql
- 備份單個表
- 備份
- pg_dump -t 表名 資料庫名 >dump.sql
- 恢復
- create database 資料庫名;
psql -U postgres 資料庫名< dump.sql
- schema級別備份
- pg_dumpall
- 全庫級別備份
- 備份
- pg_dumpall -U postgres > dumpall.sql
- 恢復
- psql -U postgres < dumpall.sql
- pg_dump VS pg_dumpall
- pg_dumpall 是一個用於寫出（"轉儲"）一個資料庫叢集裡的所有PostgreSQL 資料庫到一個指令碼檔案的工具。
- pg_dumpall 呼叫pg_dump
- pg_dumpall 還轉儲出所有資料庫公用的全域性物件。這些資訊目前包括資料庫使用者和組，以及適用於整個資料庫的訪問許可權
- pg_dumpall 無法轉儲"大物件"，因為pg_dump無法把這樣的物件轉儲到純文字檔案中。如果你的資料庫裡有大物件，那麼你應該使用pg_dump 的非文字輸出格式之一轉儲它們。