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

Re: Suggestions needed about how to dump/restore a database

From: Jeff Frost <jeff(at)frostconsultingllc(dot)com>
To: Arnau <arnaulist(at)andromeiberica(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Suggestions needed about how to dump/restore a database
Date: 2006-12-21 17:00:00
Message-ID: Pine.LNX.4.64.0612210850490.15313@discord.home.frostconsultingllc.com (view raw or flat)
Thread:
Lists: pgsql-admin
On Thu, 21 Dec 2006, Arnau wrote:

>  And as result I got a file of 2.2GB. The improvement has been quite big but 
> still very far from the 10-15 minutes that Jeff says or the Thomas'3 minutes.

Just FYI, if you were reporting the DB size by the size of the compressed dump 
file, then that's not exactly accurate.  If your dump file is 2.2GB, I'd guess 
your DB size is tremendously larger.  Maybe it's more like 10GB.  What does 
the output of 'du -sh /var/lib/pgsql/data' show (you might have to substitute 
/var/lib/pgsql/data for wherever your PGDATA directory is located)?  That also 
assumes you only have one database in your system.  A better way is to install 
the dbsize contrib module and use it to determine db size.

>
>  The version I'm running is a 7.4.2 and the postgresql.conf parameters are 
> the following:
>

You should at least upgrade to 7.4.14.  The version you are running has some 
potentional data loss bugs in it.

For some reason I can't find this email thread in the archives, so if you've 
answered this already, please forgive me.

What's the disk subsytem look like?

> # - Memory -
>
> shared_buffers = 10000          # min 16, at least max_connections*2, 8KB 
> each
> sort_mem = 10240                # min 64, size in KB
> vacuum_mem = 81920              # min 1024, size in KB
>
> # - Free Space Map -
>
> max_fsm_pages = 40000           # min max_fsm_relations*16, 6 bytes each
> max_fsm_relations = 2000        # min 100, ~50 bytes each
>
>
> Any suggestions the even reduce more the dump period?
>
> Thank you very much.
>

-- 
Jeff Frost, Owner 	<jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 650-780-7908	FAX: 650-649-1954

In response to

pgsql-admin by date

Next:From: Joseph McClintockDate: 2006-12-21 17:43:23
Subject: Re: Very Very Slow Database Restore
Previous:From: ArnauDate: 2006-12-21 16:22:02
Subject: Re: Suggestions needed about how to dump/restore a database

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