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

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