postgresql 中的参数查看和修改方式

test=# show config_file;
     config_file     
------------------------------
 /data/pgdata/postgresql.conf
(1 row)
test=# show hba_file 
test-# ;
     hba_file     
--------------------------
 /data/pgdata/pg_hba.conf
(1 row)
test=# show ident_file ;
     ident_file     
----------------------------
 /data/pgdata/pg_ident.conf

test=# show all;
-------------------------------------+------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------
 allow_system_table_mods       | off                         | Allows modifications of the structure of system tables.
 application_name          | psql                         | Sets the application name to be reported in statistics and logs.
 archive_command           | test ! -f /data/archive/%f && cp %p /data/archive/%f | Sets the shell command that will be called to archive a WAL file.
 archive_mode            | on                          | Allows archiving of WAL files using archive_command.
 archive_timeout           | 0                          | Forces a switch to the next WAL file if a new file has not been started within N seconds.
 array_nulls             | on                          | Enable input of NULL elements in arrays.
...
 
test=# show work_mem;
 work_mem 
----------
 4MB
(1 row)
 
test=# \x
Expanded display is on.
test=# select * from pg_settings where name in ('work_mem')
test-# ;
-[ RECORD 1 ]---+----------------------------------------------------------------------------------------------------------------------
name      | work_mem
setting     | 4096
unit      | kB
category    | Resource Usage / Memory
short_desc   | Sets the maximum memory to be used for query workspaces.
extra_desc   | This much memory can be used by each internal sort operation and hash table before switching to temporary disk files.
context     | user
vartype     | integer
source     | default
min_val     | 64
max_val     | 2147483647
enumvals    | 
boot_val    | 4096
reset_val    | 4096
sourcefile   | 
sourceline   | 
pending_restart | f

test=# select * from pg_file_settings where error is not null;
      sourcefile       | sourceline | seqno |   name    | setting | applied |      error       
-----------------------------------+------------+-------+-----------------+---------+---------+------------------------------
 /data/pgdata/postgresql.auto.conf |     4 |  22 | max_connections | 10000  | f    | setting could not be applied
(1 row)

test=# show work_mem;
 work_mem 
----------
 4MB
(1 row)
test=# alter system set work_mem='8MB';
ALTER SYSTEM
test=# show work_mem;
 work_mem 
----------
 4MB
(1 row)

[postgres@postgresql1 pgdata]$ cat postgresql.auto.conf 
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
work_mem = '8MB'

 
test=# show work_mem ;
 work_mem 
----------
 8MB
(1 row)

test=# 
test=# set work_mem='16MB';
SET
test=# show work_mem;
 work_mem 
----------
 16MB
(1 row)

postgres=# show work_mem;
 work_mem 
----------
 4MB
(1 row)

test=# show work_mem;
 work_mem 
----------
 16MB
(1 row)
test=# begin;
BEGIN
test=# set local work_mem='8MB';
SET
test=# show work_mem;
 work_mem 
----------
 8MB
(1 row)
test=# commit;
COMMIT
test=# show work_mem;
 work_mem 
----------
 16MB

test=# show work_mem;
 work_mem 
----------
 16MB
(1 row)
test=# reset work_mem;
RESET
test=# show work_mem;
 work_mem 
----------
 4MB
(1 row)
 
test=# reset all;
RESET

test=# alter database test set work_mem='16MB';
ALTER DATABASE

postgres=# alter role brent set work_mem='2MB';
ALTER ROLE

postgres=# \c test brent
You are now connected to database "test" as user "brent".
test=> 
test=> 
test=> show work_mem;
 work_mem 
----------
 2MB

test=# alter role brent in database test set work_mem='8MB';
ALTER ROLE

test=# select name,setting,source from pg_settings where name='work_mem';
  name  | setting | source 
----------+---------+----------
 work_mem | 16384  | database

test=# \c test brent
You are now connected to database "test" as user "brent".
test=> select name,setting,source from pg_settings where name='work_mem';
  name  | setting |  source   
----------+---------+---------------
 work_mem | 8192  | database user