Re: QSoC proposal: Rewrite pg_dump and pg_restore

On 03/21/2014 09:28 AM, Robert Haas wrote:
> On Tue, Mar 18, 2014 at 8:41 PM, Alexandr <askellio(at)gmail(dot)com> wrote:
>> Rewrite (add) pg_dump and pg_restore utilities as libraries (.so, .dll &
>> .dylib)
>
> This strikes me as (1) pretty vague and (2) probably too hard for a
> summer project.
>
> I mean, getting the existing binaries to build libraries that you can
> call with some trivial interface that mimics the existing command-line
> functionality of pg_dump might be doable, but that's not all that
> interesting. What people are really going to want is a library with a
> sophisticated API that lets you do interesting things
> programmatically. But that's going to be hard. AFAIK, nobody's even
> tried to figure out what that API should look like. Even if we had
> that worked out, a non-trivial task, the pg_dump source code is a
> mess, so refactoring it to provide such an API is likely to be a job
> and a half.

... and still wouldn't solve one of the most frequently requested things
for pg_dump / pg_restore, which is the ability to use them *server-side*
over a regular PostgreSQL connection. It'd be useful progress toward
that, though.

Right now, we can't even get the PostgreSQL server to emit DDL for a
table, let alone do anything more sophisticated.

Here's how I think it needs to look:

- Design a useful API for pg_dump and pg_restore that is practical to
use for pg_dump and pg_restore's current tasks (fast database
dump/restore) and also useful for extracting specific objects
from the database. When designing, consider that we'll want to
expose this API or functions that use it over SQL later.

- Create a new "libpqdump" library.

- Implement the designed API in the new library, moving and
adjusting code from pg_dump / pg_restore where possible, writing
new code where not.

- Refactor (closer to rewrite) pg_dump and pg_restore to use libpqdump,
removing as much knowledge of the system catalogs etc as possible from
them.

- Make sure the result still performs OK

THEN, once that's settled in:

- Modify libpqdump to support compilation as a backend extension, with
use of the SPI for queries and use of syscaches or direct scans
where possible.

- Write a "pg_dump" extension that uses libpqdump in SPI mode
to expose its API over SQL, or at least uses it to provide SQL
functions to describe database objects. So you can dump a DB,
or a subset of it, over SQL.

After all, a "libpgdump" won't do much good for the large proportion of
PostgreSQL users who use Java/JDBC, who can't use a native library
(without hideous hacks with JNI). For the very large group who use libpq
via language-specific client interfaces like the Pg gem for Ruby,
psycopg2 for Python, DBD::Pg for Perl, etc, it'll require a lot of work
to wrap the API and maintain it. Wheras a server-side SQL-callable
interface would be useful and immediately usable for all of them.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services