Set a statement timeout, to deal with clients that disappear while the query is running pg_stat_bgwriter: can tune the bgwriter, may need to raise from 4MB/sec throttling limit for big/active databases memory tuning: shared buffers, max connections private memory (per process/client): work mem, shared_buffers: old mnemonic of 0.25x RAM is obsolete. Now it's workload dependent. Start with a small value, tune with pg_buffercache work mem: 4mb is good maintenance work mem: default 64mb, going beyond 1gb likely not helpful. small can be good, better for L2/L3 cache effective io concurrency: normally 1, worth increasing for SSD/RAID systems log line prefix: add %t timestamp log min messages: NOTICE log min error statement: ERROR Where to log to: your choice log duration for long queries: 1000 log duration: set it ON log checkpoints: log stats of shared memory checkpoints log connections/disconnections: maybe useful log lock waits: considered a Wait if it's longer than the deadlock check time (which is every 1sec) log temp files: see behaviour for temp table/space activity (1024 == 1KiB) analysis tools, good for top-N reports * pgfouine * pgbadger * PQA most frequently run queries highest cumulative run times longest individual response times runtime stats: * track_activities (default on) * track_counts (default on) * track_io_timing (default off, should turn ON) pg_stat_activity: current queries pg-stat_database: amount of activity on DBs pg_stat_bgwriter: stats for writer/bg processes pg_stat_user_tables: usage stats for tables, logical stuff like indexes, tuples, scans, etc pg_statio_user_tables: same again but at IO layer pg_stat_user_indexes: usage stats for indexes, can see hot spots and cold spots (cold spots may be indices that you can drop, to remove UPDATE overhead) _statio_: same again but at IO track_functions: none|pl|all (not SQL functions, they're inline) sometimes it's hard to diagnose because it goes away before we get to looking. could be poor plan choice, etc. in these cases it can be useful to enable auto_explain, it logs plans to the server log. Gotta load the extension manually, so you need to restart the DB to enable it under the auto_explain. namespace, you can spec log_min_duration, to analyze it at the same time, to record timing, log_buffers (shared buffer hit ratio), pg_stat_statements loaded at cluster start (shared_preload_libraries) lots of stats available other tools to consider, very heavyweight: log_statement_stats log_parser_stats log_planner_stats log_executor_stats PLpgsql profiler dtrace pg_stat_statements pg_dump and pg_restore can run in parallel pg_dump to archive format is really good dump can convert to script if you want pure SQL, pg_restore can read both though pg_restore is useful for juggling dumps to restore, you can pick out what you want when rebuilding the old-master into a new slave, you need to set the timeline target in recovery.conf, otherwise it'll continue on its original timeline only, and wait forever for new WAL segments, when really they're being produced on the the new timeline pglogical: since 9.4, able to do a near-zero-downtime version upgrade