[ADMIN] speedup pg_dumpall

[Michael Monnerie]

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?

[Kevin Grittner]

Post by Michael Monnerie
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

[Michael Monnerie]

Post by Kevin Grittner

Post by Michael Monnerie
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.

Post by Kevin Grittner
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?