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

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.

Joshua D. Drake

>
> - --
> Greg Sabino Mullane greg(at)turnstep(dot)com
> End Point Corporation http://www.endpoint.com/
> PGP Key: 0x14964AC8 201005101331
> http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
> -----BEGIN PGP SIGNATURE-----
>
> iEYEAREDAAYFAkvoQ20ACgkQvJuQZxSWSsiaqQCgv6698Bo37q7cVuVngZJez11M
> 4nEAoOmYW8EFDbjBFtAR4qDZLmHRhNPa
> =NUq2
> -----END PGP SIGNATURE-----
>
>
>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering