Re: How to do pg_dump + pg_restore within Perl script?

On Mon, May 10, 2010 at 2:59 PM, Joshua D. Drake <jd(at)commandprompt(dot)com>wrote:

> On Mon, 2010-05-10 at 17:33 +0000, Greg Sabino Mullane wrote:
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: RIPEMD160
> >
> >
> > > I would like to replicate the following Unix pipe within a Perl script,
> > > perhaps using DBD::Pg:
> > >
> > >
> > > % pg_dump -Z9 -Fc -U <DB_USER> <FROM_DB> | pg_restore -v -d <TO_DB> -p
> > > <SSH_TUNNEL_PORT> -h localhost -U <DB_USER>
> > >
> > > Of course, I can try to use Perl's system, and the like, to run this
> pipe
> > > verbatim, but I this as a last-resort approach.
> > >
> > > Is there a more direct way?
> >
> > If you mean emulate the pg_dump, no. pg_dump does a *lot* of stuff behind
> > the scenes, and trying to rewrite all that in Perl would be madness. And
> I
> > say that having written some serious madness into DBD::Pg already :).
> Stick
> > with the shell script, even if it means calling system.
> >
> > If you simply want to avoid the pipes, you can think about calling
> pg_dump
> > from the remote box, using a authorized_keys with a specific command in
> it,
> > and other tricks, but nothing will be as straightforward and error proof
> > as the line you gave, I suspect.
>
> With one minor exception. I don't think he needs -Z9 since he is using
> SSH which will compress anyway.
>

Actually, that was a mistake on my part. That should have been "-Ft" rather
than "-Z9 -Fc", since I *don't* want compression (most of the data being
transmitted consists of highly incompressible blobs anyway). Regarding SSH,
my understanding is that to get compression one needs to pass to it the -C
flag at the time of creating the tunnel. But my grasp of these details is
tenuous as best.

~K