大數據就業實戰培訓 Oracle就業實戰培訓
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數據庫管理 的內容，歡迎進群一起探討交流QQ交流群：752027153
微信交流群：聯系客服拉你進微信PG交流群
釘釘交流群：35822460，釘釘群有專門講解公開課
往期課程，聯系cuug咨詢老師
