Skip site navigation (1) Skip section navigation (2)

Re: pg_dump and pg_restore

From: Jayadevan M <Jayadevan(dot)Maymala(at)ibsplc(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: pg_dump and pg_restore
Date: 2010-05-24 03:45:22
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
I increased shared_buffers and maintenance_work_memto
128MB and 64MB and the restore was over in about 20 minutes. Anyway, I am 
learning about PostgreSQL and it is not a critical situation. Thanks for 
all the replies.

From:   Robert Haas <robertmhaas(at)gmail(dot)com>
To:     Jayadevan M <Jayadevan(dot)Maymala(at)ibsplc(dot)com>
Cc:     pgsql-performance(at)postgresql(dot)org
Date:   22/05/2010 16:59
Subject:        Re: [PERFORM] pg_dump and pg_restore

On Mon, May 17, 2010 at 1:04 AM, Jayadevan M
<Jayadevan(dot)Maymala(at)ibsplc(dot)com> wrote:
> Hello all,
> I was testing how much time a pg_dump backup would take to get restored.
> Initially, I tried it with psql (on a backup taken with pg_dumpall). It 
> me about one hour. I felt that I should target for a recovery time of 15
> minutes to half an hour. So I went through the blogs/documentation etc 
> switched to pg_dump and pg_restore. I tested only the database with the
> maximum volume of data (about 1.5 GB). With
> pg_restore -U postgres -v -d PROFICIENT --clean -Fc proficient.dmp
> it took about 45 minutes. I tried it with
> pg_restore -U postgres -j8 -v -d PROFICIENT --clean -Fc proficient.dmp
> Not much improvement there either. Have I missed something or 1.5 GB 
data on
> a machine with the following configuration will take about 45 minutes? 
> is nothing else running on the machine consuming memory or CPU. Out of 
> odd tables, about 10 tables have millions of records, rest are all 
having a
> few thousand records at most.
> Here are the specs  ( a pc class  machine)-
> PostgreSQL 8.4.3 on i686-pc-linux-gnu
> CentOS release 5.2
> Intel(R) Pentium(R) D CPU 2.80GHz
> 2 GB RAM
> Storage is local disk.
> Postgresql parameters (what I felt are relevant) -
> max_connections = 100
> shared_buffers = 64MB
> work_mem = 16MB
> maintenance_work_mem = 16MB
> synchronous_commit on

I would suggest raising shared_buffers to perhaps 512MB and cranking
up checkpoint_segments to 10 or more.  Also, your email doesn't give
too much information about how many CPUs you have and what kind of
disk subsystem you are using (RAID?  how many disks?) so it's had to
say if -j8 is reasonable.  That might be too high.

Another thing I would recommend is that during the restore you use
tools like top and iostat to monitor the system.  You'll want to check
things like whether all the CPUs are in use, and how the disk activity
compares to the maximum you can generate using some other method
(perhaps dd).

One thing I've noticed (to my chagrin) is that if pg_restore is given
a set of options that are incompatible with parallel restore, it just
does a single-threaded restore.  The options you've specified look
right to me, but, again, examining exactly what is going on during the
restore should tell you if there's a problem in this area.

Robert Haas
The Enterprise Postgres Company


"The information in this e-mail and any attachment is intended only for 
the person to whom it is addressed and may contain confidential and/or 
privileged material. If you have received this e-mail in error, kindly 
contact the sender and destroy all copies of the original communication. 
IBS makes no warranty, express or implied, nor guarantees the accuracy, 
adequacy or completeness of the information contained in this email or any 
attachment and is not liable for any errors, defects, omissions, viruses 
or for resultant loss or damage, if any, direct or indirect."

In response to

pgsql-performance by date

Next:From: Konrad GarusDate: 2010-05-24 11:25:57
Subject: Re: shared_buffers advice
Previous:From: David JarvisDate: 2010-05-23 22:55:21
Subject: Re: Optimize date query for large child tables: GiST or GIN?

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group