pgsql系統配置參數說明 · PHP知識總結

<div class="liststyle"> [TOC] </div> > Postgresql運行的系統參數一般是保存在postgresql.conf配置文件中，該配置文件為數據庫集簇提供了默認值。除非這些參數被覆蓋，否則數據庫當前活動會話使用的就是這些參數。 # **查看配置文件位置** 1. 可以通過`show`命令查看配置文件位置 ``` postgres=# show config_file; config_file ------------------------------------------- /www/server/data/postgres/postgresql.conf (1 row) postgres=# show hba_file; hba_file --------------------------------------- /www/server/data/postgres/pg_hba.conf (1 row) postgres=# show ident_file; ident_file ----------------------------------------- /www/server/data/postgres/pg_ident.conf (1 row) ``` 2. 可以通過 pg_settings表查看配置文件位置 ``` postgres=# select name,setting from pg_settings where category= 'File Locations'; name | setting -------------------+------------------------------------------- config_file | /www/server/data/postgres/postgresql.conf data_directory | /www/server/data/postgres external_pid_file | hba_file | /www/server/data/postgres/pg_hba.conf ident_file | /www/server/data/postgres/pg_ident.conf (5 rows) ``` # **查看運行時的參數值：`show`、`pg_settings`、`current_setting`** 1. 通過`show`命令查看 ``` postgres=# show work_mem; work_mem ---------- 4MB (1 row) ``` 2. 通過`pg_setting`視圖查看 ``` postgres=# select name,setting from pg_settings where name in ('work_mem'); name | setting ----------+--------- work_mem | 4096 (1 row) ``` 3. 通過`current_setting`內置函數查看 ``` postgres=# select current_setting('work_mem'); current_setting ----------------- 4MB (1 row) ``` # **通過配置文件修改參數**         `postgresql.conf`中有些參數修改后，需要重啟服務才能生效：`pg_ctl restart`，比如shared_buffers，定義了共享內存的大小。也有許多參數在pg服務運行的時候就能修改，再更改之后像服務器執行一個reload操作，強制pg重新讀取`postgresql.conf`，因此只需要執行 `pg_ctl reload `即可。         除了直接編輯`postgresql.conf`文件來修改配置之外，還可以通過使用 `alter system `命令修改參數，使用`alter system`命令將修改`postgresql.auto.conf`文件，每當`postgresql.conf`被讀取時這個文件會被自動讀取，并且它的設置會以同樣的方式生效。`postgresql.auto.conf`中的設置會覆蓋`postgresql.conf`中的設置。         用`alter system`設置的值將在下一次重載服務器配置后生效，那些只能在服務器啟動時更改的參數則會在下一次服務器重啟后生效。 ``` postgres=# \h alter system Command: ALTER SYSTEM Description: change a server configuration parameter Syntax: ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT } ALTER SYSTEM RESET configuration_parameter ALTER SYSTEM RESET ALL URL: https://www.postgresql.org/docs/13/sql-altersystem.html // 設置 wal_level postgres=# ALTER SYSTEM SET wal_level = replica; // 撤銷設置 postgres=# ALTER SYSTEM RESET wal_level; ``` # **通過SQL修改參數：`alter system`、`alter database`、`alter role`** 1. `alter system`命令提供了一種改變全局默認值的從SQL可訪問的方法；它在功效上等效于編輯`postgresql.conf` 2. 為特定的數據庫里的所有的用戶設置參數：`alter database` ``` postgres=# \h alter database Command: ALTER DATABASE Description: change a database Syntax: ALTER DATABASE name [ [ WITH ] option [ ... ] ] where option can be: ALLOW_CONNECTIONS allowconn CONNECTION LIMIT connlimit IS_TEMPLATE istemplate ALTER DATABASE name RENAME TO new_name ALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER DATABASE name SET TABLESPACE new_tablespace ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT } ALTER DATABASE name SET configuration_parameter FROM CURRENT ALTER DATABASE name RESET configuration_parameter ALTER DATABASE name RESET ALL URL: https://www.postgresql.org/docs/13/sql-alterdatabase.html // 在數據庫test中默認禁用索引掃描 postgres=# ALTER DATABASE test SET enable_indexscan TO off; // 為test數據庫所有的連接設置work_mem為16MB test=# alter database test set work_mem='16MB'; ALTER DATABASE ``` 3. 為數據庫中的某個特定用戶設置參數：`alter role` ``` postgres=# \h alter role Command: ALTER ROLE Description: change a database role Syntax: ALTER ROLE role_specification [ WITH ] option [ ... ] where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | INHERIT | NOINHERIT | LOGIN | NOLOGIN | REPLICATION | NOREPLICATION | BYPASSRLS | NOBYPASSRLS | CONNECTION LIMIT connlimit | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL | VALID UNTIL 'timestamp' ALTER ROLE name RENAME TO new_name ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT } ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL where role_specification can be: role_name | CURRENT_USER | SESSION_USER URL: https://www.postgresql.org/docs/13/sql-alterrole.html // 為brent用戶,設置work_mem為2MB postgres=# alter role brent set work_mem='2MB'; ALTER ROLE ```         只有當開始一個新的數據庫會話時，用`ALTER DATABASE`和`ALTER ROLE`設置的值才會被應用。它們會覆蓋從配置文件或服務器命令行獲得的值，并且作為該會話后續的默認值。注意某些設置在服務器啟動后不能被更改，并且因此不能被這些命令設置。         如果同時為數據庫和用戶設置了特定參數，那么以用戶為準；如果同時為數據庫和用戶設置了特定參數，以及為某個特定用戶連接到特定的數據庫設置參數，那么以為某個特定用戶連接到特定的數據庫設置的參數為準、 ``` // 為用戶postgres在數據庫test中設置work_mem為8MB test=# alter role postgres in database test set work_mem='8MB'; ALTER ROLE ``` # **修改當前會話的參數：`set`**         PostgreSQL會提供兩個額外的SQL命令（以及等效的函數）用以影響會話本地的配置設置： 1. `show`命令允許察看所有參數的當前值。對應的函數是`current_setting(setting_name text)`。 2. `set`命令允許修改對于一個會話可以本地設置的參數的當前值，它對其他會話沒有影響。對應的函數是`set_config(setting_name, new_value, is_local)` ``` postgres=# show work_mem; work_mem ---------- 4MB (1 row) postgres=# set work_mem='7MB'; SET postgres=# show work_mem; work_mem ---------- 7MB (1 row) // 新打開一個會話，查詢work_mem，依然顯示4MB ``` 3. set命令后添加 local關鍵字，只在當前事務中修改，只在當前事務內有效 ``` postgres=# begin; BEGIN postgres=*# show work_mem; work_mem ---------- 7MB (1 row) postgres=*# set local work_mem='4MB'; SET postgres=*# show work_mem; work_mem ---------- 4MB (1 row) postgres=*# commit; COMMIT postgres=# show work_mem; work_mem ---------- 7MB (1 row) ``` # **通過Shell修改參數**         除了在數據庫或者角色層面上設置全局默認值或者進行覆蓋，你還可以通過 shell 工具把設置傳遞給PostgreSQL。服務器和libpq客戶端庫都能通過 shell 接受參數值 1. 在服務器啟動期間，可以通過`-c`命令行參數把參數設置傳遞給`postgres`命令，這種方式提供的設置會覆蓋通過`postgresql.conf`或者`ALTER SYSTEM`提供的設置，因此除了重啟服務器之外無法從全局上改變它們 ``` postgres -c log_connections=yes -c log_destination='syslog' ``` 2. 當通過libpq啟動一個客戶端會話時，可以使用`PGOPTIONS`環境變量指定參數設置。這種方式建立的設置構成了會話生存期間的默認值，但是不會影響其他的會話。由于歷史原因，`PGOPTIONS`的格式和啟動`postgres`命令時用到的相似，特別是`-c`標志必須被指定 ``` env PGOPTIONS="-c geqo=off -c statement_timeout=5min" psql ```