postgres 讀書筆記

Gshelldon發表於2024-11-19

postgres 讀書筆記

第1章

預寫日誌

預寫日誌在客戶端寫入資料的時候首先寫入預寫日誌中，從原理上來說因為是順序寫效能會更好。

預寫日誌的配置。

在編譯安裝的時候可以指定--with-wal-segsize=size修改預設的預寫日誌檔案大小。

wal_level=replica
fsync=on
max_wal_size = 1GB
min_wal_size = 80MB

wal_level=replica 三個級別。

minimal 不能透過基礎備份和預寫日誌恢復資料庫。

replica 該級別支援預寫日誌的歸檔和複製。

logical 在replica級別的基礎智商新增了支援邏輯解碼所需要的資訊。

日誌記錄配置

# linux的預設配置，書上推薦使用csv格式
#log_destination = 'stderr'             # Valid values are combinations of
                                        # stderr, csvlog, jsonlog, syslog, and
                                        # eventlog, depending on platform.
                                        # csvlog and jsonlog require
                                        # logging_collector to be on.

# This is used when logging to stderr:
logging_collector = on  # Enable capturing of stderr, jsonlog,
                                        # and csvlog into log files. Required
                                        # to be on for csvlogs and jsonlogs.
                                        # (change requires restart)

# These are only used if logging_collector is on:
log_directory = 'log'                   # directory where log files are written,
                                        # can be absolute or relative to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'        # log file name pattern,
                                        # can include strftime() escapes
#log_file_mode = 0600                   # creation mode for log files,
                                        # begin with 0 to use octal notation
#log_rotation_age = 1d                  # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.

# 日誌到多大的時候重新生成一個新的日誌檔案。
#log_rotation_size = 10MB               # Automatic rotation of logfiles will

1.5 歸檔日誌

歸檔日誌就是對預寫日誌的歸檔可以對把資料恢復到一個具體的時間點。

# 是否開啟，需要重啟資料庫
archive_mode = on
# 歸檔的命令
archive_command = 'cp %p /data/postgres/archive/%f'
# 強制切換日誌的時間0表示關閉
#archive_timeout = 0

自動清理程序autovacuum

pgsql有版本記錄的功能，在執行update和delete之後資料庫不會直接刪除原來的資料而是標記為刪除狀態。在事務提交之後這些資料就沒有價值了，所以需要定期刪除。

#------------------------------------------------------------------------------
# AUTOVACUUM
#------------------------------------------------------------------------------

#autovacuum = on                        # Enable autovacuum subprocess?  'on'
                                        # requires track_counts to also be on.
#autovacuum_max_workers = 3             # max number of autovacuum subprocesses
                                        # (change requires restart)
#autovacuum_naptime = 1min              # time between autovacuum runs
#autovacuum_vacuum_threshold = 50       # min number of row updates before
                                        # vacuum
#autovacuum_vacuum_insert_threshold = 1000      # min number of row inserts
                                        # before vacuum; -1 disables insert
                                        # vacuums
#autovacuum_analyze_threshold = 50      # min number of row updates before
                                        # analyze
#autovacuum_vacuum_scale_factor = 0.2   # fraction of table size before vacuum
#autovacuum_vacuum_insert_scale_factor = 0.2    # fraction of inserts over table
                                        # size before insert vacuum
#autovacuum_analyze_scale_factor = 0.1  # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000  # maximum XID age before forced vacuum
                                        # (change requires restart)
#autovacuum_multixact_freeze_max_age = 400000000        # maximum multixact age
                                        # before forced vacuum
                                        # (change requires restart)
#autovacuum_vacuum_cost_delay = 2ms     # default vacuum cost delay for
                                        # autovacuum, in milliseconds;
                                        # -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1      # default vacuum cost limit for
                                        # autovacuum, -1 means use
                                        # vacuum_cost_limit

記憶體結構

本地記憶體、共享記憶體。

幾個引數：

#work_mem = 4MB
#maintenance_work_mem = 64MB
#temp_buffers = 8MB

shared_buffers = 128MB
#effective_cache_size = 4GB

常用命令總結

# 檢視所有的庫
select datname from pg_database;
# 或者 
\l

# 檢視已有的資料庫資訊
select oid,datname,datistemplate,datallowconn from pg_database;

修改日誌格式位csv格式

# 檢視日誌輸出位置變數
postgres=# show log_destination;

# vi postgresql.conf
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

# 熱載入配置
pg_ctl reload

pg_ctl 管理資料庫例項

~]$ pg_ctl --help
pg_ctl is a utility to initialize, start, stop, or control a PostgreSQL server.

Usage:
  pg_ctl init[db]   [-D DATADIR] [-s] [-o OPTIONS]
  pg_ctl start      [-D DATADIR] [-l FILENAME] [-W] [-t SECS] [-s]
                    [-o OPTIONS] [-p PATH] [-c]
  pg_ctl stop       [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
  pg_ctl restart    [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
                    [-o OPTIONS] [-c]
  pg_ctl reload     [-D DATADIR] [-s]
  pg_ctl status     [-D DATADIR]
  pg_ctl promote    [-D DATADIR] [-W] [-t SECS] [-s]
  pg_ctl logrotate  [-D DATADIR] [-s]
  pg_ctl kill       SIGNALNAME PID

postgres 的兩種分散式叢集實現

greenplum

事務能力很強。
citus

Citus中沒有全域性的事務快照，這和MyCAT等常見的分庫分表方案一樣。這樣可以簡化設計提升效能，帶來的問題就是不能保證全域性的一致性讀，而只能實現的最終一致性。

Postgres物件管理

資料庫本身是一個postgres的物件，一個資料庫可以包含多個模式。模式中又包含表、函式、及操作等資料庫物件。在新建資料庫時會預設建立public的模式。

1.建立一個schema

mydb=# create schema gxl
mydb=# \dn
      List of schemas
  Name  |       Owner       
--------+-------------------
 gxl    | postgres
 public | pg_database_owner

2.表的基本操作

建立表，如果直接使用建立表的語句預設情況下是建立在public的模式下。如果要指定是建立在哪個模式下使用schema.table_name(.......)的方式去建立一個表。

mydb=# \dt gxl.*
Did not find any relation named "gxl.*"  # 如果模式中沒有表就會返回這個提示

# 建立表
mydb=# create table gxl.test2(id int,name varchar(32), age int);
CREATE TABLE

# 檢視錶
mydb=# \dt gxl.*
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 gxl    | test2 | table | postgres
(1 row)

# 檢視錶結構
mydb=# \d gxl.test2;
                        Table "gxl.test2"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 id     | integer               |           |          | 
 name   | character varying(32) |           |          | 
 age    | integer               |           |          | 
 
# 修改表結構
mydb=# alter table gxl.test2 add gender varchar(1) default 'M';
ALTER TABLE

mydb=# \d gxl.test2;
                               Table "gxl.test2"
 Column |         Type          | Collation | Nullable |        Default         
--------+-----------------------+-----------+----------+------------------------
 id     | integer               |           |          | 
 name   | character varying(32) |           |          | 
 age    | integer               |           |          | 
 gender | character varying(1)  |           |          | 'M'::character varying

# 修改列
alter table gxl.test2 alter gender type varchar(10);
alter table gxl.test2 drop column gender;

# 刪除表
drop table gxl.test2;

3.表的約束

一種限制條件，比如說主鍵必須是唯一的，當出現重複的主鍵的時候會報錯。

主鍵
唯一鍵
外來鍵
檢查約束
非空約束
預設值約束

# 主鍵約束
create table testprimarykey(id int primary key,name varchar(20));
mydb=# \d testprimarykey;
                  Table "public.testprimarykey"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 id     | integer               |           | not null | 
 name   | character varying(20) |           |          | 
Indexes:
    "testprimarykey_pkey" PRIMARY KEY, btree (id)

mydb=# insert into testprimarykey values(1,'tom');
INSERT 0 1
mydb=# insert into testprimarykey values(2,'jerry');
INSERT 0 1
mydb=# insert into testprimarykey values(3,'jimi');
INSERT 0 1
mydb=# insert into testprimarykey values(1,'defan');
ERROR:  duplicate key value violates unique constraint "testprimarykey_pkey"
DETAIL:  Key (id)=(1) already exists.  # 主鍵衝突

4.表的碎片

Postgres和mysql邏輯刪除的方式原理都一樣，並不會立即釋放表空間。需要手動整理表空間來整理磁碟的碎片化空間。

在整理表空間的時候會有一個vacumm的程序可以透過ps -ef | grep vacu檢視。

# 建立新表插入資料然後檢視錶空間大小。
mydb=# create table testfragement(tid int,tname varchar(20));
CREATE TABLE
mydb=# insert  into testfragement select n,'myname_'||n from generate_series(1,50000) n;
INSERT 0 50000;

mydb=# select pg_size_pretty(pg_relation_size('testfragement'));
 pg_size_pretty 
----------------
 651 MB
(1 row)

mydb=# delete from testfragement;
DELETE 55000
mydb=# select pg_size_pretty(pg_relation_size('testfragement'));
 pg_size_pretty 
----------------
 651 MB
(1 row)

mydb=# vacuum testfragement;
VACUUM
mydb=# select pg_size_pretty(pg_relation_size('testfragement'));
 pg_size_pretty 
----------------
 0 bytes
(1 row)

# 檢視錶狀態
mydb=# \x
Expanded display is on.
mydb=# select * from pg_stat_user_tables where relname = 'testfragement';
-[ RECORD 1 ]-------+------------------------------
relid               | 16773
schemaname          | public
relname             | testfragement
seq_scan            | 2
seq_tup_read        | 55000
idx_scan            | 
idx_tup_fetch       | 
n_tup_ins           | 13085872
n_tup_upd           | 0
n_tup_del           | 55000
n_tup_hot_upd       | 0
n_live_tup          | 0  # 這兩個都變成0說明表空間已經被回收
n_dead_tup          | 0  # 這兩個都變成0說明表空間已經被回收
n_mod_since_analyze | 0
n_ins_since_vacuum  | 0
last_vacuum         | 2024-10-25 11:39:54.195119+08
last_autovacuum     | 2024-10-25 11:39:52.894711+08
last_analyze        | 
last_autoanalyze    | 2024-10-25 11:39:53.22663+08
vacuum_count        | 1   # 這裡整理表空間 +1 
autovacuum_count    | 2
analyze_count       | 0
autoanalyze_count   | 2

5.pgsql的統計資訊

pg_class: 記錄表和索引的行數、塊數等統計資訊。

pg_statistic: 記錄analyze命令建立的統計資訊，這些統計資訊由查詢最佳化器使用。

pg_stats檢視提供資料庫的統計資訊。

# 從系統表中獲取表的統計資訊
mydb=# select oid,relname,relpages,reltuples from pg_class where relname='testprimarykey';
-[ RECORD 1 ]-------------
oid       | 16768
relname   | testprimarykey
relpages  | 0
reltuples | -1

mydb=# select * from pg_stats where tablename='testprimarykey';

從上面的結果發現並不準確，因為表中是有資料的。

6.使用analyze命令手動收集資料庫統計資訊

注意：使用analyze命令會鎖表。

mydb=# analyze verbose testprimarykey;
INFO:  analyzing "public.testprimarykey"
INFO:  "testprimarykey": scanned 1 of 1 pages, containing 3 live rows and 1 dead rows; 3 rows in sample, 3 estimated total rows
ANALYZE

# 再次檢視系統表的統計
# 發信資料統計正常。

7.收集器配置項

mydb=# show track_
track_activities           track_commit_timestamp     track_functions            track_wal_io_timing        
track_activity_query_size  track_counts               track_io_timing

8.臨時表

只存在當前會話中，當會話結束會自動刪除並清理表空間。

因為只存在當前會話中不需要關心是否表重名。

# 建立臨時表
create temporary table temptable(
tid int primary key,
tname varchar(10));

9.索引

pg_indexs是一個檢視透過它獲取某個模式下的索引資訊。

# 檢視public schema下的索引資訊。
mydb=# select schemaname,tablename,indexname from pg_indexes where schemaname = 'public';
 schemaname |   tablename    |      indexname      
------------+----------------+---------------------
 public     | students       | students_pkey
 public     | test_1         | test_1_pkey
 public     | dept           | dept_pkey
 public     | testprimarykey | testprimarykey_pkey
 
# 查詢索引的詳細資訊
mydb=# \x
Expanded display is on.
mydb=# select * from pg_index where indrelid in (select oid from pg_class where relname = 'testprimarykey');
-[ RECORD 1 ]-------+------
indexrelid          | 16771
indrelid            | 16768
indnatts            | 1
indnkeyatts         | 1
indisunique         | t
indnullsnotdistinct | f
indisprimary        | t
indisexclusion      | f
indimmediate        | t
indisclustered      | f
indisvalid          | t
indcheckxmin        | f
indisready          | t
indislive           | t
indisreplident      | f
indkey              | 1
indcollation        | 0
indclass            | 1978
indoption           | 0
indexprs            | 
indpred             |

pgsql的特色索引。

部分索引
表示式索引

pgsql不能強制使用特定的索引，或者阻止pgsql使用順序掃描，所以在除錯的時候可以把順序掃描關了。

9.1 索引型別

普通索引、唯一索引、主鍵索引、組合索引、全文索引和雜湊索引。

10.清理索引索引

檢查清理重複的索引

select indrelid::regclass as tablename,array_agg(indexrelid::regclass) as indexes from pg_index group by indrelid,indkey having count(*) >1;

檢查長期未使用的索引。

mydb=# select relname,indexrelname,idx_scan from pg_catalog.pg_stat_user_indexes;

檢查無效的索引。

mydb=# select indexrelid,indisvalid from pg_index where indisvalid = 'f';

11.檢視

檢視是一種虛表，因此它本身不包含資料。檢視將作為一條select語句儲存在資料字典中。檢視依賴的表叫做基表，透過檢視展示表的部分資料。

# 建立檢視
create or replace view view1 as select * from testprimarykey where id <=1;
mydb=# select * from view1;
 id | name 
----+------
  1 | tom
(1 row)

並行查詢

預設情況只會進行順序掃描，當有where條件的時候就會進行並行查詢。

可以根據實際的物理機配置情況合理配置worker程序的數量，以提高pgsql的查詢速度。

# 建立一個表
mydb=# create table testtable1(id int, tname varchar(20));
mydb=# insert  into testtable1 select n,'myname_'||n from generate_series(1,5000000) n;

mydb=# explain analyze select * from testtable1 where tname='myname_10';
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..58878.99 rows=1 width=18) (actual time=0.920..579.144 rows=1 loops=1)
   Workers Planned: 2  # 計劃的worker
   Workers Launched: 2 # 實際執行的worker
   ->  Parallel Seq Scan on testtable1  (cost=0.00..57878.89 rows=1 width=18) (actual time=375.012..566.492 rows=0 loops=3)
         Filter: ((tname)::text = 'myname_10'::text)
         Rows Removed by Filter: 1666666
 Planning Time: 0.177 ms
 Execution Time: 579.193 ms
(8 rows)
# Seq Scan 順序掃描

mydb=# set max_parallel_workers_per_gather = 0;
mydb=#  explain analyze select * from testtable1 where tname='myname_10';
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Seq Scan on testtable1  (cost=0.00..94336.12 rows=1 width=18) (actual time=0.365..1828.760 rows=1 loops=1)
   Filter: ((tname)::text = 'myname_10'::text)
   Rows Removed by Filter: 4999999
 Planning Time: 0.145 ms
 Execution Time: 1828.814 ms
(5 rows)

# 在關閉work程序之後查詢時間增長了3倍多。

worker 查詢的相關引數。

#max_worker_processes = 8               # (change requires restart)
#max_parallel_workers_per_gather = 2    # limited by max_parallel_workers
#max_parallel_workers = 8               # number of max_worker_processes that

#parallel_setup_cost = 1000.0   # same scale as above
#parallel_tuple_cost = 0.1              # same scale as above

#min_parallel_table_scan_size = 8MB
#min_parallel_index_scan_size = 512kB

1.並行掃描

順序掃描在沒有索引的情況下，不帶條件的select。
並行索引掃描，帶where條件且條件是索引的內容。
並行點陣圖掃描有索引的條件帶or關鍵字的查詢。

2.檢視錶鎖

模擬一個表鎖。

# 檢視錶的oid
# pg_class 表存了表的資訊
mydb=#  select oid,relname,relkind,relfilenode from pg_class where relname='testtable1';
  oid  |  relname   | relkind | relfilenode 
-------+------------+---------+-------------
 16787 | testtable1 | r       |       16787
 
# 啟動一個事物修改資料
postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=# start transaction;
START TRANSACTION
mydb=*# update testtable1 set tname = 'gongxiaoliao' where id = 946026;
UPDATE 1

# 此時修改表結構,發現執行很慢
mydb=# alter table testtable1 add dno int;

# 檢視鎖表資訊
postgres=# \x
Expanded display is on.
postgres=# select * from pg_locks where relation = '16787';
-[ RECORD 1 ]------+------------------------------
locktype           | relation
database           | 16397
relation           | 16787
page               | 
tuple              | 
virtualxid         | 
transactionid      | 
classid            | 
objid              | 
objsubid           | 
virtualtransaction | 5/31
pid                | 793183
mode               | AccessExclusiveLock
granted            | f
fastpath           | f
waitstart          | 2024-10-28 12:00:23.476624+08
-[ RECORD 2 ]------+------------------------------
locktype           | relation
database           | 16397
relation           | 16787
page               | 
tuple              | 
virtualxid         | 
transactionid      | 
classid            | 
objid              | 
objsubid           | 
virtualtransaction | 4/305
pid                | 793068
mode               | RowExclusiveLock
granted            | t
fastpath           | f
waitstart          |

# 當更新語句提交之後就可以正常修表結構了，隨及返回了更新資訊。
mydb=# alter table testtable1 add dno int;
ALTER TABLE

# 檢查鎖等待的SQL語句
---------------------------
postgres=# with  
t_wait as  
(  
 select 
   a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,
   a.classid,a.granted,a.objid,a.objsubid,a.pid,
   a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,
   b.state,b.query,b.xact_start,b.query_start,b.usename,
   b.datname,b.client_addr,b.client_port,b.application_name  
 from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted  
),  
t_run as  
(  
 select 
   a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,
   a.classid,a.granted,a.objid,a.objsubid,a.pid,
   a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,
   b.state,b.query,b.xact_start,b.query_start,b.usename,
   b.datname,b.client_addr,b.client_port,b.application_name  
 from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted  
),  
t_overlap as  
(  
 select r.* from t_wait w join t_run r on  
 (  
  r.locktype is not distinct from w.locktype and  
  r.database is not distinct from w.database and  
  r.relation is not distinct from w.relation and  
  r.page is not distinct from w.page and  
  r.tuple is not distinct from w.tuple and  
  r.virtualxid is not distinct from w.virtualxid and  
  r.transactionid is not distinct from w.transactionid and  
  r.classid is not distinct from w.classid and  
  r.objid is not distinct from w.objid and  
  r.objsubid is not distinct from w.objsubid and  
  r.pid <> w.pid  
 )  
),  
t_unionall as  
(  
 select r.* from t_overlap r  
 union all  
 select w.* from t_wait w  
)  
select 
locktype,datname,relation::regclass,page,tuple,
virtualxid,transactionid::text,classid::regclass,
objid,objsubid, 
string_agg(
'Pid: '
||case when pid is null 
	   then 'NULL' else pid::text end||chr(10)||'Lock_Granted: '
||case when granted is null 
	   then 'NULL' else granted::text end||' , Mode: '
||case when mode is null 
	   then 'NULL' else mode::text end||' , FastPath: '
||case when fastpath is null 
	   then 'NULL' else fastpath::text end||' , VirtualTransaction: '
||case when virtualtransaction is null 
	   then 'NULL' else virtualtransaction::text end||' , Session_State: '
||case when state is null 
	   then 'NULL' else state::text end||chr(10)||'Username: '
||case when usename is null 
	   then 'NULL' else usename::text end||' , Database: '
||case when datname is null 
	   then 'NULL' else datname::text end||' , Client_Addr: '
||case when client_addr is null 
	   then 'NULL' else client_addr::text end||' , Client_Port: '
||case when client_port is null 
	   then 'NULL' else client_port::text end||' , Application_Name: '
||case when application_name is null 
	   then 'NULL' else application_name::text end
||chr(10)||'Xact_Start: '
||case when xact_start is null 
	   then 'NULL' else xact_start::text end||' , Query_Start: '
||case when query_start is null 
	   then 'NULL' else query_start::text end||' , Xact_Elapse: '
||case when (now()-xact_start) is null 
	   then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '
||case when (now()-query_start) is null 
	   then 'NULL' else (now()-query_start)::text end
||chr(10)||'SQL (Current SQL in Transaction): '||chr(10)
||case when query is null then 'NULL' else query::text end,chr(10)
||'--------'||chr(10)  
order by  
 ( case mode  
  when 'INVALID' then 0  
  when 'AccessShareLock' then 1  
  when 'RowShareLock' then 2  
  when 'RowExclusiveLock' then 3  
  when 'ShareUpdateExclusiveLock' then 4  
  when 'ShareLock' then 5  
  when 'ShareRowExclusiveLock' then 6  
  when 'ExclusiveLock' then 7  
  when 'AccessExclusiveLock' then 8  
  else 0  
 end ) desc,  
 (case when granted then 0 else 1 end) 
) as lock_conflict 
from t_unionall  
group by locktype,datname,relation,page,tuple,virtualxid,
transactionid::text,classid,objid,objsubid;

3.死鎖

和死鎖相關的幾個引數。

deadlock_timeout
lock_timeout
statement_timeout

檢視死鎖的語句

# 監控死鎖資訊的SQL指令碼
----------------------------------
SELECT     blocked_locks.pid AS blocked_pid,
           blocked_activity.usename AS blocked_user,
           blocking_locks.pid AS blocking_pid,
           blocking_activity.usename AS blocking_user,
           blocked_activity.query AS blocked_statement,
           blocking_activity.query AS current_statement_in_blocking_process
         FROM  pg_catalog.pg_locks blocked_locks
         JOIN pg_catalog.pg_stat_activity blocked_activity 
            ON blocked_activity.pid = blocked_locks.pid
         JOIN pg_catalog.pg_locks blocking_locks 
            ON blocking_locks.locktype = blocked_locks.locktype
            AND blocking_locks.DATABASE IS NOT DISTINCT 
         FROM blocked_locks.DATABASE
            AND blocking_locks.relation IS NOT DISTINCT 
         FROM blocked_locks.relation
            AND blocking_locks.page IS NOT DISTINCT 
         FROM blocked_locks.page
            AND blocking_locks.tuple IS NOT DISTINCT 
         FROM blocked_locks.tuple
            AND blocking_locks.virtualxid IS NOT DISTINCT 
         FROM blocked_locks.virtualxid
            AND blocking_locks.transactionid IS NOT DISTINCT 
         FROM blocked_locks.transactionid
            AND blocking_locks.classid IS NOT DISTINCT 
         FROM blocked_locks.classid
            AND blocking_locks.objid IS NOT DISTINCT 
         FROM blocked_locks.objid
            AND blocking_locks.objsubid IS NOT DISTINCT 
         FROM blocked_locks.objsubid
            AND blocking_locks.pid != blocked_locks.pid
         JOIN pg_catalog.pg_stat_activity blocking_activity 
         ON blocking_activity.pid = blocking_locks.pid
         WHERE NOT blocked_locks.GRANTED;

使用者管理

create user和create role兩者都差不多，create user建立的使用者預設自帶login屬性。

pgsql在初始化的時候總是包含一個預定義角色，此角色始終是超級使用者在預設情況下這個使用者名稱與系統使用者同名，通常情況下被命名為postgres。

# 檢視當前存在的使用者
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 admin2    | Superuser                                                  | {}
 jgjadmin  |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

屬性	說明
login	可以用作資料庫連線的初始角名
superuser	資料庫超級使用者
createdb	建立資料庫許可權
createrole	建立或刪除其它的普通使用者
replication	做流複製用到的一個使用者屬性
password	指定密碼模式
inherit	整合使用者的許可權

pg資料庫中的使用者表和角色表

postgres=# select * from pg_user;
 usename  | usesysid  | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig 
----------+-----------+-------------+----------+---------+--------------+----------+----------+-----------
 admin2   |     16388 | f           | t        | f       | f            | ******** |          | 
 jgjadmin | 119922631 | f           | f        | f       | f            | ******** |          | 
 postgres |        10 | t           | t        | t       | t            | ******** |          | 
(3 rows)

postgres=# select rolname from pg_roles;
          rolname          
---------------------------
 pg_database_owner
 pg_read_all_data
 pg_write_all_data
 pg_monitor
 pg_read_all_settings
 pg_read_all_stats
 pg_stat_scan_tables
 pg_read_server_files
 pg_write_server_files
 pg_execute_server_program
 pg_signal_backend
 pg_checkpoint
 admin2
 jgjadmin
 postgres
(15 rows)

-- 修改使用者的密碼 --
create user tom;
alter user tom with password 'pas123';

postgresql的passwordcheck外掛可以檢查密碼的複雜度。

# 如果提前編譯了
[root@jgj-postgres passwordcheck]# ls
expected  Makefile  passwordcheck.c  passwordcheck.o  passwordcheck.so  sql
[root@jgj-postgres passwordcheck]# pwd
/opt/postgresql-15.7/contrib/passwordcheck
[root@jgj-postgres passwordcheck]# make install

vi /data/postgresql/data/postgresql.conf
shared_preload_libraries = 'passwordcheck'

# 如果是多個模組就這樣新增
~]# grep shared_pr /data/postgres/postgresql.conf 
shared_preload_libraries = 'file_fdw,passwordcheck'	# (change requires restart)

postgres=# show shared_preload_libraries;
 shared_preload_libraries 
--------------------------
 file_fdw,passwordcheck
 
# 會自動做一些密碼規範的校驗
postgres=# alter user tom with password 'pas123';
ERROR:  password is too short

1.許可權管理

任何資料庫物件都是所有者的，因此所有者有的物件不必單獨授權；所有者可以隱式的把物件的操作許可權授權給其它的使用者。

pgsql中的許可權有兩種，一種在建立使用者的時候指定的許可權，一種是grant和revoke。

# 建立一個使用者設定密碼，授予建立資料庫的許可權，能夠遠端登陸資料庫。
postgres=# create role cdb createdb password 'abc@123aaa' login;
CREATE ROLE

# 或者
create user cdb;
alter user cdb with createdb  password 'aaa@1234!';

postgres=# \du+ cdb;
                  List of roles
 Role name | Attributes | Member of | Description 
-----------+------------+-----------+-------------
 cdb       | Create DB  | {}        |

2.使用grant和revoke管理使用者許可權

這兩個命令可以管理如下許可權：

在資料庫中建立模式的許可權。
在指定資料庫中建立臨時表的許可權。
連線某個資料庫的許可權。
在某個資料庫中建立物件的許可權如：表、檢視和函式。
在一些表中執行DML
對序列進行操作的許可權。
把表、索引建立到指定空間中的許可權。

# 直接建立使用者沒有授權如果能登陸預設是有列出資料庫的許可權。
mydb=> \dn
      List of schemas
  Name  |       Owner       
--------+-------------------
 gxl    | postgres
 public | pg_database_owner
(2 rows)

mydb=> 
mydb=> \d
             List of relations
 Schema |      Name      | Type  |  Owner   
--------+----------------+-------+----------
 public | dept           | table | postgres
 public | students       | table | pguser
 public | test_1         | table | pguser
 public | testfragement  | table | postgres
 public | testprimarykey | table | postgres
 public | testtable1     | table | postgres
 public | view1          | view  | postgres
(7 rows)

mydb=> select * from testtable1 limit 10;
ERROR:  permission denied for table testtable1

# 授權cdb使用者能夠crud mydb庫中預設public中的表。
mydb=# grant select,insert,update,delete on all tables in schema public to cdb;

# 此時已經有許可權去檢視錶中內容
mydb=> select * from testtable1 limit 10;
 id |   tname   | dno 
----+-----------+-----
  1 | myname_1  |    
  2 | myname_2  |    
  3 | myname_3  |    
  4 | myname_4  |    
  5 | myname_5  |    
  6 | myname_6  |    
  7 | myname_7  |    
  8 | myname_8  |    
  9 | myname_9  |    
 10 | myname_10 |    
(10 rows)

# 此時授權的操作只對已經存在資料庫有效，後續建立的新物件依然沒有許可權，需要對使用者新增一個預設許可權。
alter default privileges grant select,insert,update,delete on tables to cdb;

3.admin option和grant option

admin option在選項授予角色時，角色的被授予者可以將得到的角色轉授給其它角色。

grant option選項授權時，許可權的被授予者可以將許可權賦予其它使用者。

邏輯關係演示。

# 建立一個role1 和兩個使用者A\B。
mydb=# create role role1;
CREATE ROLE
mydb=# create  user A with password '126.comM';
CREATE ROLE
mydb=# create  user B with password '126.comM';

# 授權使用者A
mydb=# grant select,update,delete on all tables in schema public to role1;
GRANT
mydb=# grant role1 to A with admin option;
GRANT ROLE

# 使用使用者A登陸並把A使用者得到的許可權授權給B使用者
# pgsql 的使用者在預設配置下建立的大寫的使用者轉換為小寫。
~]$ psql -W -h `hostname -I` mydb a
mydb=> grant role1 to b;
GRANT ROLE

# 取消的命令
mydb=# revoke role1 from a;
REVOKE ROLE
# 如果缺少許可權就會報錯

# 許可權轉讓
mydb=# grant select on testtable1 to a with grant option;

[postgres@pgsql-jgj-test ~]$ psql -W -h `hostname -I` mydb a
Password: 
psql (15.8)
Type "help" for help.

mydb=> grant select on testtable1 to b;
# 此時b有了a的查詢許可權

# 如果要單獨取消b的許可權
 revoke select on testtable1 from b cascade;

4.分組的許可權管理

在使用者過多的時候我們使用組進行管理使用者，這樣久只需要對組進行授權管理。簡化管理量。

組角色中的成員會自動繼承組角色的許可權，組角色和成員角色是父子關係。

# 建立組角色
create role father login nosuperuser nocreatedb nocreaterole password '126.comM' inherit;

inherit  # 組角色
         # pg不允許超級許可權透過組的方式進行繼承

# 授權組的許可權
mydb=# grant connect on database mydb to father;
GRANT
mydb=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=# grant usage on schema public to father;
GRANT
mydb=# grant select on public.testtable1 to father;
GRANT

# 把組許可權賦予成員
create role child1 login nosuperuser nocreatedb nocreaterole password '126.comM' inherit;
grant father to child1;

# 登陸訪問測試
[postgres@pgsql-jgj-test ~]$ psql -W -h `hostname -I` mydb child1
Password: 
psql (15.8)
Type "help" for help.
mydb=> select * from testtable1 limit 10;
 id |   tname   | dno 
----+-----------+-----
  1 | myname_1  |    
  2 | myname_2  |    
  3 | myname_3  |    
  4 | myname_4  |    
  5 | myname_5  |    
  6 | myname_6  |    
  7 | myname_7  |    
  8 | myname_8  |    
  9 | myname_9  |    
 10 | myname_10 |    
(10 rows)

mydb=> update testtable1 set tname='myname_gxl' where id=1;
ERROR:  permission denied for table testtable1

5.使用set role命令顯示啟用角色的許可權

使用場景：

建立兩個角色兩個角色有不同的許可權，把兩個角色同事授權給一個子角色，然後再把這個子角色授權給特定的使用者，這個使用者在登陸資料庫的時候在同一會話中可以透過set role來修改自己的角色來獲得不同的許可權。

6.審計日誌

儲存資料庫系統中發生的特定事件資訊，增加配置之後會給資料庫代理額外的工作量所以需要合理的配置才不會影響效能。或者使用pgaudit的外掛，可以審計DDL和DML sql語句。

推薦的線上配置。

https://www.postgresql.org/docs/16/runtime-config-logging.html

log_destination = 'csv'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_connections = on
log_disconnections = on
log_statement = ddl
log_min_duration_statement = 60s
log_checkpoints = on
log_lock_waits = on
deadlock_timeout = 1s

備份與恢復

1.設定歸檔日誌

歸檔日誌是為了備份重做日誌。

# 建立歸檔日誌的目錄
mkdir -p  /data/postgres/archlog/

# 修改配置檔案並重啟pgsql
archive_mode = on
archive_command = 'cp %p /data/postgres/archlog/%f'
wal_level = replica

# 登陸資料庫檢視配置
postgres=# show archive_mode;
 archive_mode 
--------------
 on
(1 row)

postgres=# show archive_command;
            archive_command             
----------------------------------------
 cp %p /data/postgresql/archlog/%f
(1 row)

postgres=# show wal_level;
 wal_level 
-----------
 replica
(1 row)

# 檢視預寫日誌列表
select * from pg_ls_waldir() order by modification desc;

# 手動切換日誌
# checkpoint 會觸發完全的檢查點把記憶體中的髒資料寫入磁碟。
checkpoint;
select pg_switch_wal()

# 再次檢視預寫日誌列表
select * from pg_ls_waldir() order by modification desc;

2.管理過期的歸檔日誌

在歸檔日誌開啟後會產生大量的歸檔日誌，可以使用下面幾種方式來清理。

歸檔命令寫成指令碼的方式，在執行歸檔的同時也要清理。

使用系統定時任務。

find /data/postgresql/data/archive -type f -name "00000001*" -mtime +7 -delete

使用pgsq自帶的命令。

這裡主要使用pg自帶的管理工具。

[postgres@pgsql-jgj-test data]$ pg_controldata /data/postgres
pg_control version number:            1300
Catalog version number:               202209061
Database system identifier:           7406166691963946602
Database cluster state:               in production
pg_control last modified:             Wed 30 Oct 2024 03:07:39 PM CST
Latest checkpoint location:           0/770000C0
Latest checkpoint's REDO location:    0/77000088
Latest checkpoint's REDO WAL file:    000000020000000000000077

# 表示可以刪除000000020000000000000077之前的檔案都可以刪除，表示執行到這裡的時候記憶體中的髒資料都已經持久化。

# 清理歸檔日誌。
[postgres@pgsql-jgj-test archlog]$ pg_archivecleanup -d /data/postgres/archlog/ 000000020000000000000077
pg_archivecleanup: keeping WAL file "/data/postgres/archlog//000000020000000000000077" and later
pg_archivecleanup: removing file "/data/postgres/archlog//00000002000000000000006F"
pg_archivecleanup: removing file "/data/postgres/archlog//000000020000000000000070"
pg_archivecleanup: removing file "/data/postgres/archlog//000000020000000000000071"
pg_archivecleanup: removing file "/data/postgres/archlog//000000020000000000000072"
pg_archivecleanup: removing file "/data/postgres/archlog//000000020000000000000073"
pg_archivecleanup: removing file "/data/postgres/archlog//000000020000000000000074"
pg_archivecleanup: removing file "/data/postgres/archlog//000000020000000000000075"
pg_archivecleanup: removing file "/data/postgres/archlog//000000020000000000000076"

# 清理預寫日誌
pg_archivecleanup -d /data/postgres/pg_wal/ 000000020000000000000077

3.邏輯備份

pg_dump
pg_dumpall

4.檔案級別備份

停止pg資料庫，然後把資料目錄備份，有問題的時候再把資料目錄回滾這種方式需要停止資料庫在生產上不太適合使用。

5.使用pg_basebackup完成熱備與恢復

# pg_basebackup熱備份需要開啟歸檔模式。
----------------------# 完成一個備份------------------------
mkdir /data/pg_backup

~]$ pg_basebackup -Ft -v -Xs -D /data/pg_backup/$(date +%F)/
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/78000028 on timeline 2
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_1028668"
pg_basebackup: write-ahead log end point: 0/78000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

# 檢視備份的內容
~]$ ll /data/pg_backup/2024-10-31/
total 334312
-rw------- 1 postgres postgres 275368448 Oct 31 15:10 16396.tar
-rw------- 1 postgres postgres    274234 Oct 31 15:10 backup_manifest
-rw------- 1 postgres postgres  49908224 Oct 31 15:10 base.tar
-rw------- 1 postgres postgres  16780288 Oct 31 15:10 pg_wal.tar


----------------------# 恢復一個備份------------------------
# 清理原來的資料
postgres]$ pwd
/data/postgres
postgres]$ rm -fr ./*

# 先將備份的檔案解壓到pgsql的資料目錄
tar -xf base.tar -C /data/postgres/
2024-10-31]$ ll /data/postgres/
total 60
drwxrwxr-x 2 postgres postgres  182 Oct 31 15:10 archlog
-rw------- 1 postgres postgres  227 Oct 31 15:10 backup_label
-rw-rw-r-- 1 postgres postgres  232 Sep  3 10:49 backup_label.old
drwx------ 6 postgres postgres   46 Sep  3 10:49 base
drwx------ 2 postgres postgres 4096 Oct 31 15:18 global
drwx------ 2 postgres postgres 4096 Oct 28 00:00 log
drwx------ 2 postgres postgres    6 Sep  3 10:49 pg_commit_ts
drwx------ 2 postgres postgres    6 Sep  3 10:49 pg_dynshmem
-rw------- 1 postgres postgres 4793 Sep  3 10:49 pg_hba.conf
-rw------- 1 postgres postgres 1636 Sep  3 10:49 pg_ident.conf
drwx------ 4 postgres postgres   68 Oct 31 15:10 pg_logical
drwx------ 4 postgres postgres   36 Sep  3 10:49 pg_multixact
drwx------ 2 postgres postgres    6 Sep  3 10:49 pg_notify
drwx------ 2 postgres postgres    6 Oct 31 15:10 pg_replslot
-rw-rw-r-- 1 postgres postgres  109 Oct 30 14:26 pg_rman_recovery.conf
drwx------ 2 postgres postgres    6 Sep  3 10:49 pg_serial
drwx------ 2 postgres postgres    6 Sep  3 10:49 pg_snapshots
drwx------ 2 postgres postgres    6 Oct 30 15:05 pg_stat
drwx------ 2 postgres postgres    6 Sep  3 10:49 pg_stat_tmp
drwx------ 2 postgres postgres    6 Sep  3 10:49 pg_subtrans
drwx------ 2 postgres postgres    6 Sep  3 10:52 pg_tblspc
drwx------ 2 postgres postgres    6 Sep  3 10:49 pg_twophase
-rw------- 1 postgres postgres    3 Sep  3 10:49 PG_VERSION
drwx------ 3 postgres postgres   28 Oct 31 15:18 pg_wal
drwx------ 2 postgres postgres   18 Sep  3 10:49 pg_xact
-rw------- 1 postgres postgres   88 Sep  3 10:49 postgresql.auto.conf
-rw-rw-r-- 1 postgres postgres 8230 Oct 30 15:05 postgresql.conf
-rw------- 1 postgres postgres   26 Oct 31 15:10 tablespace_map
-rw-rw-r-- 1 postgres postgres   26 Sep  3 10:49 tablespace_map.old

# 將歸檔日誌解壓到自定義目錄
mkdir /data/postgres/back_wal_log
tar -xf pg_wal.tar -C /data/postgres/back_wal_log

# 設定恢復引數
vi postgresql.conf
restore_command = 'cp /data/postgres/back_wal_log/%f %p'
recovery_target = 'immediate'   # 儘快的恢復

# 告訴postgres要執行恢復的操作。
touch /data/postgres/recovery.signal

# 啟動資料庫
pg_ctl start -D ${PGDATA}

# 是隻讀狀態
mydb=# create schema ns2;
ERROR:  cannot execute CREATE SCHEMA in a read-only transaction

~]$ pg_controldata -D /data/postgres/data/
pg_control version number:            1300
Catalog version number:               202209061
Database system identifier:           7432142558057729866
# 正在恢復
Database cluster state:               in archive recovery
----- # 或者使用
# 呼叫函式關閉只讀模式  /或者配置promote：
select pg_wal_replay_resume();    
-- 該系統函式用於在前面呼叫之後恢復WAL檔案的回放

# 執行恢復，這步執行之後會把恢復的標誌檔案清理
~]$ pg_ctl promote -D /data/postgres/data/
waiting for server to promote.... done
server promoted

# 表示執行正常的狀態
[postgres@my-pgsql data]$ pg_controldata -D /data/postgres/data/
pg_control version number:            1300
Catalog version number:               202209061
Database system identifier:           7432142558057729866
Database cluster state:               in production

5.連續歸檔基於時間點的恢復

# 建立一個表並插入資料
create table bak_test(id int,time timestamp);
insert into bak_test values(1,now());

mydb=# select * from bak_test;
 id |           time            
----+---------------------------
  1 | 2024-11-01 15:49:07.18224
(1 row)

# 切換日誌檔案並觸發一個檢查點，這裡select是確保預寫日誌能夠儘快的被歸檔
# 或者調整archive_timeout的時間，生產中建議為60s。
select pg_switch_wal();
checkpoint;

# 這個時候會產生一個歸檔檔案
[root@my-pgsql archlog]# ll
total 32772
-rw------- 1 postgres postgres 16777216 Nov  1 12:00 000000010000000000000019
-rw------- 1 postgres postgres 16777216 Nov  1 15:50 00000002000000000000001A
-rw------- 1 postgres postgres       33 Nov  1 15:32 00000002.history

# 建立一個基礎備份
pg_basebackup -Ft -P -R -v -D /data/postgres/pgbackup/

     # 此時的備份和歸檔檔案
[postgres@my-pgsql postgres]$ ll archlog/ pgbackup/
archlog/:
total 49156
-rw------- 1 postgres postgres 16777216 Nov  1 17:02 000000010000000000000001
-rw------- 1 postgres postgres 16777216 Nov  1 17:04 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Nov  1 17:04 000000010000000000000003
-rw------- 1 postgres postgres      338 Nov  1 17:04 000000010000000000000003.00000028.backup

pgbackup/:
total 47076
-rw------- 1 postgres postgres   180806 Nov  1 17:04 backup_manifest
-rw------- 1 postgres postgres 31237120 Nov  1 17:04 base.tar
-rw------- 1 postgres postgres 16778752 Nov  1 17:04 pg_wal.tar

# 插入第二條資料
insert into bak_test values(2,now());  -- 用於恢復到指定的時間點
select pg_switch_wal();
checkpoint;
select * from bak_test;

# 插入第三條資料
insert into bak_test values(3,now()); -- 測試恢復到標記的位置
select pg_create_restore_point('my_restore_point');
select pg_switch_wal();
checkpoint;
mydb=# select * from bak_test;
 id |            time            
----+----------------------------
  1 | 2024-11-01 17:02:34.125168
  2 | 2024-11-04 11:25:06.65344
  3 | 2024-11-04 11:25:23.605243
(3 rows)


# 插入第四條資料
insert into bak_test values(4,now());  -- 測試恢復到指定的事務上
  
  # 獲取當前事務ID
  select txid_current();  --741

select pg_switch_wal();
checkpoint;
mydb=# select * from bak_test;
 id |            time            
----+----------------------------
  1 | 2024-11-01 17:02:34.125168
  2 | 2024-11-04 11:25:06.65344
  3 | 2024-11-04 11:25:23.605243
  4 | 2024-11-04 11:26:03.3499     # id是4指定事務的時候插入的id
(4 rows)

# 插入第五條資料
insert into bak_test values(5,now()); -- 測試恢復到最近的時間點
select pg_switch_wal();
checkpoint;
select * from bak_test;

恢復測試,關閉postgres並刪除資料。

基於時間點的恢復

恢復資料：

先恢復全備，將備份檔案解壓到資料目錄下。
將預寫日誌pg_wal.tar解壓到pg_wal目錄下。

20241104]$ tar -xf base.tar -C /data/postgres/data/
20241104]$ tar -xf pg_wal.tar -C /data/postgres/data/pg_wal/

# 建立恢復訊號檔案
rm -fr standby.signal
touch recovery.signal

# 修改主配置檔案，配置要恢復到的時間點。
restore_command = 'cp /data/postgres/archlog/%f %p'

# 這裡的時間點需要比記錄的時間點稍微大一點才能夠包含資料被修改的時間。
recovery_target_time = '2024-11-04 11:25:06'

恢復到指定的恢復點

# 修改主配置檔案，配置要恢復到的時間點。
restore_command = 'cp /data/postgres/archlog/%f %p'
recovery_target_name = 'my_restore_point'

# 在資料目錄下新增恢復檔案
touch recovery.signal

# 重啟資料庫驗證資料
mydb=# select * from bak_test;
 id |            time            
----+----------------------------
  1 | 2024-11-01 17:02:34.125168
  2 | 2024-11-04 11:25:06.65344
  3 | 2024-11-04 11:25:23.605243
(3 rows)

恢復到指定的事務

restore_command = 'cp /data/postgres/archlog/%f %p'
recovery_target_xid = '741'

# 在資料目錄下新增恢復檔案
touch recovery.signal

# 驗證
mydb=# select * from bak_test;
 id |            time            
----+----------------------------
  1 | 2024-11-01 17:02:34.125168
  2 | 2024-11-04 11:25:06.65344
  3 | 2024-11-04 11:25:23.605243
  4 | 2024-11-04 11:26:03.3499
(4 rows)

恢復到最近時間點

# 修改主配置檔案
restore_command = 'cp /data/postgres/archlog/%f %p'
recovery_target_timeline = 'latest'

# 在資料目錄下新增恢復檔案
touch recovery.signal

#重啟pgsql驗證資料
mydb=# select * from bak_test;
 id |            time            
----+----------------------------
  1 | 2024-11-01 17:02:34.125168
  2 | 2024-11-04 11:25:06.65344
  3 | 2024-11-04 11:25:23.605243
  4 | 2024-11-04 11:26:03.3499
  5 | 2024-11-04 11:26:57.197616   # id是5的資料
(5 rows)

第三方備份工具pg_rman

pg_rman是第三方工具需要在在github下載，支援熱備，增量備份，需要pgsql開啟歸檔日誌並且和pg裝在同一臺機器上，在使用的時候需要進行初始化配置，建議不要和pg資料盤在同一磁碟。

https://github.com/ossc-db/pg_rman/releases/tag/V1.3.16

根據具體的pg版本下載合適的包。

# 依賴包
wget https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-7.9-x86_64/postgresql15-libs-15.8-1PGDG.rhel7.x86_64.rpm
yum localinstall -y postgresql15-libs-15.8-1PGDG.rhel7.x86_64.rpm

# pg_rman
wget https://github.com/ossc-db/pg_rman/releases/download/V1.3.16/pg_rman-1.3.16-1.pg15.rhel7.x86_64.rpm
yum localinstall -y pg_rman-1.3.16-1.pg15.rhel7.x86_64.rpm

# 檢視安裝位置
rpm -ql pg_rman 
/usr/pgsql-15/bin/pg_rman

# 初始化pg_rman
# 建立目錄
[postgres@my-pgsql ~]$ mkdir -p /data/postgres/pg_rman_backup/{fullbak,archlog,srvlog}

# 配置pg_rman的初始化配置，備份的路徑及日誌檔案路徑
~]# cat /etc/profile.d/pg_rman.sh 
export PATH=$PATH:/usr/pgsql-15/bin/

# 這個表示pg資料庫服務的日誌路徑
export SRVLOG_PATH=/data/postgres/data/log
# 這個表示pg資料庫歸檔日誌的存放路徑就像mysql的binlog,在恢復的時候pgsql會讀取這個路徑下的歸檔日誌。
export ARCLOG_PATH=/data/postgres/archlog/
# 這個定義的是pg_rman備份的主資料目錄
export BACKUP_PATH=/data/postgres/pg_rman_backup/fullbak

~]# source /etc/profile.d/pg_rman.sh

~]# pg_rman init
INFO: ARCLOG_PATH is set to '/data/postgres/archlog/'
INFO: SRVLOG_PATH is set to '/data/postgres/data/log'

執行一次全備

pg_rman的每次備份必須透過check選項去做校驗，不然無法使用。

pg_rman的每次備份具備以下特性。

僅使用一條命令即可對整個資料庫包含表空間進行備份。
支援增量備份和備份檔案壓縮。
支援管理備份版本並顯示備份目錄。
支援儲存快照。

# 執行一次全備
pg_rman backup --backup-mode=full --with-serverlog --progress -U postgres -h /tmp/

backup                # 備份
--backup-mode=full    # 全備
--with-serverlog      # 服務日誌
--progress            # 顯示備份進度
-h                    # 指定postgresql主機名或者socket目錄的位置
-U                    # 連線的使用者名稱

[postgres@my-pgsql ~]$ pg_rman validate
INFO: validate: "2024-11-05 10:19:29" backup, archive log files and server log files by CRC
INFO: backup "2024-11-05 10:19:29" is valid
[postgres@my-pgsql ~]$ pg_rman show
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2024-11-05 10:19:29  2024-11-05 10:19:31  FULL    20MB     3  OK

進行一次增量備份

# 檢視原始資料
mydb=# select * from bak_test;
 id |            time            
----+----------------------------
  1 | 2024-11-01 17:02:34.125168
  2 | 2024-11-04 11:25:06.65344
  3 | 2024-11-04 11:25:23.605243
  4 | 2024-11-04 11:26:03.3499
  5 | 2024-11-04 11:26:57.197616
  6 | 2024-11-04 13:39:03.510724
  7 | 2024-11-04 14:16:04.582568
(7 rows)

# 插入增量資料
mydb=# insert into bak_test values(10,now());
INSERT 0 1
mydb=# insert into bak_test values(11,now());
INSERT 0 1
mydb=# insert into bak_test values(12,now());
INSERT 0 1
mydb=# insert into bak_test values(13,now());
INSERT 0 1

mydb=# select * from bak_test where id >= 10;
 id |            time            
----+----------------------------
 10 | 2024-11-05 11:19:55.079292
 11 | 2024-11-05 11:19:57.647613
 12 | 2024-11-05 11:19:59.919833
 13 | 2024-11-05 11:20:02.551359
(4 rows)

# 執行一次增量備份
~]$ pg_rman backup --backup-mode=incremental --progress --compress-data -U postgres -h /tmp/
--backup-mode=incremental    # 增量備份
--compress-data，-Z              # 壓縮資料

~]$ pg_rman validate

~]$ pg_rman show
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2024-11-05 11:21:57  2024-11-05 11:21:59  INCR    676B     3  OK
2024-11-05 10:19:29  2024-11-05 10:19:31  FULL    20MB     3  OK

使用pg_rman進行恢復

pg_rman的恢復有兩種方法，原地覆蓋和新增$PGDATA,在恢復的時候需要停止postgresql。

# 停止資料庫
systemctl stop postgresql-15

~]$ pg_rman restore --recovery-target-time "2024-11-05 10:19:31" --hard-copy
--recovery-target-time   # 指定恢復的時間點，不指定恢復到最新的時間點
--hard-copy              # 把備份目錄中的歸檔日誌直接複製到歸檔目錄下。

INFO: restore complete
HINT: Recovery will start automatically when the PostgreSQL server is started. After the recovery is done, we recommend to remove recovery-related parameters configured by pg_rman.

# 啟動資料庫，現在的資料是全備時候的資料
mydb=# select * from bak_test;
 id |            time            
----+----------------------------
  1 | 2024-11-01 17:02:34.125168
  2 | 2024-11-04 11:25:06.65344
  3 | 2024-11-04 11:25:23.605243
  4 | 2024-11-04 11:26:03.3499
  5 | 2024-11-04 11:26:57.197616
  6 | 2024-11-04 13:39:03.510724
  7 | 2024-11-04 14:16:04.582568
(7 rows)

# 恢復到增量備份的時間點
systemctl stop postgresql-15
pg_rman restore --recovery-target-time "2024-11-05 11:21:59" --hard-copy
systemctl start postgresql-15

mydb=# select * from bak_test;  -- 10-13 id的資料
 id |            time            
----+----------------------------
  1 | 2024-11-01 17:02:34.125168
  2 | 2024-11-04 11:25:06.65344
  3 | 2024-11-04 11:25:23.605243
  4 | 2024-11-04 11:26:03.3499
  5 | 2024-11-04 11:26:57.197616
  6 | 2024-11-04 13:39:03.510724
  7 | 2024-11-04 14:16:04.582568
 10 | 2024-11-05 11:19:55.079292
 11 | 2024-11-05 11:19:57.647613
 12 | 2024-11-05 11:19:59.919833
 13 | 2024-11-05 11:20:02.551359
(11 rows)

監控診斷與最佳化資料庫

1、使用pgbench進行基準測試

# 生成測試資料，會生成pgbench_開頭的表。
pg_bench -i -s 5

-i  # 初始化
-s  # 100000 的倍數

# 執行一次簡單的基準測試
~]$ pgbench 
pgbench (15.8)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 5
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
number of failed transactions: 0 (0.000%)
latency average = 1.537 ms  # 平均響應時間
initial connection time = 3.787 ms  
tps = 650.787453 (without initial connection time)   # tps


# 設定工作執行緒為2，客戶端為4個，每個客戶端的事務為60個
[postgres@my-pgsql ~]$ pgbench -r -j2 -c4 -t60
pgbench (15.8)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 5
query mode: simple
number of clients: 4
number of threads: 2
maximum number of tries: 1
number of transactions per client: 60
number of transactions actually processed: 240/240
number of failed transactions: 0 (0.000%)
latency average = 6.375 ms
initial connection time = 25.847 ms
tps = 627.436217 (without initial connection time)
statement latencies in milliseconds and failures:
         0.005           0  \set aid random(1, 100000 * :scale)
         0.001           0  \set bid random(1, 1 * :scale)
         0.001           0  \set tid random(1, 10 * :scale)
         0.001           0  \set delta random(-5000, 5000)
         0.296           0  BEGIN;
         0.929           0  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.845           0  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.910           0  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         1.224           0  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.712           0  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         1.206           0  END;
         
# 使用內建指令碼進行測試
~]$ pgbench  -b
pgbench: option requires an argument -- 'b'
pgbench: hint: Try "pgbench --help" for more information.
[postgres@my-pgsql ~]$ pgbench  -b list
Available builtin scripts:
      tpcb-like: <builtin: TPC-B (sort of)>
  simple-update: <builtin: simple update>
    select-only: <builtin: select only>
    
 ~]$ pgbench  -b simple-update:
pgbench: error: no builtin script found for name "simple-update:"
Available builtin scripts:
      tpcb-like: <builtin: TPC-B (sort of)>
  simple-update: <builtin: simple update>
    select-only: <builtin: select only>

[postgres@my-pgsql ~]$ pgbench  -b simple-update
pgbench (15.8)
starting vacuum...end.
transaction type: <builtin: simple update>
scaling factor: 5
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
number of failed transactions: 0 (0.000%)
latency average = 2.164 ms
initial connection time = 5.652 ms
tps = 462.128564 (without initial connection time)

2、使用擴充套件監控和診斷資料庫

pg_top

這個命令就是一個系統的命令列工具，可以展示程序當前執行的sql，select的查詢計劃，程序持有的鎖，程序的IO統計資訊，下游節點的複製資訊。

倉庫地址：https://gitlab.com/pg_top/pg_top

tar -xf pg_top-main.tar.gz

# 安裝依賴,cmake需要》3.1.0
wget https://cmake.org/files/v3.1/cmake-3.1.0-Linux-x86_64.tar.gz

yum -y install libbsd libbsd-devel libmd libmd-devel
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/pg_top CMakeLists.txt
make install

echo 'export PATH=$PATH:/usr/local/pg_top/bin' > /etc/profile.d/pgtop.sh
source /etc/profile.d/pgtop.sh

last pid: 38255;  load avg:  0.00,  0.02,  0.05;       up 4+04:45:21                                                                                                                                     15:14:47
7 processes: 6 other background task(s), 1 active
CPU states:  0.2% user,  0.0% nice,  0.2% system, 99.6% idle,  0.0% iowait
Memory: 2829M used, 961M free, 0K shared, 100K buffers, 2513M cached
DB activity:   0 tps,  0 rollbs/s,   0 buffer r/s, 100 hit%,    100 row r/s,    0 row w/s 
DB I/O:     0 reads/s,     0 KB/s,     0 writes/s,     1 KB/s  
Swap: 0K used, 0K free, 0K cached, 0K in, 0K out

    PID USERNAME    SIZE   RES STATE   XTIME  QTIME  %CPU LOCKS COMMAND
  38256 postgres    273M 7332K active   0:00   0:00   0.4     8 postgres: postgres postgres [local] idle                   
  36906 postgres    271M 2064K          0:00   0:00   0.0     0 postgres: logical replication launcher                     
  36676             270M   23M          0:00   0:00   0.0     0 postgres: checkpointer                                     
  36677             270M 2392K          0:00   0:00   0.0     0 postgres: background writer                                
  36904             271M 2048K          0:00   0:00   0.0     0 postgres: autovacuum launcher                              
  36903             270M 5304K          0:00   0:00   0.0     0 postgres: walwriter                                        
  36905             270M 1276K          0:00   0:00   0.0     0 postgres: archiver last was 000000040000000000000015
  
  
# 常用擴充套件引數
-X     # 檢視每個程序的IO資訊
-R     # 檢視主從複製資訊
-Z     # 只監聽特定的使用者
-o     # 根據輸出欄位進行排序
-x     # 將輸出的資訊輸出到文字中

pg_stat_statements擴充套件與配置

可以用它來監控資料庫中執行的sql語句.比如查詢慢sql。

pg_stat_monitor查詢效能監控

這個外掛可以從效能、應用程式和分析角度等方面提供更全面的查詢檢視來簡化查詢的可觀察性，並將資料分到可配置的時間桶中以提供聚合統計資訊，客戶端資訊，計劃詳細資訊和直方圖資訊。這些時間桶允許pg_stat_monitor擴充套件捕獲較小的負載和效能資訊，因此可以根據時間和工作量來識別資料庫的效能問題。

https://github.com/percona/pg_stat_monitor

以上兩種工具使用方法差不多，pg_stat_monitor支援的功能會更全面。

auto_explain擴充套件監控慢查詢

任何擴充套件的開啟都有效能的負擔，應該根據實際情況考慮。記錄慢查詢語句的執行計劃的功能。

pg_profile生成資料庫效能報告

在發現資料庫有效能問題時，需要分析真個資料庫或者叢集，包括索io、cpu、記憶體等。pg_profile是基於pg資料庫標準統計資訊檢視的診斷工具。

使用pg_profile可以在指定時間內生成資料庫快照，並提供html格式來解釋快照之間的統計資料，從而分析和診斷資料庫的效能問題。

https://github.com/zubkov-andrei/pg_profile

3、使用pgsql的分割槽

分割槽表是解決單表過大的引起的效能問題的一種方式，因為單表如果過大，執行全表掃描的成本就會增加，進而造成慢查詢。在一般情況下，當單表大小超過記憶體大小時就應該考慮使用分割槽。

pgsql支援三種分割槽形式。

範圍分割槽
列表分割槽
雜湊分割槽

範圍分割槽

範圍分割槽是根據一個分割槽鍵或一組分割槽鍵劃分為相應的'範圍',並在不同的範圍時間沒有重疊。分割槽鍵的值是連續的，在表中插入資料時，按照分割槽鍵的匹配關係將資料儲存到哪個分割槽中。例如根據日期範圍把特定的物件的標識劃分範圍分割槽。

-- 建立表
create table sales(
  prod_id int not null,
  cust_id int not null,
  sales_date date not null
) partition by range (sales_date);

-- 在表中建立分割槽
create table sales_y2024m01 partition of sales for values from ('2024-01-01') to ('2024-02--01');
create table sales_y2024m02 partition of sales for values from ('2024-02-01') to ('2024-03--01');
create table sales_y2024m03 partition of sales for values from ('2024-03-01') to ('2024-04--01');

-- 檢視錶結構
mydb=# \d+ sales;
                                      Partitioned table "public.sales"
   Column   |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
------------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 prod_id    | integer |           | not null |         | plain   |             |              | 
 cust_id    | integer |           | not null |         | plain   |             |              | 
 sales_date | date    |           | not null |         | plain   |             |              | 
Partition key: RANGE (sales_date)
Partitions: sales_y2024m01 FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'),
            sales_y2024m02 FOR VALUES FROM ('2024-02-01') TO ('2024-03-01'),
            sales_y2024m03 FOR VALUES FROM ('2024-03-01') TO ('2024-04-01')
            
-- 插入資料
insert into sales values(1,100,'2024-01-12');
insert into sales values(2,200,'2024-02-10');
insert into sales values(3,300,'2024-03-16');

-- 檢視執行計劃

mydb=# explain select * from sales  where sales_date = '2024-03-10';
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Seq Scan on sales_y2024m03 sales  (cost=0.00..35.50 rows=10 width=12)  # 可以看出只掃描了sales_y2024m03 這個分割槽
   Filter: (sales_date = '2024-03-10'::date)
(2 rows)

列表分割槽

列表分割槽是根據特定的值來劃分分割槽，即透過顯示的列出每個分割槽中的鍵值來劃分表，列表分割槽的分割槽鍵值是離散的。

CREATE TABLE students (
    student_id SERIAL,
    name VARCHAR(100) NOT NULL, 
    gender CHAR(1) CHECK (gender IN ('男', '女')),
    grade VARCHAR(10) NOT NULL,
    grade_level CHAR(1) CHECK (grade_level IN ('A', 'B', 'C', 'D')),
    PRIMARY KEY (student_id, grade_level)  -- 新增 grade_level 到主鍵
) PARTITION BY LIST (grade_level);

CREATE TABLE students_grade_level_pA PARTITION OF students FOR VALUES IN ('A');
CREATE TABLE students_grade_level_pB PARTITION OF students FOR VALUES IN ('B');
CREATE TABLE students_grade_level_pC PARTITION OF students FOR VALUES IN ('C');
CREATE TABLE students_grade_level_pD PARTITION OF students FOR VALUES IN ('D');

mydb=# insert into students values(001,'job','男',98,'A');
INSERT 0 1
mydb=# insert into students values(002,'tom','男',96,'A');
INSERT 0 1
mydb=# insert into students values(003,'jerry','男',80,'B');
INSERT 0 1
mydb=# insert into students values(004,'baker','男',85,'B');
INSERT 0 1
mydb=# insert into students values(005,'limeimei','女',57,'D');

# 資料來自D分割槽
mydb=# explain select * from students where grade_level = 'D';
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Seq Scan on students_grade_level_pd students  (cost=0.00..13.25 rows=1 width=276)
   Filter: (grade_level = 'D'::bpchar)
(2 rows)

雜湊分割槽

雜湊分割槽根據分割槽鍵的雜湊值建立分割槽，如果分割槽鍵的雜湊值相同，那麼對應的資料將儲存到同一個分割槽中。

create table  emp_hash_by_job(
id int,
job varchar(20),
deptno varchar(20))
partition by hash(id);

-- 劃分分割槽採用了取模的方式
create table hash_p1 partition of emp_hash_by_job for values with (modulus 4, remainder 0);
create table hash_p2 partition of emp_hash_by_job for values with (modulus 4, remainder 1);
create table hash_p3 partition of emp_hash_by_job for values with (modulus 4, remainder 2);

-- 檢視分割槽
mydb=# \d+  emp_hash_by_job;
                                      Partitioned table "public.emp_hash_by_job"
 Column |         Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id     | integer               |           |          |         | plain    |             |              | 
 job    | character varying(20) |           |          |         | extended |             |              | 
 deptno | character varying(20) |           |          |         | extended |             |              | 
Partition key: HASH (id)
Partitions: hash_p1 FOR VALUES WITH (modulus 4, remainder 0),
            hash_p2 FOR VALUES WITH (modulus 4, remainder 1),
            hash_p3 FOR VALUES WITH (modulus 4, remainder 2)
            
# 插入資料
insert into emp_hash_by_job values(1,'wash',001);
insert into emp_hash_by_job values(2,'cut',002);
insert into emp_hash_by_job values(3,'blow',003);

# 檢視執行計劃
explain select * from emp_hash_by_job where id=1;

# 可以看到資料·來自哪個分割槽
mydb=# explain select * from emp_hash_by_job where id=3;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Seq Scan on hash_p2 emp_hash_by_job  (cost=0.00..16.88 rows=3 width=120)
   Filter: (id = 3)
(2 rows)

pgsql的最佳化

1、最佳化硬體包括cpu、記憶體、磁碟、網路。

2、最佳化作業系統。

2、配置主從。

3、使用分散式叢集。

4、最佳化pgsql的配置引數。

# 最大連線數，生產建議10000
mydb=# show max_connections;

# 資料緩衝區，設定為實體記憶體的25%
mydb=# show shared_buffers;

# 估計可以做磁碟快取的記憶體大小，通常往大了的調，可以提高效能。
mydb=# show effective_cache_size;

# 維護任務的記憶體，增加這個對修改表結構，回收髒資料，建立索引有明顯效能提升
mydb=# show maintenance_work_mem;

# 指定檢查點的速度。
# checkpoint_completion_target的引數值越大表示刷盤的頻率久越小刷資料盤的io就越平滑，可以提升io效能。
mydb=# show checkpoint_completion_target; 0.9
mydb=# show checkpoint_timeout;   5

# 列的預設統計目標，增加該數值會增加sql語句分析的時間
mydb=# show default_statistics_target;

# 讓資料庫更傾向索引掃描還是索引掃描 
mydb=# show random_page_cost;
減少該數值會更傾向索引掃描。

# 磁碟IO併發數，磁碟併發操作的條目數，增加可以提高io效能。
mydb=# show effective_io_concurrency;

# 在寫入檔案之前每個操作可使用的最大記憶體容量
mydb=# show work_mem;

# 預寫日誌緩衝區，指定預寫日誌的緩衝區。預寫日誌共享記憶體量。不建議設定得太大有助於在繁忙的資料庫上提高資料寫的效能。
mydb=# show wal_buffers;

# 預寫日誌的最小維護尺寸
mydb=# show min_wal_size;

# 預寫日誌最大維護尺寸
mydb=# show max_wal_size;

# 後臺最大程序數，只能在服務啟動的時候設定。
mydb=# show max_worker_processes;

# 複製槽的限制
max_slot_wal_keep_size;

pgsql的效能檢視

mydb=# select relname from pg_class where relname like 'pg_stat_%';

pg_stat_database        資料庫叢集內所有庫資訊
pg_stat_user_tables     記錄使用者自己建立的表資訊
pg_stat_user_indexes    使用者自己建立的索引
pg_statio_user_indexes  記錄使用者表的io 資訊
pg_stat_bgwriter        後臺程序寫資料情況

PG資料庫的高可用架構

記錄一個配置檔案

postgres.conf

# 監聽所有IP
listen_addresses = "0.0.0.0"

# 最大連線數，從pgsql需要大於或等於主的值
max_connections = 300

restore_command = 'cp /data/postgresql-12/archive/%f %p'
# archive_cleanup_command = '/usr/local/postgresql-12/bin/pg_archivecleanup -d /data/postgresql-12/data/pg_wal %r && /usr/local/postgresql-12/bin/pg_archivecleanup -d /data/postgresql-12/archive %r >> /data/postgresql-12/log/archive_cleanup.log 2>&1'

# 9.6開始沒有hot_standby（熱備模式）
wal_level = replica
# 最多有16個流複製連線。
max_wal_senders = 16

# 設定比主庫大，可以設定為2倍的數值
wal_keep_segments = 512
max_logical_replication_workers = 10

autovacuum_max_workers = 2
# 和主的值保持一致即可
max_worker_processes = 16

# 說明這臺機器不僅用於資料歸檔，還可以用於資料查詢
hot_standby = on
#流備份的最大延遲時間
max_standby_streaming_delay = 30s 
# 向主機彙報本機狀態的間隔時間
wal_receiver_status_interval = 10s 
# 出現錯誤複製，向主機反饋
hot_standby_feedback = on


# 日誌設定
log_destination = 'stderr'
logging_collector = on
log_directory = '/data/postgresql-12/log'
log_filename = 'postgresql-%w.log'
log_file_mode = 0600
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 1GB

log_min_messages = error
# 執行超過300ms的sql語句會被記錄到pgsql的日誌檔案中
log_min_duration_statement = 300
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose
log_hostname = on
log_line_prefix = '%m [%p] '
log_lock_waits = on 
log_statement = 'ddl'

pg資料庫主從

複製槽

standby端如果長時間停機，重啟後standby可能因缺少相應的WAL日誌無法連線primary。此時可以透過啟用max_replication_slots引數啟用複製槽來解決此問題。

primary端例項會一直保留預寫日誌（WAL）檔案，直到所有備庫所需的插槽都確認已接收到特定段為止。只有完成此操作後，主庫例項才會移除相應的WAL檔案。

主庫端複製槽要和從庫數量對應，我這裡有2個從庫，建立2個複製槽：

cndba=# select * from pg_create_physical_replication_slot('pgsql_db2');
-[ RECORD 1 ]--------
slot_name | pgsql_db2
lsn       |

cndba=# select * from pg_create_physical_replication_slot('pgsql_db3');
-[ RECORD 1 ]--------
slot_name | pgsql_db3
lsn       |


檢視複製槽：
postgres=# select * from pg_replication_slots;

主機名	IP
master	192.168.21.92
slave	192.168.21.178

# 設定hosts
~]# cat /etc/hosts

192.168.21.92 master
192.168.21.178 slave

主庫配置

# 建立流複製使用者
postgres=#  create user replicator replication password '126.comM';

# 修改基礎認證配置檔案
~]$ vi /data/postgres/data/pg_hba.conf
host    replication     replicator      192.168.21.178/32       scram-sha-256

systemctl restart postgresql-15

# 主庫的傳送程序
ps -ef | grep sender

# 查詢主從同步狀態
select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;

從庫配置

# 在從庫上備份主庫的資料
pg_basebackup -h master -D ./data/ -U replicator -P -v -R -X stream


-C -S slot1    # 建立一個槽位，槽位的名字叫做slot1

# 備份完成後會在備份目錄下生成
standby.signal          # 表示一個從庫
postgresql.auto.conf    # 記錄有主庫的連線資訊

master-bak]$ cat postgresql.auto.conf 
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=postgres passfile=''/home/postgres/.pgpass'' channel_binding=disable port=5432 sslmode=disable sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
primary_conninfo = 'user=replicator password=126.comM channel_binding=disable host=master port=5432 sslmode=disable sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
primary_slot_name = 'slot1'

# 把備份的資料放到$PGDATA目錄下
[postgres@slave postgres]$ ll $PGDATA/
total 252
-rw------- 1 postgres postgres    227 Nov  6 16:32 backup_label
-rw-rw-r-- 1 postgres postgres    234 Nov  6 16:32 backup_label.old
-rw------- 1 postgres postgres 185196 Nov  6 16:32 backup_manifest
drwx------ 6 postgres postgres     46 Nov  6 16:32 base
drwxr-xr-x 2 postgres postgres      6 Nov  6 16:32 bin
drwx------ 2 postgres postgres   4096 Nov  6 16:32 global
drwx------ 2 postgres postgres      6 Nov  6 16:32 pg_commit_ts
drwx------ 2 postgres postgres      6 Nov  6 16:32 pg_dynshmem
-rw------- 1 postgres postgres   4805 Nov  6 16:32 pg_hba.conf
-rw------- 1 postgres postgres   1636 Nov  6 16:32 pg_ident.conf
drwx------ 4 postgres postgres     68 Nov  6 16:32 pg_logical
drwx------ 4 postgres postgres     36 Nov  6 16:32 pg_multixact
drwx------ 2 postgres postgres      6 Nov  6 16:32 pg_notify
drwx------ 2 postgres postgres      6 Nov  6 16:32 pg_replslot
-rw-rw-r-- 1 postgres postgres    155 Nov  6 16:32 pg_rman_recovery.conf
drwx------ 2 postgres postgres      6 Nov  6 16:32 pg_serial
drwx------ 2 postgres postgres      6 Nov  6 16:32 pg_snapshots
drwx------ 2 postgres postgres      6 Nov  6 16:32 pg_stat
drwx------ 2 postgres postgres      6 Nov  6 16:32 pg_stat_tmp
drwx------ 2 postgres postgres      6 Nov  6 16:32 pg_subtrans
drwx------ 2 postgres postgres      6 Nov  6 16:32 pg_tblspc
drwx------ 2 postgres postgres      6 Nov  6 16:32 pg_twophase
-rw------- 1 postgres postgres      3 Nov  6 16:32 PG_VERSION
drwx------ 3 postgres postgres     84 Nov  6 16:32 pg_wal
drwx------ 2 postgres postgres     18 Nov  6 16:32 pg_xact
-rw------- 1 postgres postgres    600 Nov  6 16:32 postgresql.auto.conf
-rw-rw-r-- 1 postgres postgres  29588 Nov  6 16:32 postgresql.conf
drwxr-xr-x 3 postgres postgres     17 Nov  6 16:32 share
-rw------- 1 postgres postgres      0 Nov  6 16:32 standby.signal
-rw------- 1 postgres postgres      0 Nov  6 16:32 tablespace_map.old


# 啟動從庫
systemctl start postgresql-15

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t
(1 row)
檢視是否處於recovery狀態，值為“t”說明是當前資料庫是備庫，值為“f”說明當前資料庫是主庫（也可能複製流出現異常出現雙主

# 檢視wal 日誌接收狀態
mydb=# select * from pg_stat_wal_receiver;

# 從庫的接收程序
ps -ef | grep recei

# 檢視資料庫狀態
主庫：
-bash-4.2$ pg_controldata | grep "Database cluster state:"
Database cluster state:               in production 

從庫：
-bash-4.2$  pg_controldata | grep "Database cluster state:"
Database cluster state:               in archive recovery
-bash-4.2$

手動完成從庫到主庫的切換

# 1、停止主庫
[root@master data]# systemctl stop postgresql-15

# 2、啟動從庫可寫
[postgres@slave data]$ pg_ctl promote
waiting for server to promote.... done
server promoted

-- 從庫開啟寫後接收日誌流的程序沒有了。
# 提升備庫為主庫之後，後臺程序中不再有startup recovering程序了，同時，多了postgres: walwriter 寫程序。
# 把掛掉的主庫作為從庫加入到新的主庫中
# 幾個注意點：
hba配置檔案
host    replication     replicator      192.168.21.0/24       scram-sha-256

# 建立檔案standby.signal
#1、在新的從庫操作
touch standby.signal
data]# cat postgresql.auto.conf   # 在最後新增
primary_conninfo = 'user=replicator password=126.comM host=slave port=5432'

# 啟動新的從庫
systemctl start postgresql-15
[postgres@master postgres]$ ps -ef | grep postgres
root        6458    6438  0 15:03 pts/0    00:00:00 su - postgres
postgres    6459    6458  0 15:03 pts/0    00:00:00 -bash
postgres    7695       1  0 16:06 ?        00:00:00 /opt/postgresql/bin/postgres -D /data/postgres/data
postgres    7696    7695  0 16:06 ?        00:00:00 postgres: checkpointer 
postgres    7697    7695  0 16:06 ?        00:00:00 postgres: background writer 
postgres    7698    7695  0 16:06 ?        00:00:00 postgres: startup recovering 00000007000000000000002F
postgres    7700    7695  0 16:06 ?        00:00:00 postgres: walreceiver streaming 0/2F0005B8
postgres    7715    6459  0 16:15 pts/0    00:00:00 ps -ef
postgres    7716    6459  0 16:15 pts/0    00:00:00 grep --color=auto postgres


[postgres@master postgres]$ pg_controldata | grep state
Database cluster state:               in archive recovery  # 從庫只讀狀態

讀書筆記...
2007-02-14
筆記
讀書筆記
2024-06-04
筆記
《讀書與做人》讀書筆記
2024-06-14
筆記
Cucumber讀書筆記
2020-04-06
筆記
散文讀書筆記
2018-08-26
筆記
HTTP 讀書筆記
2018-03-05
HTTP筆記
CoreJava讀書筆記-------
2017-10-23
Java筆記
flask讀書筆記
2017-04-03
Flask筆記
Vue讀書筆記
2018-05-02
Vue筆記
MONGODB 讀書筆記
2014-01-26
MongoDB筆記
Qt讀書筆記
2013-06-16
QT筆記
Node讀書筆記
2016-02-03
筆記
SAP讀書筆記
2015-09-22
筆記
YII讀書筆記
2014-09-11
筆記
iptables 讀書筆記
2012-11-23
筆記
Makefile 讀書筆記
2011-03-16
筆記
mysql讀書筆記
2016-02-23
MySql筆記
鎖讀書筆記
2008-01-17
筆記
dataguard讀書筆記
2007-12-23
筆記
讀書筆記3
2024-06-19
筆記
讀書筆記2
2024-10-30
筆記
《重構》讀書筆記
2021-03-06
筆記
webpackDemo讀書筆記
2018-07-30
Web筆記
PMBook讀書筆記（一）
2020-12-07
筆記
Effective Java 讀書筆記
2016-11-23
Java筆記
js高程讀書筆記
2018-04-09
JS筆記
“Docker Practice”讀書筆記
2017-06-18
Docker筆記
FPGA讀書筆記5
2016-07-26
FPGA筆記
FPGA讀書筆記3
2016-07-20
FPGA筆記
FPGA讀書筆記4
2016-07-20
FPGA筆記
FPGA讀書筆記2
2016-07-19
FPGA筆記
FPGA讀書筆記1
2016-07-18
FPGA筆記
《精通JavaScript》讀書筆記
2016-07-12
JavaScript筆記
讀書筆記摘錄：
2014-10-16
筆記
《矽谷之父》讀書筆記
2009-02-19
筆記
重構讀書筆記
2010-01-27
筆記
讀書筆記之一
2016-03-24
筆記
Spam Or Ham讀書筆記
2016-01-24
筆記

postgres 讀書筆記

postgres 讀書筆記

第1章

預寫日誌

日誌記錄配置

1.5 歸檔日誌

自動清理程序autovacuum

記憶體結構

postgres 的兩種分散式叢集實現

Postgres物件管理

1.建立一個schema

2.表的基本操作

3.表的約束

4.表的碎片

5.pgsql的統計資訊

6.使用analyze命令手動收集資料庫統計資訊

7.收集器配置項

8.臨時表

9.索引

10.清理索引索引

11.檢視

並行查詢

1.並行掃描

2.檢視錶鎖

3.死鎖

使用者管理

1.許可權管理

2.使用grant和revoke管理使用者許可權

3.admin option和grant option

4.分組的許可權管理

5.使用set role命令顯示啟用角色的許可權

6.審計日誌

備份與恢復

1.設定歸檔日誌

2.管理過期的歸檔日誌

3.邏輯備份

4.檔案級別備份

5.使用pg_basebackup完成熱備與恢復

5.連續歸檔基於時間點的恢復

基於時間點的恢復

恢復到指定的恢復點

恢復到指定的事務

恢復到最近時間點

第三方備份工具pg_rman

執行一次全備

進行一次增量備份

使用pg_rman進行恢復

監控診斷與最佳化資料庫

1、使用pgbench進行基準測試

2、使用擴充套件監控和診斷資料庫

pg_top

pg_stat_statements擴充套件與配置

pg_stat_monitor查詢效能監控

auto_explain擴充套件監控慢查詢

pg_profile生成資料庫效能報告

3、使用pgsql的分割槽

範圍分割槽

列表分割槽

雜湊分割槽

pgsql的最佳化

pgsql的效能檢視

PG資料庫的高可用架構

pg資料庫主從

複製槽

主庫配置

從庫配置

手動完成從庫到主庫的切換

相關文章