Обсуждение: Out of memory errors on pg_dump

Поиск
Список
Период
Сортировка

Out of memory errors on pg_dump

От
Chander Ganesan
Дата:
I'm trying to dump a table in PostgreSQL , it's PostGIS data, and is
quite wide...I've checked the ulimit settings and verified they are
unlimited for memory related settings.

I get this whenever I try to do a pg_dump.  Curiously, if I *decrease*
shared buffers waaaay down, I find that these errors go away. If I
increase shared_buffers I get a larger number of tables that fail this
dump (other tables.) "test":

TopMemoryContext: 172388 total in 13 blocks; 5408 free (16 chunks);
166980 used
   CFuncHash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used
   Type information cache: 8192 total in 1 blocks; 1800 free (0 chunks);
6392 used
   Operator class cache: 8192 total in 1 blocks; 3848 free (0 chunks);
4344 used
   Operator lookup cache: 24576 total in 2 blocks; 14072 free (6
chunks); 10504 used
   TopTransactionContext: 8192 total in 1 blocks; 7760 free (1 chunks);
432 used
   MessageContext: 8192 total in 1 blocks; 5256 free (0 chunks); 2936 used
   smgr relation table: 8192 total in 1 blocks; 1776 free (0 chunks);
6416 used
   TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0
chunks); 16 used
   Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
   PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used
     PortalHeapMemory: 134298676 total in 6 blocks; 11400 free (14
chunks); 134287276 used
       COPY TO: 354370832 total in 7 blocks; 32552 free (25 chunks);
354338280 used
   Relcache by OID: 8192 total in 1 blocks; 2856 free (0 chunks); 5336 used
   CacheMemoryContext: 667472 total in 20 blocks; 14704 free (1 chunks);
652768 used
     pg_toast_27020980_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
     pg_description_o_c_o_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
     pg_constraint_contypid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_constraint_conname_nsp_index: 1024 total in 1 blocks; 280 free
(0 chunks); 744 used
     pg_attrdef_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
     pg_cast_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
     pg_rewrite_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
     pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
     pg_constraint_conrelid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
     pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
     pg_depend_reference_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
     pg_depend_depender_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
     pg_tablespace_spcname_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_tablespace_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_roles: 7168 total in 3 blocks; 2824 free (0 chunks); 4344 used
     pg_index_indrelid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
     pg_ts_dict_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
     pg_aggregate_fnoid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_language_name_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_statistic_relid_att_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
     pg_ts_dict_dictname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
     pg_namespace_nspname_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
     pg_opfamily_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
     pg_opclass_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
     pg_ts_parser_prsname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
     pg_amop_fam_strat_index: 1024 total in 1 blocks; 88 free (0
chunks); 936 used
     pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
     pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 240 free
(0 chunks); 784 used
     pg_cast_source_target_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
     pg_auth_members_role_member_index: 1024 total in 1 blocks; 280 free
(0 chunks); 744 used
     pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 240 free
(0 chunks); 784 used
     pg_ts_config_cfgname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
     pg_authid_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
     pg_ts_config_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_conversion_default_index: 1024 total in 1 blocks; 128 free (0
chunks); 896 used
     pg_language_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
     pg_enum_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
     pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
     pg_ts_parser_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_database_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
     pg_conversion_name_nsp_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
     pg_class_relname_nsp_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
     pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 280 free
(0 chunks); 744 used
     pg_class_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
     pg_amproc_fam_proc_index: 1024 total in 1 blocks; 88 free (0
chunks); 936 used
     pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 88 free (0
chunks); 936 used
     pg_index_indexrelid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
     pg_type_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
     pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
     pg_authid_rolname_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
     pg_auth_members_member_role_index: 1024 total in 1 blocks; 280 free
(0 chunks); 744 used
     pg_enum_typid_label_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
     pg_constraint_oid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
     pg_conversion_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_ts_template_tmplname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
     pg_ts_config_map_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
     pg_namespace_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_type_typname_nsp_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
     pg_operator_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
     pg_amop_opr_fam_index: 1024 total in 1 blocks; 240 free (0 chunks);
784 used
     pg_proc_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
     pg_opfamily_am_name_nsp_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
     pg_ts_template_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
   MdSmgr: 8192 total in 1 blocks; 6352 free (0 chunks); 1840 used
   LOCALLOCK hash: 24576 total in 2 blocks; 16168 free (4 chunks); 8408 used
   Timezones: 48616 total in 2 blocks; 5968 free (0 chunks); 42648 used
   ErrorContext: 8192 total in 1 blocks; 8176 free (3 chunks); 16 used
4fd8b71e.7502.1@2012-06-13 11:52:20 EDT - ERROR:  out of memory
4fd8b71e.7502.2@2012-06-13 11:52:20 EDT - DETAIL:  Failed on request of
size 268435456.
4fd8b71e.7502.3@2012-06-13 11:52:20 EDT - STATEMENT:  COPY
public.shapefile_19296 (gid, clsfinal, shape_leng, shape_area, the_geom,
web_geometry) TO stdout;



Thanks!

Re: Out of memory errors on pg_dump

От
Tom Lane
Дата:
Chander Ganesan <chander.ganesan@gmail.com> writes:
> I get this whenever I try to do a pg_dump.  Curiously, if I *decrease*
> shared buffers waaaay down, I find that these errors go away.

32-bit machine no doubt?  There's a limit to how big a datum can be
slung around in a 32-bit address space, and it's not very many tens of
megabytes if you've got shared_buffers eating a large chunk of that
address space.

            regards, tom lane