3 ms·
Multicore aware dump and restore would be a big win. I've hacked something up to do it in python queues and threads calling out to pg_dump. The drawback is that
by wiredfool 16y ago
Multicore aware dump and restore would be a big win. I've hacked something up to do it in python queues and threads calling out to pg_dump. The drawback is that when I do it, I'm not getting a consistent, one transaction dump. But then usually, I'm shutting down the system to do an upgrade when I thwack it like this, so it's a reasonable tradeoff. With a fast set of drives, I can generally dump and reload with a concurrency of roughly O(#cores) in approximately the time it takes for the largest table, rather than the sum of the times for all the tables.
(there's a caveat there, and that's that my biggest tables tend to be toasted, so there's a decent processor load uncompressing the toasted bits. Also, Power law governs table size, and the biggest table is ~ 50+ % of the database, with ~ 100 tables. YMMV)
I wish there was a way to transparently parallelize operations over more than one core. I've got enough operations that are actually processor bound that I could benefit (some) from it. I could partition the database, but that's a management headache.
- random42 16y agoAre you looking for something similar to flag -j, --jobs of http://www.postgresql.org/docs/8.4/static/app-pgrestore.html http://www.postgresql.org/docs/8.4/static/app-pgrestore.html, which implies the number of jobs running in parallel (which to my understanding depends on the number of CPU cores.) Granted, this only exists for pg_restore (8.4 onwards), no equivalent functionality in postgres for pg_dump.
- wiredfool 16y agoSomething like that. The nice thing there is that the index creation is done in parallel, which is not split well with my method. What I do is dump the schema, then split it between the table creation and the indexes. The first part gets loaded before the data, the second, after. Then all the tables and all the sequences are dumped, optionally teed to disk, and loaded in a psql process to the new location. I've been doing this since the 7.x series, so it way predates 8.4. (It also predates the in place upgrades, which is a big thing that they've added since I was coming up to speed)
- someotheridiot 16y agoI'm trying to do something similar to copy databases around. Another problem with doing the parallelism manually is that you have to worry about all the annoying dependencies between tables, constraints, functions etc. Arggghhh.
- teh 16y agoThere are patches for for parallel in 9.1 [1]. [1] https://commitfest.postgresql.org/action/patch_view?id=426 https://commitfest.postgresql.org/action/patch_view?id=426