How to do pg_dump + pg_restore within Perl script?

-----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.

- --
Greg Sabino Mullane greg@turnstep.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-----

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@turnstep.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

On Mon, May 10, 2010 at 1:33 PM, Greg Sabino Mullane <greg@turnstep.com>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 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...

I can work with pg_dump, I think. What I'm trying to avoid is the
SSH-tunneling, which I find too fragile for reliable automated operation.

My script can use DBI::connect to provide a password when connecting to the
remote host, so I can run regular SQL on the remote host via Perl DBI
without SSH-tunneling.

But I have not found a way for my script to provide a password when it
runs commands like dropdb, createdb, and pg_restore with the "-h <REMOTE
HOST>" flag. So I end up resorting to SSH-tunneling. This is what I'm
trying to avoid.

Your idea of having the remote host run the pg_dump is worth looking into,
although I'm reluctant because involving the remote host like this would
significantly complicate my whole set up.

Anyway, thanks!

~K

On Mon, May 10, 2010 at 2:59 PM, Joshua D. Drake <jd@commandprompt.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

Kynn Jones <kynnjo@gmail.com> writes:

But I have not found a way for my script to provide a password when it
runs commands like dropdb, createdb, and pg_restore with the "-h <REMOTE
HOST>" flag. So I end up resorting to SSH-tunneling. This is what I'm
trying to avoid.

You don't really want to embed a password in the script anyway.
Consider using a ~/.pgpass file, or look at non-password-based
authentication mechanisms.

regards, tom lane

Kynn Jones <kynnjo@gmail.com> writes:

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.

Actually, I'd suggest -Fc -Z0, or maybe plain text dump, if your
motivation is to avoid compression. -Ft has its own issues that
make it a less-than-desirable choice; you shouldn't pick it unless
you really specifically need a tar-compatible dump format.

regards, tom lane

On 5/10/2010 2:46 PM, Kynn Jones wrote:

On Mon, May 10, 2010 at 1:33 PM, Greg Sabino Mullane <greg@turnstep.com
<mailto:greg@turnstep.com>> 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 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...

I can work with pg_dump, I think. What I'm trying to avoid is the
SSH-tunneling, which I find too fragile for reliable automated operation.

My script can use DBI::connect to provide a password when connecting to
the remote host, so I can run regular SQL on the remote host via Perl
DBI without SSH-tunneling.

But I have not found a way for my script to provide a password when it
runs commands like dropdb, createdb, and pg_restore with the "-h <REMOTE
HOST>" flag. So I end up resorting to SSH-tunneling. This is what I'm
trying to avoid.

Your idea of having the remote host run the pg_dump is worth looking
into, although I'm reluctant because involving the remote host like this
would significantly complicate my whole set up.

Anyway, thanks!

~K

Ah, this one I have hit too. I have very large database updates to send
to the web boxes... and I'd sometimes loose connection mid way.

I changed the process to dump to file, then rsync the file to the dest,
then remote exec the restore via ssh.

-Andy