
        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 
        ----------
         4MB
        (1 row)
        

Для установки требуется повторное считывание файлов конфигурации

        => select pg_reload_conf();
         pg_reload_conf 
        ----------------
         t
        (1 row)
        

        => SHOW work_mem;
         work_mem 
        ----------
         8MB
        (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 
        ----------
         4MB
        (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
