> * A way to securely make a libpq connection from a bgworker without messing > with passwords etc. Generate one-time cookies, sometihng like that.
Why would you have the bgworker connect to the database via TCP instead of just doing whatever it wants to do directly?
pg_dump and pg_restore, mainly, for copying the initial database state.
PostgreSQL doesn't have SQL-level function equivalents, nor pg_get_tabledef() etc, and there's been strong opposition to adding anything of the sort when it's been raised before. We could read a dump in via pg_restore's text conversion and run the appropriate queries over the SPI, doing the query splitting, COPY parsing and loading, etc ourselves in a bgworker. It'd be ugly and duplicate a lot, but it'd work. However, it wouldn't be possible to do restores in parallel that way, and that's necessary to get good restore performance on big DBs. For that we'd also basically rewrite pg_restore's parallel functionality using a bgworker pool.
The alternative is a massive rewrite of pg_dump and pg_restore to allow them to be used as libraries, and let them use either libpq or the SPI for queries, presumably via some level of abstraction layer. As well as further abtraction for pipelining parallel work. Not very practical, and IIRC whenever library-ifing pg_dump and pg_restore has been discussed before it's been pretty firmly rejected.
Also, parallelism at apply time. There are two ways to do apply work in parallel - a pool of bgworkers that each use the SPI, or using regular backends managing async libpq connections. At this point I think Konstantin's approach, with a bgworker pool that processes a work queue, is probably better for this, and want to explore making that a re-usable extension for 9.5 and possibly a core part of 9.6 or 9.7.