PG技術大講堂 - Part 10：PostgreSQL資料庫管理

PostgreSQL從小白到專家，是從入門逐漸能力提升的一個系列教程，內容包括對PG基礎的認知、包括安裝使用、包括角色許可權、包括維護管理、、等內容，希望對熱愛PG、學習PG的同學們有幫助，歡迎持續關注CUUG PG技術大講堂。

Part 10：PostgreSQL資料庫管理

內容1：PostgreSQL資料庫結構

內容2：PostgreSQL資料庫級許可權管理

內容3：PG資料庫級環境引數設定

內容4：PostgreSQL資料庫級屬性修改

10.1、資料庫結構

資料庫集簇邏輯結構每個資料庫儲存的物件(表、索引、檢視等等)是獨立的、私有的，每個資料庫類似於每個房間，從房間中取東西，就需要到房間裡面;同理，要訪問某個資料庫中的物件，就需要登入到指定的資料庫中。

PostgreSQL資料庫結構

資料庫叢集是由PostgreSQL伺服器管理的資料庫的集合。PostgreSQL中的“資料庫叢集”一詞並不意味著“一組資料庫伺服器”。PostgreSQL伺服器在單個主機上執行，並管理單個資料庫群集。 

 資料庫是資料庫物件的集合。在關聯式資料庫理論中，資料庫物件是用來儲存或引用資料的資料結構。堆(heap)表是一個典型的例子，它有很多類似於索引、序列、檢視、函式等等。在PostgreSQL中，資料庫本身也是資料庫物件，在邏輯上彼此分離。所有其他資料庫物件(如表、索引等)都屬於各自的資料庫。

PostgreSQL資料庫屬主

· Postgres中的資料庫屬主屬於建立者，只要有createdb的許可權就可以建立資料庫，資料庫屬主不一定擁有存放在該資料庫中其它使用者建立的物件的訪問許可權。

· 資料庫在建立後，允許public角色連線，即允許任何人連線。

· 資料庫在建立後，不允許除了超級使用者和owner之外的任何人在資料庫中建立schema。

· 資料庫在建立後，會自動建立名為public的schema，這個schema的all許可權已經賦予給了public角色，即允許任何人在裡面建立物件，但對己存在的其它使用者的表不具有任何許可權。

10.2、資料庫許可權

CREATE：可以在指定資料庫建立schema的許可權

CONNECT：可以連線到指定資料庫的許可權TEMPORARY：可以建立臨時表的許可權ALL：指定資料庫所有的許可權語法：

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

ON DATABASE 資料庫名稱 [, ...] 

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

由於資料庫在建立後，允許public角色連線，即允許任何人連線。所以如果要取消某個使用者對指定資料庫連線的許可權，需要先取消public的連線許可權，再取消該使用者的連線許可權。

--授權使用者連線資料庫的許可權

grant connect on database db_name to user_name;

--撤銷使用者連線資料庫的許可權

revoke connect on database db_name from public;

revoke connect on database db_name from user_name;

--檢視哪些使用者有某個資料庫的connect許可權

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

10.3、資料庫環境設定

PostgreSQL引數設定分為例項級、資料庫級、使用者級和會話級，而有些引數可以在所有級別中設定，優先順序順序為會話級>使用者級>資料庫級>例項級。

資料庫引數配置語法：

ALTER DATABASE 名稱 SET 配置引數 { TO | = } { 值 | DEFAULT }

ALTER DATABASE 名稱 SET 配置引數 FROM CURRENT

ALTER DATABASE 名稱 RESET 配置引數

ALTER DATABASE 名稱 RESET ALL

配置示例(一)：

--設定資料庫搜尋路徑：

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

--配置連線某個庫時可使用的工作記憶體

alter database postgres set work_mem = '8MB'; 

 --配置連線某個庫時可使用的維護記憶體

alter database postgres set maintenance_work_mem TO '256MB';

配置示例(二)：

--配置連線某個庫後使用的時區

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

--配置連線某個庫後執行語句最多時長(執行1秒超時)

alter database postgres set statement_timeout =1000;

--配置連線某個庫後預設的客戶端編碼，配置客戶端編碼為gbk，適用於資料庫編碼為utf8，應用程式編碼為gbk的應用

alter database postgres set client_encoding to gbk;

配置示例(三)：

--配置某個庫使用日誌記錄級別(設定後，對這個資料庫的訪問不記錄日誌)

alter database postgres set log_statement=none;

--配置連線某個庫後的wal日誌寫盤級別(設定後，該庫的更新操作只要求本地提交)

alter database postgres set synchronous_commit to local;

--配置連線某個庫後禁用某個規劃器(禁用indexonlyscan掃描)

alter database postgres set enable_indexonlyscan to off;

配置示例(四)：

--配置連線某個庫後執行出錯時中斷連線(對新會話生效)

alter database postgres set exit_on_error to on;

--重新連線後

select pg_backend_pid();

--執行錯誤會導致連線中斷

select * from d1;

配置示例(五)：

--檢視所有個性化配置

\drds

--查詢資料庫的連線數限制只能檢視資料字典表

select datname,datconnlimit from pg_database

--設定某個個性化設定為預設值

ALTER DATABASE postgres reset exit_on_error;

--設定所有個性化設定為預設值

ALTER DATABASE postgres reset ALL;

10.4、資料庫屬性修改

資料庫的屬性我們可以進行修改，修改範圍是資料庫名字、屬主、表空間。

ALTER DATABASE 名稱 RENAME TO 新的名稱

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

ALTER DATABASE 名稱 SET TABLESPACE 新的表空間

示例：

--修改資料庫名字

ALTER DATABASE newdb2 RENAME TO newdb3;

--修改資料庫屬主

ALTER DATABASE newdb3 OWNER TO u1;

--修改新表空間的名字

ALTER DATABASE newdb3 SET TABLESPACE new_tbl;

以上就是Part 10 - PostgreSQL資料庫管理 的內容，歡迎進群一起探討交流，釘釘交流群：35，82，24，60，釘釘群有專門講解公開課

往期課程，聯絡cuug諮詢老師