He’s a list of handy admin queries for PostgreSQL
List all tables in the current database
SELECT tablename FROM pg_tables
WHERE schemaname = 'public'
;Example…
tablename
-------------------------
actual_generation
market_notice
rooftop_pv_actual
rooftop_pv_file_log
dispatch_price
market_notice_metadata
stock_prices
auction_units
arduino_weather_log
duid_metadata
dispatch_scada
bom_weather_metrics
dispatch_scada_log_file
bom_stations_metadata
(14 rows)Show details of table structure (columns, types)
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'insert_table_name'
;Example…
column_name | data_type | is_nullable
-------------------+--------------------------+-------------
interval_datetime | timestamp with time zone | NO
mwh_reading | numeric | YES
last_changed | timestamp with time zone | YES
inserted_at | timestamp with time zone | YES
duid | text | NO
source_file | text | YES
(6 rows)
Show active DB network connections
SELECT pid, usename, datname, client_addr, state
FROM pg_stat_activity
;Example…
pid | usename | datname | client_addr | state
------+----------+----------+-------------+--------
871 | | | |
873 | postgres | | |
4067 | postgres | postgres | 10.0.0.6 | idle
2612 | postgres | nem_data | 10.0.0.6 | idle
4068 | postgres | nem_data | 10.0.0.6 | idle
4335 | postgres | postgres | 10.0.0.6 | idle
4419 | postgres | postgres | 10.0.0.6 | idle
4431 | postgres | nem_data | 10.0.0.6 | idle
4901 | postgres | postgres | 10.0.0.6 | idle
5100 | postgres | nem_data | ::1 | active
843 | | | |
872 | | | |
842 | | | |
870 | | | |
(14 rows)
Show all databases
SELECT datname
FROM pg_database
WHERE datistemplate = false
;Example…
datname
----------
postgres
nem_data
(2 rows)
Show all tables and their estimated rows
SELECT relname, reltuples::bigint AS estimated_rows
FROM pg_catalog.pg_class
WHERE relkind = 'r'
-- note: 'r' = ordinary table
; Example…
relname | estimated_rows
--------------------------+----------------
actual_generation | 4133639
market_notice | 1245
rooftop_pv_actual | 184700
rooftop_pv_file_log | 1819
pg_statistic | 535
pg_type | 611
dispatch_price | 549460
market_notice_metadata | -1
pg_foreign_table | 0
stock_prices | 72204
auction_units | 1362
arduino_weather_log | 1069
pg_authid | 13
duid_metadata | 538
dispatch_scada | 32474742
pg_statistic_ext_data | 0
bom_weather_metrics | 51027
dispatch_scada_log_file | 3655
bom_stations_metadata | 721
pg_user_mapping | 0
pg_subscription | 0
pg_attribute | 3302
pg_proc | 3244
pg_class | 468
pg_attrdef | 0
pg_constraint | 111
pg_inherits | 0
pg_index | 183
pg_operator | 799
pg_opfamily | 146
pg_opclass | 177
pg_am | 7
pg_amop | 945
pg_amproc | 696
pg_language | 4
pg_largeobject_metadata | 0
pg_aggregate | 148
pg_statistic_ext | 0
pg_rewrite | 142
pg_trigger | 0
pg_event_trigger | 0
pg_description | 5136
pg_cast | 229
pg_enum | 0
pg_namespace | 4
pg_conversion | 128
pg_depend | 1913
pg_database | 1
pg_db_role_setting | 0
pg_tablespace | 2
pg_auth_members | 3
pg_shdepend | 0
pg_shdescription | 1
pg_ts_config | 29
pg_ts_config_map | 551
pg_ts_dict | 29
pg_ts_parser | 1
pg_ts_template | 5
pg_extension | 1
pg_foreign_data_wrapper | 0
pg_foreign_server | 0
pg_policy | 0
pg_replication_origin | 0
pg_default_acl | 0
pg_init_privs | 213
pg_seclabel | 0
pg_shseclabel | 0
pg_collation | 813
pg_parameter_acl | 0
pg_partitioned_table | 0
pg_range | 6
pg_transform | 0
pg_sequence | 0
pg_publication | 0
pg_publication_namespace | 0
pg_publication_rel | 0
pg_subscription_rel | 0
pg_largeobject | 0
sql_features | 714
sql_implementation_info | 12
sql_parts | 10
sql_sizing | 23
(82 rows)
Estimate row count and table size
SELECT relname AS table_name,
n_live_tup AS estimated_rows,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE relname = 'your_table_name'
;Example…
table_name | estimated_rows | total_size
----------------+----------------+------------
dispatch_scada | 40584 | 5746 MB
(1 row)