Try update to the latest release,I see there is a bug fix about pg_dump out of memroy in 9.2.2,from the release note http://www.postgresql.org/docs/devel/static/release-9-2-2.html:
- Work around unportable behavior of malloc(0) and realloc(NULL, 0) (Tom Lane) On platforms where these calls return NULL, some code mistakenly thought that meant out-of-memory. This is known to have broken pg_dump for databases containing no user-defined aggregates. There might be other cases as well. Jov blog: http:amutu.com/blog <http://amutu.com/blog> 2013/10/1 Sergey Klochkov <kloch...@iqbuzz.ru> > Hello All, > > While trying to backup a database of relatively modest size (160 Gb) I ran > into the following issue: > > When I run > $ pg_dump -f /path/to/mydb.dmp -C -Z 9 mydb > > File /path/to/mydb.dmp does not appear (yes, I've checked permissions and > so on). pg_dump just begins to consume memory until it eats up all > avaliable RAM (96 Gb total on server, >64 Gb available) and is killed by > the oom killer. > > According to pg_stat_activity, pg_dump runs the following query > > SELECT oid, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = lomowner) > AS rolname, lomacl FROM pg_largeobject_metadata > > until it is killed. > > strace shows that pg_dump is constantly reading a large amount of data > from a UNIX socket. I suspect that it is the result of the above query. > > There are >300000000 large objects in the database. Please don't ask me > why. > > I tried googling on this, and found mentions of pg_dump being killed by > oom killer, but I failed to find anything related to the huge large objects > number. > > Is there any method of working around this issue? > > Thanks in advance. > > OS: CentOS 6 > PostgreSQL version: 9.2.1 > 96 Gb RAM > > PostgreSQL configuration: > > listen_addresses = '*' # what IP address(es) to listen on; > port = 5432 # (change requires restart) > max_connections = 500 # (change requires restart) > shared_buffers = 16GB # min 128kB > temp_buffers = 64MB # min 800kB > work_mem = 512MB # min 64kB > maintenance_work_mem = 30000MB # min 1MB > checkpoint_segments = 70 # in logfile segments, min 1, 16MB > each > effective_cache_size = 50000MB > logging_collector = on # Enable capturing of stderr and > csvlog > log_directory = 'pg_log' # directory where log files are > written, > log_filename = 'postgresql-%a.log' # log file name pattern, > log_truncate_on_rotation = on # If on, an existing log file of > the > log_rotation_age = 1d # Automatic rotation of logfiles > will > log_rotation_size = 0 # Automatic rotation of logfiles > will > log_min_duration_statement = 5000 > log_line_prefix = '%t' # special values: > autovacuum = on # Enable autovacuum subprocess? > 'on' > log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions > and > autovacuum_max_workers = 5 # max number of autovacuum > subprocesses > autovacuum_naptime = 5s # time between autovacuum runs > autovacuum_vacuum_threshold = 25 # min number of row updates before > autovacuum_vacuum_scale_factor = 0.1 # fraction of table size before > vacuum > autovacuum_vacuum_cost_delay = 7ms # default vacuum cost delay for > autovacuum_vacuum_cost_limit = 1500 # default vacuum cost limit for > datestyle = 'iso, dmy' > lc_monetary = 'ru_RU.UTF-8' # locale for monetary > formatting > lc_numeric = 'ru_RU.UTF-8' # locale for number > formatting > lc_time = 'ru_RU.UTF-8' # locale for time > formatting > default_text_search_config = 'pg_catalog.russian' > > -- > Sergey Klochkov > kloch...@iqbuzz.ru > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/**mailpref/pgsql-admin<http://www.postgresql.org/mailpref/pgsql-admin> >
