【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:

VirMach 便宜 VPS

QNews

QNews