ANBOB™

对于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 ;}'

打赏

微信扫一扫，打赏作者吧～