psql КОНФИГУРИРОВАНИЕ СЕРВЕРА ~~~~~~~~~~~~~~~~~~~~~~~~ ** Файл postgresql.conf ----------------------------------------------------------------------- => select * from regexp_split_to_table(pg_read_file('postgresql.conf'), '\n') limit 50; regexp_split_to_table --------------------------------------------------------------------------------- # ----------------------------- # PostgreSQL configuration file # ----------------------------- # # This file consists of lines of the form: # # name = value # # (The "=" is optional.) Whitespace may be used. Comments are introduced with # "#" anywhere on a line. The complete list of parameter names and allowed # values can be found in the PostgreSQL documentation. # # The commented-out settings shown in this file represent the default values. # Re-commenting a setting is NOT sufficient to revert it to the default value; # you need to reload the server. # # This file is read on server startup and when the server receives a SIGHUP # signal. If you edit the file on a running system, you have to SIGHUP the # server for the changes to take effect, or use "pg_ctl reload". Some # parameters, which are marked below, require a server shutdown and restart to # take effect. # # Any parameter can also be given as a command-line option to the server, e.g., # "postgres -c log_connections=on". Some parameters can be changed at run time # with the "SET" SQL command. # # Memory units: kB = kilobytes Time units: ms = milliseconds # MB = megabytes s = seconds # GB = gigabytes min = minutes # TB = terabytes h = hours # d = days #------------------------------------------------------------------------------ # FILE LOCATIONS #------------------------------------------------------------------------------ # The default values of these variables are driven from the -D command-line # option or PGDATA environment variable, represented here as ConfigDir. #data_directory = 'ConfigDir' # use data in another directory # (change requires restart) #hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file # (change requires restart) #ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file # (change requires restart) # If external_pid_file is not explicitly set, no extra PID file is written. #external_pid_file = '' # write an extra PID file # (change requires restart) (50 rows) ----------------------------------------------------------------------- ** Команда ALTER SYSTEM ----------------------------------------------------------------------- => select * from regexp_split_to_table(pg_read_file('postgresql.auto.conf'), '\n'); regexp_split_to_table --------------------------------------------------- # Do not edit this file manually! # It will be overwritten by ALTER SYSTEM command. (3 rows) ----------------------------------------------------------------------- => ALTER SYSTEM SET work_mem TO '8mb'; ERROR: invalid value for parameter "work_mem": "8mb" HINT: Valid units for this parameter are "kB", "MB", "GB", and "TB". ALTER SYSTEM выполняет проверку на допустимые значения ----------------------------------------------------------------------- => ALTER SYSTEM SET work_mem TO '8MB'; ALTER SYSTEM ----------------------------------------------------------------------- Значение 8MB записано в файл postgresql.auto.conf => select * from regexp_split_to_table(pg_read_file('postgresql.auto.conf'), '\n'); regexp_split_to_table --------------------------------------------------- # Do not edit this file manually! # It will be overwritten by ALTER SYSTEM command. work_mem = '8MB' (4 rows) ----------------------------------------------------------------------- ... но не установлено => SHOW work_mem; work_mem ---------- 16MB (1 row) Для установки требуется повторное считывание файлов конфигурации => select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) => SHOW work_mem; work_mem ---------- 16MB (1 row) ----------------------------------------------------------------------- Для удаления строк из postgresql.auto.conf также используется ALTER SYSTEM => ALTER SYSTEM RESET work_mem; ALTER SYSTEM => select * from regexp_split_to_table(pg_read_file('postgresql.auto.conf'), '\n'); regexp_split_to_table --------------------------------------------------- # Do not edit this file manually! # It will be overwritten by ALTER SYSTEM command. (3 rows) ----------------------------------------------------------------------- Восстановим значение по умолчанию => select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) => SHOW work_mem; work_mem ---------- 16MB (1 row) ----------------------------------------------------------------------- ** Установка параметров во время исполнения ----------------------------------------------------------------------- => SET work_mem TO '8MB'; SET => select set_config('work_mem', '8MB', false); set_config ------------ 8MB (1 row) => UPDATE pg_settings => SET setting = '8MB' => WHERE name = 'work_mem'; set_config ------------ 8MB (1 row) UPDATE 0 ----------------------------------------------------------------------- UPDATE на pg_settings вызывает set_config() => \d+ pg_settings View "pg_catalog.pg_settings" Column | Type | Modifiers | Storage | Description ------------+---------+-----------+----------+------------- name | text | | extended | setting | text | | extended | unit | text | | extended | category | text | | extended | short_desc | text | | extended | extra_desc | text | | extended | context | text | | extended | vartype | text | | extended | source | text | | extended | min_val | text | | extended | max_val | text | | extended | enumvals | text[] | | extended | boot_val | text | | extended | reset_val | text | | extended | sourcefile | text | | extended | sourceline | integer | | plain | View definition: SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline FROM pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline); Rules: pg_settings_n AS ON UPDATE TO pg_settings DO INSTEAD NOTHING pg_settings_u AS ON UPDATE TO pg_settings WHERE new.name = old.name DO SELECT set_config(old.name, new.setting, false) AS set_config ----------------------------------------------------------------------- ** Чтение значений параметров во время выполнения ----------------------------------------------------------------------- => SHOW work_mem; work_mem ---------- 8MB (1 row) => select current_setting ('work_mem'); current_setting ----------------- 8MB (1 row) => select name, setting, unit from pg_settings where name = 'work_mem'; name | setting | unit ----------+---------+------ work_mem | 8192 | kB (1 row) ----------------------------------------------------------------------- ** Установка параметров для новых сессий на уровне БД и ролей ----------------------------------------------------------------------- => ALTER DATABASE postgres SET work_mem TO '16MB'; ALTER DATABASE => ALTER ROLE postgres SET work_mem TO '32MB'; ALTER ROLE => ALTER ROLE postgres IN DATABASE template1 SET work_mem TO '6MB'; ALTER ROLE ----------------------------------------------------------------------- => SELECT r.rolname, d.datname, rs.setconfig => FROM pg_db_role_setting rs => LEFT JOIN pg_roles r ON r.oid = rs.setrole => LEFT JOIN pg_database d ON d.oid = rs.setdatabase; rolname | datname | setconfig ----------+-----------+----------------- | postgres | {work_mem=16MB} postgres | | {work_mem=32MB} postgres | template1 | {work_mem=6MB} (3 rows) ----------------------------------------------------------------------- Проверяем для новых сессий => \c postgres postgres You are now connected to database "postgres" as user "postgres". => SHOW work_mem; work_mem ---------- 32MB (1 row) ----------------------------------------------------------------------- => \c template1 postgres You are now connected to database "template1" as user "postgres". => SHOW work_mem; work_mem ---------- 6MB (1 row) ----------------------------------------------------------------------- => CREATE ROLE test_role LOGIN; CREATE ROLE => \c postgres test_role You are now connected to database "postgres" as user "test_role". => SHOW work_mem; work_mem ---------- 16MB (1 row) ----------------------------------------------------------------------- Подключимся под postgres => \c postgres postgres You are now connected to database "postgres" as user "postgres". => drop role test_role; DROP ROLE Удаление настроек для БД и ролей => ALTER DATABASE postgres RESET work_mem; ALTER DATABASE => SELECT r.rolname, d.datname, rs.setconfig => FROM pg_db_role_setting rs => LEFT JOIN pg_roles r ON r.oid = rs.setrole => LEFT JOIN pg_database d ON d.oid = rs.setdatabase; rolname | datname | setconfig ----------+-----------+----------------- postgres | | {work_mem=32MB} postgres | template1 | {work_mem=6MB} (2 rows) ----------------------------------------------------------------------- => ALTER ROLE postgres RESET ALL; ALTER ROLE => ALTER ROLE postgres IN DATABASE template1 RESET ALL; ALTER ROLE => SELECT r.rolname, d.datname, rs.setconfig => FROM pg_db_role_setting rs => LEFT JOIN pg_roles r ON r.oid = rs.setrole => LEFT JOIN pg_database d ON d.oid = rs.setdatabase; rolname | datname | setconfig ---------+---------+----------- (0 rows) => \c postgres postgres You are now connected to database "postgres" as user "postgres". ----------------------------------------------------------------------- ** Установка параметров для функций ----------------------------------------------------------------------- => CREATE FUNCTION f_test () RETURNS int => AS $$ => DECLARE => l_work_mem text; => BEGIN => RAISE NOTICE 'work_mem: %', current_setting('work_mem'); => RETURN 0; => END; => $$ LANGUAGE plpgsql; CREATE FUNCTION => ALTER FUNCTION f_test() SET work_mem TO '16MB'; ALTER FUNCTION ----------------------------------------------------------------------- => RESET work_mem; RESET => SHOW work_mem; work_mem ---------- 4MB (1 row) => select f_test(); NOTICE: work_mem: 16MB f_test -------- 0 (1 row) => SHOW work_mem; work_mem ---------- 4MB (1 row) => drop function f_test(); DROP FUNCTION ----------------------------------------------------------------------- Конец демонстрации. ----------------------------------------------------------------------- => \q