Viewing Server Configuration Parameter Settings

The SQL command SHOW allows you to see the current server configuration parameter settings. For example, to see the settings for all parameters:
$ psql -c 'SHOW ALL;'

For Example

[20:46 sachi@sachi ~] > psql -c 'SHOW ALL;'
Password: 
      1                    name                    |    setting     |                                                                                   descr      1 iption                                                                                    
      2 -------------------------------------------+----------------+----------------------------------------------------------------------------------------      
      2 ------------------------------------------------------------------------------------------
      3  add_missing_from                          | off            | Automatically adds missing table references to FROM clauses.
      4  application_name                          |                | Sets the application name to be reported in statistics and logs.
      5  array_nulls                               | on             | Enable input of NULL elements in arrays.
      6  authentication_timeout                    | 1min           | Sets the maximum time in seconds to complete client authentication.
      7  backslash_quote                           | safe_encoding  | Sets whether "\'" is allowed in string literals.
      8  block_size                                | 32768          | Shows size of a disk block
      9  bonjour_name                              |                | Sets the Bonjour broadcast service name.
     10  check_function_bodies                     | on             | Check function bodies during CREATE FUNCTION.
     11  client_encoding                           | UTF8           | Sets the client's character set encoding.
     12  client_min_messages                       | notice         | Sets the message levels that are sent to the client.
     13  cpu_index_tuple_cost                      | 0.005          | Sets the planner's estimate of the cost of processing each index entry during an index      13 scan.
     14  cpu_operator_cost                         | 0.0025         | Sets the planner's estimate of the cost of processing each operator or function call.
     15  cpu_tuple_cost                            | 0.01           | Sets the planner's estimate of the cost of processing each tuple (row).
     16  cursor_tuple_fraction                     | 1              | Sets the planner's estimate of the fraction of a cursor's rows that will be retrieved.
     17  custom_variable_classes                   |                | Sets the list of known custom variable classes.
     18  DateStyle                                 | ISO, MDY       | Sets the display format for date and time values.
     19  db_user_namespace                         | off            | Enables per-database user names.
     20  deadlock_timeout                          | 1s             | The time in milliseconds to wait on lock before checking for deadlock.
     21  debug_assertions                          | off            | Turns on various assertion checks.
     22  debug_pretty_print                        | off            | Indents parse and plan tree displays.
     23  debug_print_parse                         | off            | Prints the parse tree to the server log.
     24  debug_print_plan                          | off            | Prints the execution plan to server log.
     25  debug_print_prelim_plan                   | off            | Prints the preliminary execution plan to server log.
     26  debug_print_rewritten                     | off            | Prints the parse tree after rewriting to server log.
     27  debug_print_slice_table                   | off            | Prints the slice table to server log.
     28  default_statistics_target                 | 25             | Sets the default statistics target.
     29  default_tablespace                        |                | Sets the default tablespace to create tables and indexes in.
     30  default_transaction_isolation             | read committed | Sets the transaction isolation level of each new transaction.
     31  default_transaction_read_only             | off            | Sets the default read-only status of new transactions.
     32  effective_cache_size                      | 512MB          | Sets the planner's assumption about size of the disk cache.
     33  enable_bitmapscan                         | on             | Enables the planner's use of bitmap-scan plans.
     34  enable_groupagg                           | on             | Enables the planner's use of grouping aggregation plans.
     35  enable_hashagg                            | on             | Enables the planner's use of hashed aggregation plans.
     36  enable_hashjoin                           | on             | Enables the planner's use of hash join plans.
     37  enable_indexscan                          | on             | Enables the planner's use of index-scan plans.
     38  enable_mergejoin                          | off            | Enables the planner's use of merge join plans.
     39  enable_nestloop                           | off            | Enables the planner's use of nested-loop join plans.
     40  enable_seqscan                            | on             | Enables the planner's use of sequential-scan plans.
     41  enable_sort                               | on             | Enables the planner's use of explicit sort steps.
     42  enable_tidscan                            | on             | Enables the planner's use of TID scan plans.
     43  escape_string_warning                     | on             | Warn about backslash escapes in ordinary string literals.
     44  explain_pretty_print                      | on             | Uses the indented output format for EXPLAIN VERBOSE.
     45  extra_float_digits                        | 0              | Sets the number of digits displayed for floating-point values.
     46  from_collapse_limit                       | 20             | Sets the FROM-list size beyond which subqueries are not collapsed.
     47  gp_adjust_selectivity_for_outerjoins      | on             | Adjust selectivity of null tests over outer joins.
     48  gp_analyze_relative_error                 | 0.25           | target relative error fraction for row sampling during analyze
     49  gp_autostats_mode                         | ON_NO_STATS    | Sets the autostats mode.
     50  gp_autostats_on_change_threshold          | 2147483647     | Threshold for number of tuples added to table by CTAS or Insert-to to trigger autostats     50  in on_change mode. See gp_autostats_mode.
     51  gp_backup_directIO                        | off            | Enable direct IO dump
     52  gp_backup_directIO_read_chunk_mb          | 20             | Size of read Chunk buffer in directIO dump (in MB)
     53  gp_cached_segworkers_threshold            | 5              | Sets the maximum number of segment workers to cache between statements.
     54  gp_command_count                          | 1              | Shows the number of commands received from the client in this session.
     55  gp_connections_per_thread                 | 64             | Sets the number of client connections handled in each thread.
     56  gp_contentid                              | -1             | The contentid used by this server.
     57  gp_dbid                                   | 1              | The dbid used by this server.
     58  gp_debug_linger                           | 0              | Number of seconds for QD/QE process to linger upon fatal internal error.
     59  gp_dynamic_partition_pruning              | on             | This guc enables plans that can dynamically eliminate scanning of partitions.
     60  gp_enable_adaptive_nestloop               | on             | Enables the planner's use of the Adaptive Join Choice operator.
     61  gp_enable_agg_distinct                    | on             | Enable 2-phase aggregation to compute a single distinct-qualified aggregate.
     62  gp_enable_agg_distinct_pruning            | on             | Enable 3-phase aggregation and join to compute distinct-qualified aggregates.
     63  gp_enable_direct_dispatch                 | on             | Enable dispatch for single-row-insert targetted mirror-pairs.
     64  gp_enable_fallback_plan                   | on             | Plan types which are not enabled may be used when a query would be infeasible without t     64 hem.
     65  gp_enable_fast_sri                        | on             | Enable single-slice single-row inserts.
     66  gp_enable_gpperfmon                       | on             | Enable gpperfmon monitoring.
     67  gp_enable_groupext_distinct_gather        | on             | Enable gathering data to a single node to compute distinct-qualified aggregates on grou     67 ping extention queries.
     68  gp_enable_groupext_distinct_pruning       | on             | Enable 3-phase aggregation and join to compute distinct-qualified aggregates on groupin     68 g extention queries.
     69  gp_enable_multiphase_agg                  | on             | Enables the planner's use of two- or three-stage parallel aggregation plans.
     70  gp_enable_predicate_propagation           | on             | When two expressions are equivalent (such as with equijoined keys) then the planner app     70 lies predicates on one expression to the other expression.
     71  gp_enable_preunique                       | on             | Enable 2-phase duplicate removal.
     72  gp_enable_sequential_window_plans         | on             | Experimental feature: Enable non-parallel window plans.
     73  gp_enable_sort_distinct                   | on             | Enable duplicate removal to be performed while sorting.
     74  gp_enable_sort_limit                      | on             | Enable LIMIT operation to be performed while sorting.
     75  gp_external_enable_exec                   | on             | Enable selecting from an external table with an EXECUTE clause.
     76  gp_external_grant_privileges              | off            | Enable non superusers to create http or gpfdist external tables.
     77  gp_external_max_segs                      | 64             | Maximum number of segments that connect to a single gpfdist URL.
     78  gp_filerep_tcp_keepalives_count           | 2              | Maximum number of TCP keepalive retransmits for FileRep connection.
     79  gp_filerep_tcp_keepalives_idle            | 1min           | Seconds between issuing TCP keepalives for FileRep connection.
     80  gp_filerep_tcp_keepalives_interval        | 30s            | Seconds between TCP keepalive retransmits for FileRep connection.
     81  gp_fts_probe_interval                     | 1min           | A complete probe of all segments starts each time a timer with this period expires.
     82  gp_fts_probe_threadcount                  | 16             | Use this number of threads for probing the segments.
     83  gp_fts_probe_timeout                      | 20s            | Maximum time (in seconds) allowed for FTS to complete probing a segment.
     84  gp_gpperfmon_send_interval                | 1              | Interval in seconds between sending messages to gpperfmon.
     85  gp_hadoop_home                            |                | The location where Hadoop is installed in each segment.
     86  gp_hadoop_target_version                  | gphd-1.1       | The distro/version of Hadoop that external table is connecting to.
     87  gp_hashjoin_tuples_per_bucket             | 5              | Target density of hashtable used by Hashjoin during execution
     88  gp_idf_deduplicate                        | auto           | Sets the mode to control inverse distribution function's de-duplicate strategy.
     89  gp_interconnect_hash_multiplier           | 2              | Sets the number of hash buckets used by the UDP interconnect to track connections (the      89 number of buckets is given by the product of the segment count and the hash multipliers).
     90  gp_interconnect_queue_depth               | 4              | Sets the maximum size of the receive queue for each connection in the UDP interconnect
     91  gp_interconnect_setup_timeout             | 2h             | Timeout (in seconds) on interconnect setup that occurs at query start
     92  gp_interconnect_type                      | UDP            | Sets the protocol used for inter-node communication.
     93  gp_log_format                             | csv            | Sets the format for log files.
     94  gp_max_csv_line_length                    | 1048576        | Maximum allowed length of a csv input data row in bytes
     95  gp_max_databases                          | 16             | Sets the maximum number of databases.
     96  gp_max_filespaces                         | 8              | Sets the maximum number of filespaces.
     97  gp_max_local_distributed_cache            | 1024           | Sets the number of local-distributed transactions to cache for optimizing visibility pr     97 ocessing by backends.
     98  gp_max_packet_size                        | 8192           | Sets the max packet size for the Interconnect.
     99  gp_max_tablespaces                        | 16             | Sets the maximum number of tablespaces.
    100  gp_motion_cost_per_row                    | 0              | Sets the planner's estimate of the cost of moving a row between worker processes.
    101  gp_num_contents_in_cluster                | 2              | Sets the number of segments in the cluster.
    102  gp_reject_percent_threshold               | 300            | Reject limit in percent starts calculating after this number of rows processed
    103  gp_reraise_signal                         | on             | Do we attempt to dump core when a serious problem occurs.
    104  gp_resqueue_memory_policy                 | eager_free     | Sets the policy for memory allocation of queries.
    105  gp_resqueue_priority                      | on             | Enables priority scheduling.
    106  gp_resqueue_priority_cpucores_per_segment | 4              | Number of processing units associated with a segment.
    107  gp_resqueue_priority_sweeper_interval     | 1000           | Frequency (in ms) at which sweeper process re-evaluates CPU shares.
    108  gp_role                                   | dispatch       | Sets the role for the session.
    109  gp_safefswritesize                        | 0              | Minimum FS safe write size.
    110  gp_segment_connect_timeout                | 10min          | Maximum time (in seconds) allowed for a new worker process to start or a mirror to resp    110 ond.
    111  gp_segments_for_planner                   | 0              | If >0, number of segment dbs for the planner to assume in its cost and size estimates.
    112  gp_session_id                             | 170069         | Global ID used to uniquely identify a particular session in an Greenplum Database array
    113  gp_set_proc_affinity                      | off            | On postmaster startup, attempt to bind postmaster to a processor
    114  gp_standby_dbid                           | 0              | Sets DBID of standby master.
    115  gp_statistics_pullup_from_child_partition | on             | This guc enables the planner to utilize statistics from partitions in planning queries     115 on the parent.
    116  gp_statistics_use_fkeys                   | on             | This guc enables the planner to utilize statistics derived from foreign key relationshi    116 ps.
    117  gp_subtrans_warn_limit                    | 16777216       | Sets the warning limit on number of subtransactions in a transaction.
    118  gp_vmem_idle_resource_timeout             | 18s            | Sets the time a session can be idle (in milliseconds) before we release gangs on the se    118 gment DBs to free resources.
    119  gp_vmem_protect_limit                     | 8192           | Virtual memory limit (in MB) of Greenplum memory protection.
    120  gp_vmem_protect_segworker_cache_limit     | 500            | Max virtual memory limit (in MB) for a segworker to be cachable.
    121  gp_workfile_checksumming                  | on             | Enable checksumming on the executor work files in order to catch possible faulty writes    121  caused by your disk drivers.
    122  gp_workfile_compress_algorithm            | none           | Specify the compression algorithm that work files in the query executor use.
    123  gpperfmon_port                            | 8888           | Sets the port number of gpperfmon.
    124  integer_datetimes                         | on             | Datetimes are integer based.
    125  IntervalStyle                             | postgres       | Sets the display format for interval values.
    126  join_collapse_limit                       | 20             | Sets the FROM-list size beyond which JOIN constructs are not flattened.
    127  krb_caseins_users                         | off            | Sets whether Kerberos user names should be treated as case-insensitive.
    128  krb_srvname                               | postgres       | Sets the name of the Kerberos service.
    129  lc_collate                                | en_US.utf8     | Shows the collation order locale.
    130  lc_ctype                                  | en_US.utf8     | Shows the character classification and case conversion locale.
    131  lc_messages                               | en_US.utf8     | Sets the language in which messages are displayed.
    132  lc_monetary                               | en_US.utf8     | Sets the locale for formatting monetary amounts.
    133  lc_numeric                                | en_US.utf8     | Sets the locale for formatting numbers.
    134  lc_time                                   | en_US.utf8     | Sets the locale for formatting date and time values.
    135  listen_addresses                          | *              | Sets the host name or IP address(es) to listen to.
    136  local_preload_libraries                   |                | Lists shared libraries to preload into each backend.
    137  log_autostats                             | off            | Logs details of auto-stats issued ANALYZEs.
    138  log_connections                           | on             | Logs each successful connection.
    139  log_disconnections                        | on             | Logs end of a session, including duration.
    140  log_dispatch_stats                        | off            | Writes dispatcher performance statistics to the server log.
    141  log_duration                              | off            | Logs the duration of each completed SQL statement.
    142  log_error_verbosity                       | default        | Sets the verbosity of logged messages.
    143  log_executor_stats                        | off            | Writes executor performance statistics to the server log.
    144  log_hostname                              | off            | Logs the host name in the connection logs.
    145  log_min_duration_statement                | -1             | Sets the minimum execution time in milliseconds above which statements will be logged.
    146  log_min_error_statement                   | error          | Causes all statements generating error at or above this level to be logged.
    147  log_min_messages                          | warning        | Sets the message levels that are logged.
    148  log_parser_stats                          | off            | Writes parser performance statistics to the server log.
    149  log_planner_stats                         | off            | Writes planner performance statistics to the server log.
    150  log_rotation_age                          | 1d             | Automatic log file rotation will occur after N minutes
    151  log_rotation_size                         | 0              | Automatic log file rotation will occur after N kilobytes
    152  log_statement                             | all            | Sets the type of statements logged.
    153  log_statement_stats                       | off            | Writes cumulative performance statistics to the server log.
    154  log_timezone                              | US/Eastern     | Sets the time zone to use in log messages.
    155  log_truncate_on_rotation                  | off            | Truncate existing log files of same name during log rotation.
    156  maintenance_work_mem                      | 64MB           | Sets the maximum memory to be used for maintenance operations.
    157  max_appendonly_tables                     | 10000          | Maximum number of different (unrelated) append only tables that can participate in writ    157 ing data concurrently.
    158  max_connections                           | 250            | Sets the maximum number of concurrent connections.
    159  max_files_per_process                     | 1000           | Sets the maximum number of simultaneously open files for each server process.
    160  max_fsm_pages                             | 200000         | Sets the maximum number of disk pages for which free space is tracked.
    161  max_fsm_relations                         | 1000           | Sets the maximum number of tables and indexes for which free space is tracked.
    162  max_function_args                         | 100            | Shows the maximum number of function arguments.
    163  max_identifier_length                     | 63             | Shows the maximum identifier length
    164  max_index_keys                            | 32             | Shows the maximum number of index keys.
    165  max_locks_per_transaction                 | 128            | Sets the maximum number of locks per transaction.
    166  max_prepared_transactions                 | 250            | Sets the maximum number of simultaneously prepared transactions.
    167  max_resource_portals_per_transaction      | 64             | Maximum number of resource queues.
    168  max_resource_queues                       | 9              | Maximum number of resource queues.
    169  max_stack_depth                           | 2MB            | Sets the maximum stack depth, in kilobytes.
    170  max_statement_mem                         | 2000MB         | Sets the maximum value for statement_mem setting.
    171  max_work_mem                              | 1000MB         | Sets the maximum value for work_mem setting.
    172  password_encryption                       | on             | Encrypt passwords.
    173  pljava_classpath                          |                | classpath used by the the JVM
    174  port                                      | 5432           | Sets the TCP port the server listens on.
    175  random_page_cost                          | 100            | Sets the planner's estimate of the cost of a nonsequentially fetched disk page.
    176  regex_flavor                              | advanced       | Sets the regular expression "flavor".
    177  resource_cleanup_gangs_on_wait            | on             | Enable idle gang cleanup before resource lockwait.
    178  resource_select_only                      | off            | Enable resource locking of SELECT only.
    179  search_path                               | "$user",public | Sets the schema search order for names that are not schema-qualified.
    180  seq_page_cost                             | 1              | Sets the planner's estimate of the cost of a sequentially fetched disk page.
    181  server_encoding                           | UTF8           | Sets the server (database) character set encoding.
    182  server_version                            | 8.2.15         | Shows the server version.
    183  server_version_num                        | 80215          | Shows the server version as an integer.
    184  shared_buffers                            | 125MB          | Sets the number of shared memory buffers used by the server.
    185  ssl                                       | off            | Enables SSL connections.
    186  standard_conforming_strings               | off            | '...' strings treat backslashes literally.
    187  statement_mem                             | 125MB          | Sets the memory to be reserved for a statement.
    188  statement_timeout                         | 0              | Sets the maximum allowed duration (in milliseconds) of any statement.
    189  stats_queue_level                         | off            | Collects resource queue-level statistics on database activity.
    190  superuser_reserved_connections            | 3              | Sets the number of connection slots reserved for superusers.
    191  tcp_keepalives_count                      | 0              | Maximum number of TCP keepalive retransmits.
    192  tcp_keepalives_idle                       | 0              | Seconds between issuing TCP keepalives.
    193  tcp_keepalives_interval                   | 0              | Seconds between TCP keepalive retransmits.
    194  temp_buffers                              | 1024           | Sets the maximum number of temporary buffers used by each session.
    195  TimeZone                                  | US/Eastern     | Sets the time zone for displaying and interpreting time stamps.
    196  timezone_abbreviations                    | Default        | Selects a file of time zone abbreviations.
    197  track_activities                          | on             | Collects information about executing commands.
    198  track_counts                              | off            | Collects statistics on database activity.
    199  transaction_isolation                     | read committed | Sets the current transaction's isolation level.
    200  transaction_read_only                     | off            | Sets the current transaction's read-only status.
    201  transform_null_equals                     | off            | Treats "expr=NULL" as "expr IS NULL".
    202  unix_socket_group                         |                | Sets the owning group of the Unix-domain socket.
    203  unix_socket_permissions                   | 511            | Sets the access permissions of the Unix-domain socket.
    204  update_process_title                      | on             | Updates the process title to show the active SQL command.
    205  vacuum_cost_delay                         | 0              | Vacuum cost delay in milliseconds.
    206  vacuum_cost_limit                         | 200            | Vacuum cost amount available before napping.
    207  vacuum_cost_page_dirty                    | 20             | Vacuum cost for a page dirtied by vacuum.
    208  vacuum_cost_page_hit                      | 1              | Vacuum cost for a page found in the buffer cache.
    209  vacuum_cost_page_miss                     | 10             | Vacuum cost for a page not found in the buffer cache.
    210  vacuum_freeze_min_age                     | 100000000      | Minimum age at which VACUUM should freeze a table row.
    211  work_mem                                  | 32MB           | Sets the maximum memory to be used for query workspaces.
    212 (209 rows)


SHOW lists the settings for the master instance only. To see the value of a particular parameter across the entire system (master and all segments), use the gpconfig
utility. For example:
$ gpconfig --show max_connections



Comments