Question about memory usage of pg_dump

From: Condor <condor(at)stz-bg(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Question about memory usage of pg_dump
Date: 2017-10-09 19:36:14
Message-ID: 20919b2d4c86b6010212817188c92b58@stz-bg.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Hello,

I have a question .. okay I know it's a dump but need to ask it because
probably I will need to tell of five of my collective bad things... :)

My version is PostgreSQL 9.6.5 on x86_64-slackware-linux-gnu, compiled
by x86_64-slackware-linux-gcc (GCC) 7.2.0, 64-bit on server IP 10.1.1.3
(the ip of the server is connected with question)
I do a simple backup for few small tables (9M rows each) with bash shell
script:

for table in table1 table2 table3
do
pg_dump -U postgres --no-tablespaces --no-owner -a -b -t $table
my_db -h 10.1.1.2 | psql -U data -h 10.1.1.1 my_local_db
done

and I see after done of each table how many rows is copied. All of them,
but my collective trying to convince me,
they use this way to backup few tables on other project and because
these tables contain much data (20 - 30M rows as they explain)
pg_dump took too much memory and process was fail because out of memory,
so they rewrite every things on php.

I think that is a bullshit, they probably smoke something because I
think with php they add one more level over the whole process because
they do:

system command to dump the table like mine in shell and send data to
backup server
read the output
explode output to array
reach end of array with foreach that contain how many rows are copied.

but with my shell script I do only first line:
system command to dump the table like mine in shell and send data to
backup server

After a short introduction my question is:

How much memory take pg_dump and from witch sever ? 10.1.1.3 and / or
10.1.1.2. Lets say our data is 100 MB.
I know the dump process lock the table on the server when is read and
this take memory on server, after that : here I need explanation what is
happened with few words like: server 10.1.1.2 take 100MB into memory
then start send rows to server 10.1.1.2 that hold them all 100MB or
start resend data that receive immediately after receive it to 10.1.1.1
:

Thanks and sorry of dumb question, but I really need to know what is
happened on this process to can I tell them: My solution is okay and
work like a charm.

Cheers,
Hristo S.

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2017-10-09 20:01:14 Re: startup process stuck in recovery
Previous Message Christophe Pettus 2017-10-09 19:21:34 Re: startup process stuck in recovery