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 ```