Re: Dumping a database that is not accepting commands?

 version                         | PostgreSQL 9.1.9 on x86_64-unknown-freebsd9.1, compiled by gcc (GCC) 4.2.1 20070831 patched [FreeBSD], 64-bit

 autovacuum                      | on

 autovacuum_analyze_scale_factor | 0.1

 autovacuum_freeze_max_age       | 800000000

 autovacuum_max_workers          | 3

 autovacuum_vacuum_cost_delay    | 0

 autovacuum_vacuum_scale_factor  | 0.1

 checkpoint_segments             | 128

 effective_cache_size            | 12GB

 listen_addresses                | *

 log_autovacuum_min_duration     | 10s

 log_destination                 | stderr

 log_filename                    | logfile-%A.log

 log_line_prefix                 | %t:%u:%r:[%p]: 

 log_rotation_age                | 1d

 log_rotation_size               | 1GB

 log_truncate_on_rotation        | on

 logging_collector               | on

 maintenance_work_mem            | 10GB

 max_connections                 | 500

 max_stack_depth                 | 2MB

 random_page_cost                | 1

 seq_page_cost                   | 1

 shared_buffers                  | 128MB

 synchronous_commit              | off

 temp_buffers                    | 128MB

 TimeZone                        | US/Central

 vacuum_cost_limit               | 500

 wal_buffers                     | 32MB

 work_mem                        | 256MB

This is the tuning of the original database, anything changed from the default settings. The machine it was running on had 48GB of memory. The database was 36TB, with 2 tables taking up the bulk of that (about 14TB each), and about 10 other tables and a few large indexes making up the rest. Our typical usage pattern is mostly inserts, with a some hourly summaries (which take maybe 5 minutes), some daily summaries (which take about 20-40 minutes), and a couple of end of month queries that take several hours. We have the same setup and tuning in production, which is about the same size, with an additional end of month query that runs off one of the 14TB tables, which can take 4-7 days.