PostgreSQL從小白到專家，是從入門逐漸能力提升的一個系列教程，內(nèi)容包括對PG基礎(chǔ)的認(rèn)知、包括安裝使用、包括角色權(quán)限、包括維護(hù)管理、、等內(nèi)容，希望對熱愛PG、學(xué)習(xí)PG的同學(xué)們有幫助，歡迎持續(xù)關(guān)注CUUG PG技術(shù)大講堂。

Part 10：PostgreSQL數(shù)據(jù)庫管理

內(nèi)容1：PostgreSQL數(shù)據(jù)庫結(jié)構(gòu)

內(nèi)容2：PostgreSQL數(shù)據(jù)庫級權(quán)限管理

內(nèi)容3：PG數(shù)據(jù)庫級環(huán)境參數(shù)設(shè)置

內(nèi)容4：PostgreSQL數(shù)據(jù)庫級屬性修改

10.1、數(shù)據(jù)庫結(jié)構(gòu)

數(shù)據(jù)庫集簇邏輯結(jié)構(gòu)每個數(shù)據(jù)庫存儲的對象(表、索引、視圖等等)是獨(dú)立的、私有的，每個數(shù)據(jù)庫類似于每個房間，從房間中取東西，就需要到房間里面;同理，要訪問某個數(shù)據(jù)庫中的對象，就需要登錄到指定的數(shù)據(jù)庫中。

PostgreSQL數(shù)據(jù)庫結(jié)構(gòu)

數(shù)據(jù)庫集群是由PostgreSQL服務(wù)器管理的數(shù)據(jù)庫的集合。PostgreSQL中的“數(shù)據(jù)庫集群”一詞并不意味著“一組數(shù)據(jù)庫服務(wù)器”。PostgreSQL服務(wù)器在單個主機(jī)上運(yùn)行，并管理單個數(shù)據(jù)庫群集。 

 數(shù)據(jù)庫是數(shù)據(jù)庫對象的集合。在關(guān)系數(shù)據(jù)庫理論中，數(shù)據(jù)庫對象是用來存儲或引用數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。堆(heap)表是一個典型的例子，它有很多類似于索引、序列、視圖、函數(shù)等等。在PostgreSQL中，數(shù)據(jù)庫本身也是數(shù)據(jù)庫對象，在邏輯上彼此分離。所有其他數(shù)據(jù)庫對象(如表、索引等)都屬于各自的數(shù)據(jù)庫。

PostgreSQL數(shù)據(jù)庫屬主

· Postgres中的數(shù)據(jù)庫屬主屬于創(chuàng)建者，只要有createdb的權(quán)限就可以創(chuàng)建數(shù)據(jù)庫，數(shù)據(jù)庫屬主不一定擁有存放在該數(shù)據(jù)庫中其它用戶創(chuàng)建的對象的訪問權(quán)限。

· 數(shù)據(jù)庫在創(chuàng)建后，允許public角色連接，即允許任何人連接。

· 數(shù)據(jù)庫在創(chuàng)建后，不允許除了超級用戶和owner之外的任何人在數(shù)據(jù)庫中創(chuàng)建schema。

· 數(shù)據(jù)庫在創(chuàng)建后，會自動創(chuàng)建名為public的schema，這個schema的all權(quán)限已經(jīng)賦予給了public角色，即允許任何人在里面創(chuàng)建對象，但對己存在的其它用戶的表不具有任何權(quán)限。

10.2、數(shù)據(jù)庫權(quán)限

CREATE：可以在指定數(shù)據(jù)庫創(chuàng)建schema的權(quán)限

CONNECT：可以連接到指定數(shù)據(jù)庫的權(quán)限TEMPORARY：可以創(chuàng)建臨時表的權(quán)限ALL：指定數(shù)據(jù)庫所有的權(quán)限語法：

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] } 

ON DATABASE 數(shù)據(jù)庫名稱 [, ...] 

TO role_specification [, ...] [ WITH GRANT OPTION ]

由于數(shù)據(jù)庫在創(chuàng)建后，允許public角色連接，即允許任何人連接。所以如果要取消某個用戶對指定數(shù)據(jù)庫連接的權(quán)限，需要先取消public的連接權(quán)限，再取消該用戶的連接權(quán)限。

--授權(quán)用戶連接數(shù)據(jù)庫的權(quán)限

grant connect on database db_name to user_name;

--撤銷用戶連接數(shù)據(jù)庫的權(quán)限

revoke connect on database db_name from public;

revoke connect on database db_name from user_name;

--查看哪些用戶有某個數(shù)據(jù)庫的connect權(quán)限

select datname,datacl from pg_database where datname='db_name';

10.3、數(shù)據(jù)庫環(huán)境設(shè)置

PostgreSQL參數(shù)設(shè)置分為實(shí)例級、數(shù)據(jù)庫級、用戶級和會話級，而有些參數(shù)可以在所有級別中設(shè)置，優(yōu)先級順序?yàn)闀�話�?gt;用戶級>數(shù)據(jù)庫級>實(shí)例級。

數(shù)據(jù)庫參數(shù)配置語法：

ALTER DATABASE 名稱 SET 配置參數(shù) { TO | = } { 值 | DEFAULT }

ALTER DATABASE 名稱 SET 配置參數(shù) FROM CURRENT

ALTER DATABASE 名稱 RESET 配置參數(shù)

ALTER DATABASE 名稱 RESET ALL

配置示例(一)：

--設(shè)置數(shù)據(jù)庫搜索路徑：

alter database postgres set search_path to "$user", public, schema_name;

--配置連接某個庫時可使用的工作內(nèi)存

alter database postgres set work_mem = '8MB'; 

 --配置連接某個庫時可使用的維護(hù)內(nèi)存

alter database postgres set maintenance_work_mem TO '256MB';

配置示例(二)：

--配置連接某個庫后使用的時區(qū)

alter database postgres set TimeZone to cet;alter database postgres set DateStyle to SQL, DMY;(重新登錄生效)

--配置連接某個庫后執(zhí)行語句最多時長(執(zhí)行1秒超時)

alter database postgres set statement_timeout =1000;

--配置連接某個庫后默認(rèn)的客戶端編碼，配置客戶端編碼為gbk，適用于數(shù)據(jù)庫編碼為utf8，應(yīng)用程序編碼為gbk的應(yīng)用

alter database postgres set client_encoding to gbk;

配置示例(三)：

--配置某個庫使用日志記錄級別(設(shè)置后，對這個數(shù)據(jù)庫的訪問不記錄日志)

alter database postgres set log_statement=none;

--配置連接某個庫后的wal日志寫盤級別(設(shè)置后，該庫的更新操作只要求本地提交)

alter database postgres set synchronous_commit to local;

--配置連接某個庫后禁用某個規(guī)劃器(禁用indexonlyscan掃描)

alter database postgres set enable_indexonlyscan to off;

配置示例(四)：

--配置連接某個庫后執(zhí)行出錯時中斷連接(對新會話生效)

alter database postgres set exit_on_error to on;

--重新連接后

select pg_backend_pid();

--執(zhí)行錯誤會導(dǎo)致連接中斷

select * from d1;

配置示例(五)：

--查看所有個性化配置

\drds

--查詢數(shù)據(jù)庫的連接數(shù)限制只能查看數(shù)據(jù)字典表

select datname,datconnlimit from pg_database

--設(shè)置某個個性化設(shè)置為默認(rèn)值

ALTER DATABASE postgres reset exit_on_error;

--設(shè)置所有個性化設(shè)置為默認(rèn)值

ALTER DATABASE postgres reset ALL;

10.4、數(shù)據(jù)庫屬性修改

數(shù)據(jù)庫的屬性我們可以進(jìn)行修改，修改范圍是數(shù)據(jù)庫名字、屬主、表空間。

ALTER DATABASE 名稱 RENAME TO 新的名稱

ALTER DATABASE 名稱 OWNER TO { 新的屬主 | CURRENT_USER | SESSION_USER }

ALTER DATABASE 名稱 SET TABLESPACE 新的表空間

示例：

--修改數(shù)據(jù)庫名字

ALTER DATABASE newdb2 RENAME TO newdb3;

--修改數(shù)據(jù)庫屬主

ALTER DATABASE newdb3 OWNER TO u1;

--修改新表空間的名字

ALTER DATABASE newdb3 SET TABLESPACE new_tbl;

以上就是Part 10 - PostgreSQL數(shù)據(jù)庫管理 的內(nèi)容，歡迎進(jìn)群一起探討交流QQ交流群：752027153

微信交流群：聯(lián)系客服拉你進(jìn)微信PG交流群

釘釘交流群：35822460，釘釘群有專門講解公開課

往期課程，聯(lián)系cuug咨詢老師