PostgreSQL Quick Health Check

So you have inherited a new PostgreSQL database. Lucky you! You need to quickly get an idea of its health and any urgent maintenance required. I generally ask the following questions for starters (You can download any of the shell and sql scripts used below at my downloads page).

1. What are the common settings

$ db_settings.sh 

Logging Parameters...
  log_destination = stderr
  logging_collector = on
  log_directory = pg_log
  log_filename = postgresql-%Y-%m-%d_%H%M%S.log
  log_rotation_age = 1d
  log_rotation_size = 100MB
  log_min_messages = notice
  log_min_error_statement = error
  log_min_duration_statement = 20s
  silent_mode = off
  log_duration = off
  log_line_prefix = %u - %d - %h - %t:
  log_lock_waits = on
  log_statement = none
  log_temp_files = 1GB

Memory Parameters...
  shared_buffers = 16GB
  effective_cache_size = 32GB
  wal_buffers = 1MB
  work_mem = 64MB
  maintenance_work_mem = 256MB

Vacuum Parameters...
  autovacuum = on

Backup Parameters...
  archive_mode = on
  archive_command = /var/lib/dba/db_archive_log.sh %p
  checkpoint_segments = 32
  checkpoint_timeout = 5min

Performance Parameters...
  fsync = on
  wal_sync_method = fdatasync
  seq_page_cost = 1
  random_page_cost = 2

Is the logging sufficient to pick up health/performance problems?
Do the memory parameters suit the server?
Is autovacuum on? If not is there a good reason?
Is archive_mode on? If not is there a good reason?
Make sure the performance parameters are sane.
If not running autovacuum, is there a risk of transaction wrapping?

$ psql staging -f tid
     datname      | datfrozenxid |    age    | txid_current
------------------+--------------+-----------+--------------
 metadata         |   2210689720 | 100191222 |   2310880942
 mystats          |   2210469233 | 100411709 |   2310880942
 moredata         |   2208464385 | 102416557 |   2310880942
 abbadaba         |   2208264458 | 102616484 |   2310880942
 bigass           |   2208103441 | 102777501 |   2310880942
 progress         |   2208103441 | 102777501 |   2310880942
 template1        |   2200098982 | 110781960 |   2310880942
 template_postgis |   2126818815 | 184062127 |   2310880942
 template0        |   2126818812 | 184062130 |   2310880942
 postgres         |   2120019978 | 190860964 |   2310880942
(10 rows)

2. How big are the databases

Get a feel for the data volumes involved

$ psql -f db_size
     datname      |     size     |  spcname   |                 spclocation
------------------+--------------+------------+----------------------------------------------
 bigass           | 446399467116 | largedata  | /datawarehouse/postgresql/8.3.5-1/data
 moredata         |  61644787308 | ts_rawdata | /datawarehouse/postgresql/8.3.5-1/ts_rawdata
 abbadaba         |  10602622572 | largedata  | /datawarehouse/postgresql/8.3.5-1/data
 progress         |   9576033900 | largedata  | /datawarehouse/postgresql/8.3.5-1/data
 metadata         |   3306540652 | largedata  | /datawarehouse/postgresql/8.3.5-1/data
 mystats          |   3184004716 | largedata  | /datawarehouse/postgresql/8.3.5-1/data
 template_postgis |      7856132 | largedata  | /datawarehouse/postgresql/8.3.5-1/data
 postgres         |      4714092 | pg_default |
 template0        |      4603908 | pg_default |
 template1        |      4562948 | pg_default |
(10 rows)

Is there enough disk space?
How are the schemas distributed?

$ psql -f schema_size bigass
      nspname       | est MB
--------------------+--------
 main_data          | 213883
 history            | 107391
 allstats           |  46843
 delta              |  22050
 working            |  12373
 pg_toast           |   1063
 statistics         |    694
 main_datagis       |     31
 pg_catalog         |     14
 etl                |      3
 public             |      2
 information_schema |      0
(12 rows)

What are the top 10 biggest objects?

$ psql staging -f top10
              relname              | est MB
-----------------------------------+--------
 big_ass_table                     |  24883
 sale_stat                         |  19533
 big_ass_table_pit                 |  16169
 sale_stat_ix_region_id            |  16125
 staging_update_transfer_attribute |  15477
 property_attribute                |  15110
 transfer_attribute                |  12664
 sale_stat_ix_stat_id              |  10489
 staging_update_property_attribute |   9727
 property_attribute_pkey           |   8769
(10 rows)

3. What’s running right now?

$ db_running.sh
   datname   | procpid | usename  |                     substring                      | waiting |       age       | client_addr | client_port
-------------+---------+----------+----------------------------------------------------+---------+-----------------+-------------+-------------
 db1         |    6599 | postgres | <IDLE> in transaction                              | f       | 00:00:01.230222 | 172.17.0.51 |       41238
 db1         |    8053 | postgres | <IDLE> in transaction                              | f       | 00:00:00.001006 | 172.17.0.53 |       11346
 db2         |    8830 | postgres | SELECT property_id FROM ( SELECT * FROM property.s | f       | 00:00:02.969822 | 172.17.0.22 |       16232
 db2         |   13684 | postgres | SELECT ss1.*, ss2.countx  FROM property.sales_summ | f       | 00:00:01.637497 | 172.17.0.21 |       49790
 db2         |   10994 | postgres | SELECT property_id FROM ( SELECT * FROM property.s | f       | 00:00:00.511403 | 172.17.0.53 |       37233

You can download any of the shell and sql scripts used above at my downloads page.