1\timing off
  2
  3-- ========================================================================== --
  4--                                 V e r s i o n
  5-- ========================================================================== --
  6
  7--\pset title 'PostgreSQL Version'
  8
  9-- version() returns a long human readable string, hence we split from others SELECTs eg.
 10-- PostgreSQL 12.3 (Debian 12.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
 11SELECT
 12  version(),
 13  current_setting('server_version') AS "server_version",
 14  current_setting('server_version_num') AS "server_version_num";
 15
 16-- ========================================================================== --
 17--                          S e r v e r   D e t a i l s
 18-- ========================================================================== --
 19
 20\echo
 21--\pset title 'PostgreSQL Server Details'
 22
 23SELECT
 24  pg_postmaster_start_time(),
 25  pg_conf_load_time(),
 26  current_setting('logging_collector') AS "logging_collector",
 27  current_setting('log_destination') AS "log_destination",
 28  -- not available in Postgres 9
 29  -- doesn't work because it still checks if pg_current_logfile() is valid and neither eval or execute seem to work around this
 30  -- CASE WHEN current_setting('server_version_num')::int > 100000 THEN pg_current_logfile() ELSE NULL END as pg_current_logfile
 31  pg_current_logfile()
 32  -- current_setting('log_directory') AS "log_directory",  -- log
 33  -- current_setting('log_filename') AS "log_filename",    -- postgresql-%Y-%m-%d_%H%M%S.log
 34  -- not available on Postgres 10
 35  --pg_jit_available()
 36;
 37
 38-- SELECT pg_reload_conf(), pg_rotate_logfile();
 39
 40-- pg_sleep(1.5)
 41-- pg_sleep_for('5 minutes')
 42-- pg_sleep_until('tomorrow 03:00')
 43
 44-- ======================================================
 45-- https://www.postgresql.org/docs/12/runtime-config.html
 46
 47-- set runtime config at system, database, or user level, or even user level for a specific database (eg. for an ETL user to set different memory settings for use in a given DB)
 48-- ALTER SYSTEM SET timezone = 'UTC';
 49-- ALTER DATABASE test SET timezone = 'UTC';
 50-- ALTER USER hari SET timezone = 'UTC';
 51-- ALTER USER hari IN DATABASE test SET timezone = 'UTC';
 52
 53-- session level:
 54-- SET myParam TO 'myValue';
 55-- UPDATE pg_settings SET setting = 'myValue' WHERE name = 'myParam';
 56
 57-- \x
 58-- SELECT * from pg_settings;
 59
 60-- SHOW ALL;
 61-- SHOW one_param;
 62
 63\echo
 64--\pset title 'Config Files'
 65
 66SELECT
 67  current_setting('config_file') AS "config_file",
 68  current_setting('hba_file') AS "hba_file",
 69  current_setting('ident_file') AS "ident_file";
 70
 71\echo
 72--\pset title 'PostgreSQL Data Directory & Unix Sockets'
 73
 74SELECT
 75  current_setting('data_directory') AS "data_directory",
 76  current_setting('unix_socket_directories') AS "unix_socket_directories",
 77  current_setting('unix_socket_permissions') AS "unix_socket_permissions",
 78  current_setting('unix_socket_group') AS "unix_socket_group";
 79
 80-- ========================================================================== --
 81--                   B u f f e r s   &   C o n n e c t i o n s
 82-- ========================================================================== --
 83
 84\echo
 85--\pset title 'Buffers & Connections'
 86
 87SELECT
 88  current_setting('shared_buffers') AS "shared_buffers",
 89  current_setting('work_mem') AS "work_mem",
 90  current_setting('max_connections') AS "max_connections",
 91  current_setting('max_files_per_process') AS "max_files_per_process", -- should be less than ulimit nofiles to avoid “Too many open files” failures
 92  current_setting('track_activities') AS "track_activities", -- for pg_stat / pg_statio family of system views that are used in many other adjacent scripts
 93  current_setting('track_counts') AS "track_counts", -- needed for the autovacuum daemon
 94  current_setting('password_encryption') AS "password_encryption";
 95
 96
 97-- ========================================================================== --
 98--                                    U s e r
 99-- ========================================================================== --
100
101\echo
102--\pset title 'Users, Sessions & Queries'
103
104-- in SQL the following have special syntax and should be called without parens: current_catalog, current_role, current_schema, current_user, session_user
105SELECT
106  current_user,  -- aka user, current_role - this is the effective user for permission checking
107  session_user,  -- connection user before superuser SET SESSION AUTHORIZATION
108  current_schema,
109  current_catalog, -- SQL standard, same as current_database()
110  pg_backend_pid(),
111  current_query();
112
113\echo
114--\pset title 'Schema search list'
115
116SELECT current_schemas(true) AS "current_schemas(true) - auto-searched schemas"; -- true to include implicit schemas eg. pg_catalog
117
118
119-- ========================================================================== --
120--                       B a c k u p   &   R e c o v e r y
121-- ========================================================================== --
122
123\echo
124--\pset title 'Backup & Recovery'
125
126SELECT
127  pg_is_in_backup(),
128  pg_is_in_recovery(),
129  pg_backup_start_time(),
130  'see postgres_recovery.sql for more info' AS "info";
131  -- use postgres_recovery.sql instead of having a big blank table distracting us here
132  -- the following recovery control functions can only be executed during recovery - to get just the above use postgres_funcs.sql
133  --( CASE WHEN pg_is_in_recovery() THEN pg_is_wal_replay_paused() END)       AS "pg_is_wal_replay_paused()",
134  --( CASE WHEN pg_is_in_recovery() THEN pg_last_wal_receive_lsn() END)       AS "pg_last_wal_receive_lsn()",
135  --( CASE WHEN pg_is_in_recovery() THEN pg_last_wal_replay_lsn() END)        AS "pg_last_wal_replay_lsn()",
136  --( CASE WHEN pg_is_in_recovery() THEN pg_last_xact_replay_timestamp() END) AS "pg_last_xact_replay_timestamp()"
137
138--SELECT
139--  pg_ls_logdir(),
140--  pg_ls_waldir(),
141--  pg_ls_archive_statusdir(),
142--  pg_ls_tmpdir();
143
144
145-- ========================================================================== --
146--                                 N e t w o r k
147-- ========================================================================== --
148
149\echo
150--\pset title 'Networking'
151
152SELECT
153  inet_client_addr(),
154  inet_client_addr(),
155  inet_server_addr(),
156  inet_server_port();
157
158-- causes 0 rows when mixed with other select funcs
159--SELECT pg_listening_channels();
160
161
162-- ========================================================================== --
163--                             D a t e   &   T i m e
164-- ========================================================================== --
165
166-- CURRENT_TIME and CURRENT_TIMESTAMP deliver values with time zone; LOCALTIME and LOCALTIMESTAMP deliver values without time zone.
167-- CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, and LOCALTIMESTAMP can optionally take a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field
168
169\echo
170--\pset title 'Date & Time'
171
172SELECT
173  -- current timestamps even inside transactions/functions
174  now(),
175  timeofday(),  -- human string timestamp with timezone
176
177  -- at start of current transaction for consistency, includes +offset timezone
178  current_timestamp(2) AS "current_timestamp(2)", -- secs precision of 2 decimal places, includes +offset timezone
179  current_date,
180  current_time(1) AS "current_time(1)", -- includes +offset timezone
181  localtime,
182  localtimestamp(1) AS "localtimestamp(1)",
183
184  -- provide current timestamps even inside transactions/functions
185  -- now()
186  -- timeofday(),  -- human string timestamp with timezone
187  clock_timestamp(), -- current date + time (changes throughout function)
188  statement_timestamp(),
189  transaction_timestamp()  -- same as CURRENT_TIMESTAMP
190;
191
192--\pset title