PostgreSQL 9.4: System Table – ‘pg_settings” – Queries that display Postgresql configuration settings

Obtaining Configuration settings.

Get Version of Database

select version();

Show Data directory

show data_directory;

Show config files

select name, setting 
from pg_settings
where category = 'File Locations';

 


Query your postgresql.conf file via pg_settings

name      - Name of Setting
context   - Does it require a hard or soft restart (postgres yes - user no)
unit      - MBs, KBs etc
setting   - Current value
boot_val  - Default setting
reset_val - New value once posgres service is restarted

Example

select 
name, context, unit, setting, boot_val, reset_val
from pg_settings
where name in 
('listen_addresses', 'max_connections', 'shared_buffers');
       name       |  context   | unit | setting | boot_val  | reset_val
------------------+------------+------+---------+-----------+-----------
 listen_addresses | postmaster |      | *       | localhost | *
 max_connections  | postmaster |      | 100     | 100       | 100
 shared_buffers   | postmaster | 8kB  | 16384   | 1024      | 16384
(3 rows)

All pg_settings fields you can query
All pg_settings fields

name | allow_system_table_mods
name | application_name
name | archive_command
name | archive_mode
name | archive_timeout
name | array_nulls
name | authentication_timeout
name | autovacuum
name | autovacuum_analyze_scale_factor
name | autovacuum_analyze_threshold
name | autovacuum_freeze_max_age
name | autovacuum_max_workers
name | autovacuum_multixact_freeze_max_age
name | autovacuum_naptime
name | autovacuum_vacuum_cost_delay
name | autovacuum_vacuum_cost_limit
name | autovacuum_vacuum_scale_factor
name | autovacuum_vacuum_threshold
name | autovacuum_work_mem
name | backslash_quote
name | bgwriter_delay
name | bgwriter_lru_maxpages
name | bgwriter_lru_multiplier
name | block_size
name | bonjour
name | bonjour_name
name | bytea_output
name | check_function_bodies
name | checkpoint_completion_target
name | checkpoint_segments
name | checkpoint_timeout
name | checkpoint_warning
name | client_encoding
name | client_min_messages
name | commit_delay
name | commit_siblings
name | config_file
name | constraint_exclusion
name | cpu_index_tuple_cost
name | cpu_operator_cost
name | cpu_tuple_cost
name | cursor_tuple_fraction
name | data_checksums
name | data_directory
name | DateStyle
name | db_user_namespace
name | deadlock_timeout
name | debug_assertions
name | debug_pretty_print
name | debug_print_parse
name | debug_print_plan
name | debug_print_rewritten
name | default_statistics_target
name | default_tablespace
name | default_text_search_config
name | default_transaction_deferrable
name | default_transaction_isolation
name | default_transaction_read_only
name | default_with_oids
name | dynamic_library_path
name | dynamic_shared_memory_type
name | effective_cache_size
name | effective_io_concurrency
name | enable_bitmapscan
name | enable_hashagg
name | enable_hashjoin
name | enable_indexonlyscan
name | enable_indexscan
name | enable_material
name | enable_mergejoin
name | enable_nestloop
name | enable_seqscan
name | enable_sort
name | enable_tidscan
name | escape_string_warning
name | event_source
name | exit_on_error
name | external_pid_file
name | extra_float_digits
name | from_collapse_limit
name | fsync
name | full_page_writes
name | geqo
name | geqo_effort
name | geqo_generations
name | geqo_pool_size
name | geqo_seed
name | geqo_selection_bias
name | geqo_threshold
name | gin_fuzzy_search_limit
name | hba_file
name | hot_standby
name | hot_standby_feedback
name | huge_pages
name | ident_file
name | ignore_checksum_failure
name | ignore_system_indexes
name | integer_datetimes
name | IntervalStyle
name | join_collapse_limit
name | krb_caseins_users
name | krb_server_keyfile
name | lc_collate
name | lc_ctype
name | lc_messages
name | lc_monetary
name | lc_numeric
name | lc_time
name | listen_addresses
name | lo_compat_privileges
name | local_preload_libraries
name | lock_timeout
name | log_autovacuum_min_duration
name | log_checkpoints
name | log_connections
name | log_destination
name | log_directory
name | log_disconnections
name | log_duration
name | log_error_verbosity
name | log_executor_stats
name | log_file_mode
name | log_filename
name | log_hostname
name | log_line_prefix
name | log_lock_waits
name | log_min_duration_statement
name | log_min_error_statement
name | log_min_messages
name | log_parser_stats
name | log_planner_stats
name | log_rotation_age
name | log_rotation_size
name | log_statement
name | log_statement_stats
name | log_temp_files
name | log_timezone
name | log_truncate_on_rotation
name | logging_collector
name | maintenance_work_mem
name | max_connections
name | max_files_per_process
name | max_function_args
name | max_identifier_length
name | max_index_keys
name | max_locks_per_transaction
name | max_pred_locks_per_transaction
name | max_prepared_transactions
name | max_replication_slots
name | max_stack_depth
name | max_standby_archive_delay
name | max_standby_streaming_delay
name | max_wal_senders
name | max_worker_processes
name | password_encryption
name | port
name | post_auth_delay
name | pre_auth_delay
name | quote_all_identifiers
name | random_page_cost
name | restart_after_crash
name | search_path
name | segment_size
name | seq_page_cost
name | server_encoding
name | server_version
name | server_version_num
name | session_preload_libraries
name | session_replication_role
name | shared_buffers
name | shared_preload_libraries
name | sql_inheritance
name | ssl
name | ssl_ca_file
name | ssl_cert_file
name | ssl_ciphers
name | ssl_crl_file
name | ssl_ecdh_curve
name | ssl_key_file
name | ssl_prefer_server_ciphers
name | ssl_renegotiation_limit
name | standard_conforming_strings
name | statement_timeout
name | stats_temp_directory
name | superuser_reserved_connections
name | synchronize_seqscans
name | synchronous_commit
name | synchronous_standby_names
name | syslog_facility
name | syslog_ident
name | tcp_keepalives_count
name | tcp_keepalives_idle
name | tcp_keepalives_interval
name | temp_buffers
name | temp_file_limit
name | temp_tablespaces
name | TimeZone
name | timezone_abbreviations
name | trace_notify
name | trace_recovery_messages
name | trace_sort
name | track_activities
name | track_activity_query_size
name | track_counts
name | track_functions
name | track_io_timing
name | transaction_deferrable
name | transaction_isolation
name | transaction_read_only
name | transform_null_equals
name | unix_socket_directories
name | unix_socket_group
name | unix_socket_permissions
name | update_process_title
name | vacuum_cost_delay
name | vacuum_cost_limit
name | vacuum_cost_page_dirty
name | vacuum_cost_page_hit
name | vacuum_cost_page_miss
name | vacuum_defer_cleanup_age
name | vacuum_freeze_min_age
name | vacuum_freeze_table_age
name | vacuum_multixact_freeze_min_age
name | vacuum_multixact_freeze_table_age
name | wal_block_size
name | wal_buffers
name | wal_keep_segments
name | wal_level
name | wal_log_hints
name | wal_receiver_status_interval
name | wal_receiver_timeout
name | wal_segment_size
name | wal_sender_timeout
name | wal_sync_method
name | wal_writer_delay
name | work_mem
name | xmlbinary
name | xmloption
name | zero_damaged_pages

Share Button

PostgreSQL 9.4 – System Tables – Get Database/Table number, size and other general info for your database

Get information about your existing databases

select * from pg_database;

Show uptime of Postgres server (Not uptime of OS)

select date_trunc ('second', current_timestamp - pg_postmaster_start_time());

View how much space a database consumes

select pg_size_pretty(pg_database_size(current_database()));

View the largest tables in your database

\x
select relname, relpages
from pg_class 
order by relpages desc;

view how large a table is

select pg_size_pretty(pg_relation_size('name_of_table'));

or

\dt+ name_of_table

view how many tables a user owns

select  count(*)  from pg_tables where tableowner = 'seeker';

View how many tables a schema of your database has.

select count(*) from information_schema.tables where table_schema not in ('pg_catalog', 'information_schema');

View how many databases a server has.

select count(*)  from pg_database;

View size of all databases

select sum(pg_database_size(datname)) from pg_database;

List all roles

select * from information_schema.enabled_roles;

List all schemas

select schema_name from information_schema.schemata;
Share Button