Обсуждение: speedup pg_dumpall

On one database server, we have both MySQL und PostgreSQL running, and
via script make a fulldump. We see that MySQL is more than twice as fast
for that:

mysql 5.1: 100s (1.66 Min) 1110224519 Bytes, 10.58 MB/s
postgres 8.4: 133s (2.21 Min) 654205642 Bytes, 4.69 MB/s

The commands are:

1) mysqldump --single-transaction --flush-logs --master-data=2 --all-
databases --add-drop-table

2) pg_dumpall -c

What can I do to improve pg_dump performance?

--
mit freundlichen Grüssen,
Michael Monnerie, Ing. BSc

it-management Internet Services: Protéger
http://proteger.at [gesprochen: Prot-e-schee]
Tel: +43 660 / 415 6531

Michael Monnerie <lists.michael.monnerie@is.it-management.at> wrote:

> What can I do to improve pg_dump performance?

I can't help wondering why you're running it.  For routine backups
it is generally not your best option in PostgreSQL.  PITR or warm
standby are generally more useful for routine backups of any
database big enough to worry about run time.

For my purposes, pg_dump is mostly useful for dumping schema as SQL
statements or dumping data from individual tables.  Prior to the
availability of pg_upgrade our main use was for upgrades from one
major release to another, but I now see it as a pretty narrow niche
utility -- at least for my shop.

-Kevin

Am Donnerstag, 23. Februar 2012, 11:08:29 schrieb Kevin Grittner:
> > What can I do to improve pg_dump performance?
>
>
> I can't help wondering why you're running it.  For routine backups
> it is generally not your best option in PostgreSQL.  PITR or warm
> standby are generally more useful for routine backups of any
> database big enough to worry about run time.

We're a hoster, so usually each database belongs to a different
customer. Each customer can have a "get a dump of our database daily"
dump. Also, restoring a single db is simpler when you got a dump of it.

> For my purposes, pg_dump is mostly useful for dumping schema as SQL
> statements or dumping data from individual tables.  Prior to the
> availability of pg_upgrade our main use was for upgrades from one
> major release to another, but I now see it as a pretty narrow niche
> utility -- at least for my shop.

Agreed, for a classical db server this could be better. We need the
per-db dumps, so pg_dump performance tuning would be nice. It's quite
slow currently. Someone got hints what to do against that?

--
mit freundlichen Grüssen,
Michael Monnerie, Ing. BSc

it-management Internet Services: Protéger
http://proteger.at [gesprochen: Prot-e-schee]
Tel: +43 660 / 415 6531