Обсуждение: Memory usage and pg_dump

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

Memory usage and pg_dump

От
Patrick Welche
Дата:
% pg_dump trans > attempt.db

generates a nice little process

  PID USERNAME PRI NICE   SIZE   RES STATE      TIME   WCPU    CPU COMMAND
 2881 prlw1      2    0   512K 1248K select     0:02  2.88%  2.78% pg_dump

and provides a backup. I am about to change a schema however, and

% pg_dump -D trans > attempt.db

generates a monster:

Memory: 146M Act, 73M Inact, 3792K Wired, 940K Free, 107M Swp, 181M Swp free

  PID USERNAME PRI NICE   SIZE   RES STATE      TIME   WCPU    CPU COMMAND
 2949 postgres  62    0  2904K 2520K RUN        2:44 50.83% 50.83% postgres
 2948 prlw1      2    0   127M   77M select     1:31 46.97% 46.97% pg_dump

which then gets confused when SIZE reaches 128Mb: (odd as there is 256Mb phys
mem)

Backend sent B message without prior T

and so on, which then leads to a core dump:

#0  0x4808944e in appendPQExpBufferStr (str=0x806535c, data=0x0)
    at pqexpbuffer.c:265
#1  0x480858ae in PQexec (conn=0x8065200,
    query=0x8096400 "SELECT * FROM ONLY \"trans\"") at fe-exec.c:1244
#2  0x804a0cd in dumpClasses_dumpData (fout=0x8065000, oid=0x8078230 "22556",
    dctxv=0x8078200) at pg_dump.c:514
#3  0x80582fa in _PrintTocData (AH=0x8065000, te=0x8085e00, ropt=0x8095f00)
    at pg_backup_null.c:104
#4  0x8053851 in RestoreArchive (AHX=0x8065000, ropt=0x8095f00)
    at pg_backup_archiver.c:370
#5  0x804b15b in main (argc=3, argv=0xbfbfd498) at pg_dump.c:1155
#6  0x8049bd9 in ___start ()

which is essentially strlen(0) being unhappy.. (due to the inconsistent
messages leading to zero data?)

Anyway, any idea why the behaviour is so different, and how I might tinker
with the schema? (Presumably alter table add column is the way forward, but is
that really the same as dump, edit schema, create, reload data?)

Cheers,

Patrick

Re: Memory usage and pg_dump

От
Tom Lane
Дата:
Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> Anyway, any idea why the behaviour is so different, and how I might tinker
> with the schema?

-D does a SELECT not a COPY, so pg_dump has to buffer the whole contents
of a table when you do that.  Consider increasing your kernel limit on
process size.

            regards, tom lane