对于oracle DBA查看数据库实例参数可以在sqlplus中使用show prameter xxx 模糊匹配非隐藏参数或已修改隐藏参数，当然也可以查询v$ 的视图， 在openGauss或postgresql当前版本中需要匹配输入参数名，当然参数名我们不可能完全记的全名，模糊搜索需要手动创建个shell方法。
在postgresql中查看参数的几个方法
1， show all
Displays the current setting of run-time parameters，也就是运行时当前的参数。只有3列，也可以指定具体的参数值。相当于oracle sqlplus的show parameter ,在og/pg通用
openGauss=# show all;
openGauss=# show autovacuum;
autovacuum
------------
on
(1 row)
openGauss=# show autovac;
ERROR: unrecognized configuration parameter "autovac"
2, pg_settings
显示结果和show all相同， 只不过pg_settings view显示的列更多，等同于oracle的v$parameter, 在og/pg通用。
openGauss=# select * from pg_settings;
3， pg_file_settings
该view pg_file_settings.查询的是postgresql.conf配置文件的内容。 相当于oracle的v$spparameter, 这是目前postgresql有，而opengauss还没有的view.
[local]:5432 postgres@postgres=# TABLE pg_file_settings;
sourcefile | sourceline | seqno | name | setting | applied | error
-------------------------------------------------------+------------+-------+----------------------------+--------------------------------------------+---------+-------
/opensource/postgreSQL/13.2/data/postgresql.conf | 60 | 1 | listen_addresses | * | t |
/opensource/postgreSQL/13.2/data/postgresql.conf | 64 | 2 | port | 5432 | t |
/opensource/postgreSQL/13.2/data/postgresql.conf | 66 | 3 | max_connections | 100 | t |
/opensource/postgreSQL/13.2/data/postgresql.conf | 123 | 4 | shared_buffers | 128MB | f |
/opensource/postgreSQL/13.2/data/postgresql.conf | 144 | 5 | dynamic_shared_memory_type | posix | t |
/opensource/postgreSQL/13.2/data/postgresql.conf | 230 | 6 | max_wal_size | 1GB | t |
/opensource/postgreSQL/13.2/data/postgresql.conf | 231 | 7 | min_wal_size | 80MB | t |
/opensource/postgreSQL/13.2/data/postgresql.conf | 565 | 8 | log_timezone | America/New_York | t |
/opensource/postgreSQL/13.2/data/postgresql.conf | 680 | 9 | datestyle | iso, mdy | t |
/opensource/postgreSQL/13.2/data/postgresql.conf | 683 | 10 | timezone | Asia/Shanghai | t |
/opensource/postgreSQL/13.2/data/postgresql.conf | 684 | 11 | orafce.timezone | PRC | t |
/opensource/postgreSQL/13.2/data/postgresql.conf | 698 | 12 | lc_messages | C | t |
/opensource/postgreSQL/13.2/data/postgresql.conf | 700 | 13 | lc_monetary | C | t |
/opensource/postgreSQL/13.2/data/postgresql.conf | 701 | 14 | lc_numeric | C | t |
/opensource/postgreSQL/13.2/data/postgresql.conf | 702 | 15 | lc_time | C | t |
/opensource/postgreSQL/13.2/data/postgresql.conf | 705 | 16 | default_text_search_config | pg_catalog.english | t |
/opensource/postgreSQL/13.2/data/postgresql.conf | 785 | 17 | shared_preload_libraries | pg_stat_statements,pgsentinel,pg_hint_plan | t |
/opensource/postgreSQL/13.2/data/postgresql.conf | 787 | 18 | track_activity_query_size | 2048 | t |
/opensource/postgreSQL/13.2/data/postgresql.conf | 789 | 19 | pg_stat_statements.track | all | t |
/opensource/postgreSQL/13.2/data/postgresql.conf | 790 | 20 | pg_stat_statements.max | 10000 | t |
/opensource/postgreSQL/13.2/data/postgresql.auto.conf | 3 | 21 | log_rotation_age | 10081 | t |
/opensource/postgreSQL/13.2/data/postgresql.auto.conf | 4 | 22 | logging_collector | on | t |
/opensource/postgreSQL/13.2/data/postgresql.auto.conf | 5 | 23 | log_destination | stderr | t |
/opensource/postgreSQL/13.2/data/postgresql.auto.conf | 6 | 24 | log_statement | none | t |
/opensource/postgreSQL/13.2/data/postgresql.auto.conf | 7 | 25 | shared_buffers | 256MB | t |
(25 rows)
BTW: 在postgresql系中， table xxx相当于select * from xxx;
我们如果想直接和oracle sqlplus一样模糊搜索，可以搞一个shell 为了美化可以使用AWR 实例ltrim 和substr 截取长度或删除尾部空格。
sub(/^[[:blank:]]*/,””,变量) 是去掉变量左边的空白符
sub(/[[:blank:]]*$/,””,变量) 是去掉变量右边的空白符
gsub(/[[:blank:]]*/,””,变量) 是去掉变量中所有的空白符
自己搞个shell
openGauss=# \! sh show vacuum
autovacuum | on | Starts the autovacuum subprocess.
autovacuum_analyze_scale_factor | 0.1 | Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples.
autovacuum_analyze_threshold | 50 | Minimum number of tuple inserts, updates, or deletes prior to analyze.
autovacuum_freeze_max_age | 4000000000 | Age at which to autovacuum a table.
autovacuum_io_limits | -1 | Sets io_limit for autovacum.
autovacuum_max_workers | 3 | Sets the maximum number of simultaneously running autovacuum worker processes.
autovacuum_mode | mix | Sets the behavior of autovacuum
autovacuum_naptime | 10min | Time to sleep between autovacuum runs.
autovacuum_vacuum_cost_delay | 20ms | Vacuum cost delay in milliseconds, for autovacuum.
autovacuum_vacuum_cost_limit | -1 | Vacuum cost amount available before napping, for autovacuum.
autovacuum_vacuum_scale_factor | 0.2 | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.
autovacuum_vacuum_threshold | 50 | Minimum number of tuple updates or deletes prior to vacuum.
enable_debug_vacuum | off | This parameter is just used for logging some vacuum info.
log_autovacuum_min_duration | -1 | Sets the minimum execution time above which autovacuum actions will be logged.
vacuum_cost_delay | 0 | Vacuum cost delay in milliseconds.
vacuum_cost_limit | 200 | Vacuum cost amount available before napping.
vacuum_cost_page_dirty | 20 | Vacuum cost for a page dirtied by vacuum.
vacuum_cost_page_hit | 1 | Vacuum cost for a page found in the buffer cache.
vacuum_cost_page_miss | 10 | Vacuum cost for a page not found in the buffer cache.
vacuum_defer_cleanup_age | 0 | Number of transactions by which VACUUM and HOT cleanup should be deferred, if any.
vacuum_freeze_min_age | 2000000000 | Minimum age at which VACUUM should freeze a table row.
vacuum_freeze_table_age | 4000000000 | Age at which VACUUM should scan whole table to freeze tuples.
vacuum_gtt_defer_check_age | 10000 | The defer check age of GTT, used to check expired data after vacuum.
openGauss=#
[og@oel7db1 ~]$ cat show
#!/bin/bash
# author: weizhao zhang(anbob.com)
gsql -d postgres -p 5432 -c 'show all;'|grep "$1"|awk -F"|" '{ sub(/[[:blank:]]*$/,"",$3);print substr($1,1,40) "|" substr($2,1,20) "|" $3 ;}'
