【PostgreSQL】入門學習筆記

2022 年 4 月 19 日
筆記
PostgreSQL

前言：

以下內容為前幾天在備考PostgreSQL入門考試時候做的筆記，經過了全職的兩天的奮戰與實驗，並最終順利通過了PCA初級認證考試。現在把我學習的筆記分享給大家，文中有對應的思維導圖圖片可供查看，內容與後面正文文本一致。另外，由於SQL語句部分比較基礎，基本上會一門資料庫就都會，所以此處部分省略掉不做過多記錄了。

以下，enjoy：

# PostgreSQL

## PostgreSQL的發展歷程

### 始於1986年的Postgres項目

### 1994年，新增了SQL語言解釋器，Postgres95誕生

### 1996年，更名 PostgreSQL,版本號從6.0開始

## PostgreSQL的安裝

### yum安裝

- //www.postgresql.org/download/linux/redhat/

- yum install -y //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安裝

- //yum.postgresql.org/rpmchart/

- postgresql14

- postgresql14-contrib

- postgresql14-libs

- postgresql14-server

- rpm -ivh postgresql14*

### 源碼的安裝

- wget //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

- //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

- //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 的非文本輸出格式之一轉儲它們。

Tags: PostgreSQL

【PostgreSQL】入門學習筆記

VirMach 便宜 VPS

QNews

【PostgreSQL】入門學習筆記

分享此文：

Related Posts

目標檢測數據集分析

Spring Security實現基於RBAC的許可權表達式動態訪問控制

山東風箏新高度：空間站對接都能模仿！還有航天員出艙

目標檢測數據集分析

VirMach 便宜 VPS

QNews

熱門文章

熱門搜尋