Thread: 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
Attachment
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