Thread: How to do pg_dump + pg_restore within Perl script?
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?
Thanks!
~K
-----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:With one minor exception. I don't think he needs -Z9 since he is using
> -----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.
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
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