How to list all non-default database settings

Products

• VMware Tanzu Greenplum

Issue/Introduction

This KB will describe how to list all non-default database settings.

Environment

Product Version: 6.11

Resolution

When required, we can use the query below to list all the non-default database settings:

select name,setting,boot_val from pg_settings where setting <> boot_val;

Please also be aware that the settings below are the default settings for the GPDB system (v6.11):

application_name               | psql                                                                             |
 client_encoding                | UTF8                                                                             | SQL_ASCII
 data_checksums                 | on                                                                               | off
 default_text_search_config     | pg_catalog.english                                                               | pg_catalog.simple
 geqo_seed                      | 1                                                                                | 0
 gp_autostats_mode              | on_no_stats                                                                      | none
 gp_autostats_mode_in_functions | on_change                                                                        | none
 gp_command_count               | 2                                                                                | 0
 gp_contentid                   | -1                                                                               | -10000
 gp_dbid                        | 1                                                                                | -10000
 gp_default_storage_options     | appendonly=false,blocksize=32768,compresstype=none,checksum=true,orientation=row |
 gp_enable_query_metrics        | on                                                                               | off
 gp_resqueue_memory_policy      | eager_free                                                                       | none
 gp_segment_connect_timeout     | 600                                                                              | 180
 gp_session_id                  | 10                                                                               | -1
 lc_collate                     | en_US.utf8                                                                       | C
 lc_ctype                       | en_US.utf8                                                                       | C
 lc_messages                    | en_US.utf8                                                                       |
 lc_monetary                    | en_US.utf8                                                                       | C
 lc_numeric                     | en_US.utf8                                                                       | C
 lc_time                        | en_US.utf8                                                                       | C
 listen_addresses               | *                                                                                | localhost
 log_autostats                  | off                                                                              | on
 log_file_mode                  | 0600                                                                             | 384
 log_statement                  | all                                                                              | none
 log_timezone                   | America/New_York                                                                 | GMT
 max_appendonly_tables          | 10000                                                                            | 2048
 max_connections                | 20                                                                               | 200
 max_prepared_transactions      | 250                                                                              | 50
 max_stack_depth                | 2048                                                                             | 100
 port                           | 6120                                                                             | 5432
 server_encoding                | UTF8                                                                             | SQL_ASCII
 shared_buffers                 | 4000                                                                             | 4096
 shared_preload_libraries       | metrics_collector                                                                |
 TimeZone                       | America/New_York                                                                 | GMT
 transaction_isolation          | read committed                                                                   | default
 unix_socket_permissions        | 0777                                                                             | 511
 wal_buffers                    | 125                                                                              | -1
(38 rows)