Memory problem - Mailing list pgsql-general

Greetings,

Is it possible to recover data from a database if I can't even execute COPY
or pg_dump commands? For every try I get a "Memory exhausted in
AllocSetAlloc" error message and my psql closes. Is there a procedure to
recover from this error? My PostgreSQL is still 7.1.2 on RedHat 6.2.

I've included some information bellow (psql, strace and pg_ctl output).

Thanks in advance.

$ psql bxs
bxs=# COPY cham_chamada TO '/home/postgres/cham_chamada.dump' USING
DELIMITERS '|';
ERROR:  Memory exhausted in AllocSetAlloc(892678965)
bxs=#
bxs=#
bxs=# VACUUM VERBOSE ANALYZE cham_chamada;
NOTICE:  --Relation cham_chamada--
NOTICE:  Pages 2614: Changed 0, reaped 2614, Empty 0, New 0; Tup 142352: Vac
0, Keep/VTL 0/0, Crash 0, U
nUsed 24836, MinLen 88, MaxLen 152; Re-using: Free/Avail. Space
170752/96888; EndEmpty/Avail. Pages 0/88
2. CPU 1.35s/0.20u sec.
NOTICE:  Index xpkcham_chamada: Pages 2394; Tuples 142352: Deleted 0. CPU
1.60s/1.38u sec.
NOTICE:  Index xie1cham_chamada: Pages 1838; Tuples 142352: Deleted 0. CPU
0.99s/1.27u sec.
NOTICE:  Index xie2cham_chamada: Pages 1674; Tuples 142352: Deleted 0. CPU
0.71s/1.36u sec.
NOTICE:  Rel cham_chamada: Pages: 2614 --> 2614; Tuple(s) moved: 0. CPU
6.40s/5.36u sec.
NOTICE:  Analyzing...
pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!#



pg_ctl output :

$ pg_ctl start > /home/postgres/err.log 2>&1
$ tail -f /home/postgres/err.log
postmaster successfully started
DEBUG:  database system was shut down at 2003-04-07 10:48:30 BRT
DEBUG:  CheckPoint record at (1, 1577336212)
DEBUG:  Redo record at (1, 1577336212); Undo record at (0, 0); Shutdown TRUE
DEBUG:  NextTransactionId: 1101598; NextOid: 1701297
DEBUG:  database system is in production state
TopMemoryContext: 24600 total in 3 blocks; 19312 free (7 chunks); 5288 used
TopTransactionContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
DeferredTriggerXact: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
TransactionCommandContext: 24576 total in 2 blocks; 23128 free (16 chunks);
1448 used
QueryContext: 8192 total in 1 blocks; 7384 free (1 chunks); 808 used
DeferredTriggerSession: 8192 total in 1 blocks; 8176 free (0 chunks); 16
used
CacheMemoryContext: 516096 total in 6 blocks; 195520 free (9 chunks); 320576
used
pg_user: 5120 total in 5 blocks; 912 free (0 chunks); 4208 used
PortalMemory: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
MdSmgr: 8192 total in 1 blocks; 6120 free (0 chunks); 2072 used
DynaHash: 24576 total in 2 blocks; 9488 free (2 chunks); 15088 used
ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
ERROR:  Memory exhausted in AllocSetAlloc(892678965)
NOTICE:  --Relation cham_chamada--
NOTICE:  Pages 2614: Changed 0, reaped 2614, Empty 0, New 0; Tup 142352: Vac
0, Keep/VTL 0/0, Crash 0, UnUsed 24836, Min
Len 88, MaxLen 152; Re-using: Free/Avail. Space 170752/96888;
EndEmpty/Avail. Pages 0/882. CPU 1.35s/0.20u sec.
NOTICE:  Index xpkcham_chamada: Pages 2394; Tuples 142352: Deleted 0. CPU
1.60s/1.38u sec.
NOTICE:  Index xie1cham_chamada: Pages 1838; Tuples 142352: Deleted 0. CPU
0.99s/1.27u sec.
NOTICE:  Index xie2cham_chamada: Pages 1674; Tuples 142352: Deleted 0. CPU
0.71s/1.36u sec.
DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES
NOTICE:  Rel cham_chamada: Pages: 2614 --> 2614; Tuple(s) moved: 0. CPU
6.40s/5.36u sec.
NOTICE:  Analyzing...
Server process (pid 5113) exited with status 11 at Mon Apr  7 10:51:54 2003
Terminating any active server processes...
Server processes were terminated at Mon Apr  7 10:51:54 2003
Reinitializing shared memory and semaphores
DEBUG:  database system was interrupted at 2003-04-07 10:51:32 BRT
The Data Base System is starting up
DEBUG:  CheckPoint record at (1, 1577336212)
DEBUG:  Redo record at (1, 1577336212); Undo record at (0, 0); Shutdown TRUE
DEBUG:  NextTransactionId: 1101598; NextOid: 1701297
DEBUG:  database system was not properly shut down; automatic recovery in
progress...
DEBUG:  redo starts at (1, 1577336276)
DEBUG:  ReadRecord: record with zero len at (1, 1598949424)
DEBUG:  redo done at (1, 1598949388)
DEBUG:  database system is in production state



$ ps -ef | grep postg
postgres  5109     1  0 10:48 pts/1    00:00:00 /usr/bin/postmaster -D
/home/pos
postgres  5123  5012  0 10:55 pts/3    00:00:00 psql bxs
postgres  5111  4908  0 10:48 pts/1    00:00:00 tail -f
/home/postgres/err.log
postgres  5124  5109  8 10:55 pts/1    00:00:43 postgres: postgres bxs
[local] i

#strace -p 5124
...
write(34, "32148355|2002-10-11 09:40:18-03|"..., 4096) = 4096
write(34, "03|0|10|32148355|2002-10-11 10:2"..., 4096) = 4096
write(34, "261|0|7|\\N|\\N|\\N|\\N\n1|266|32773|"..., 4096) = 4096
write(34, "1|0|023644891120|1|0|0|61|0|261|"..., 4096) = 4096
write(34, "|32148355|2002-10-11 12:14:36-03"..., 4096) = 4096
write(34, "32:28-03|2|0|023|1|0|0|61|0|261|"..., 4096) = 4096
write(34, "2003-02-21 14:20:24-03|0|10|3214"..., 4096) = 4096
write(34, "03-02-21 14:00:06-03|8|0||0|0|0|"..., 4096) = 4096
write(34, "0|10|32148355|2002-10-11 13:18:1"..., 4096) = 4096
write(34, "0|61|0|6|0|266|\\N|\\N|\\N|\\N\n1|266"..., 4096) = 4096
write(34, "|\\N|\\N\n1|8|9|2002-10-11 13:49:55"..., 4096) = 4096
old_mmap(NULL, 892682240, PROT_READ|PROT_WRITE,
MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = -1 ENOMEM (Cannot allocate memory)
brk(0x3d57e000)                         = 0x822a000
old_mmap(NULL, 892682240, PROT_READ|PROT_WRITE,
MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = -1 ENOMEM (Cannot allocate memory)
brk(0x3d57e000)                         = 0x822a000
write(2, "TopMemoryContext: 24600 total in"..., 76) = 76
write(2, "TopTransactionContext: 8192 tota"..., 77) = 77
write(2, "DeferredTriggerXact: 8192 total "..., 75) = 75
write(2, "TransactionCommandContext: 24576"..., 85) = 85
write(2, "QueryContext: 8192 total in 1 bl"..., 69) = 69
write(2, "DeferredTriggerSession: 8192 tot"..., 78) = 78
write(2, "CacheMemoryContext: 516096 total"..., 82) = 82
write(2, "pg_user: 5120 total in 5 blocks;"..., 64) = 64
write(2, "PortalMemory: 8192 total in 1 bl"..., 68) = 68
write(2, "MdSmgr: 8192 total in 1 blocks; "..., 64) = 64
write(2, "DynaHash: 24576 total in 2 block"..., 68) = 68
write(2, "ErrorContext: 8192 total in 1 bl"..., 68) = 68
write(2, "ERROR:  Memory exhausted in Allo"..., 53) = 53
send(5, "EERROR:  Memory exhausted in All"..., 55, 0) = 55
rt_sigprocmask(SIG_SETMASK, ~[QUIT ILL TRAP ABRT BUS FPE KILL SEGV CONT STOP
UNUSED], NULL, 8) = 0
write(34, "|2|10|1468227393|2002-10-11 14:2"..., 2596) = 2596
close(34)                               = 0
munmap(0x40127000, 4096)                = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
send(5, "Z", 1, 0)                      = 1
recv(5,


José Vilson de Mello de Farias
Analista de Sistemas - APC

DÍGITRO TECNOLOGIA
E-mail: vilson.farias@digitro.com.br
Fone: (0xx48) 281-7158
Fax: (0xx48) 281-7000
Site: www.digitro.com.br