Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    17

    Unanswered: Using pg_dump from Perl script

    Hey Folks,


    I've been pulling my hair out over getting pg_dump to work from a Perl script. I have two main problems:

    I can't seem to get pg_dump to recognize the PGPASSWORD environment variable if I set it like this:

    Code:
    qx(PGPASSWORD=mypassword);
    qx(export PGPASSWORD);
    Also I want to run a command against the database from the psql shell to list the databases. Here's the command I'm trying but even when I manually type the password I get a syntax error (some kind of problem with the \l)

    Code:
    /usr/local/pgsql/bin/psql -U postgres -c \\l -d template1
    Do any of you guys have advice? I want to avoid using pg_dumpall since with looping through pg_dump I can more easily make a separate archive file for each database in the cluster. Thanks!


    -Cliff

  2. #2
    Join Date
    Sep 2003
    Posts
    2

    Re: Using pg_dump from Perl script

    Originally posted by cliffyman
    I can't seem to get pg_dump to recognize the PGPASSWORD environment variable if I set it like this:
    Code:
    qx(PGPASSWORD=mypassword);
    qx(export PGPASSWORD);

    You are making an assumption that is false. Your code will spawn an external shell, set the variable, then the shell exits and the variable goes away. This is a fundamental working of *nix, and has nothing to do with perl. Additionally, qx is for capturing the output of a command. If you just want to run something you should use system().
    What you really want to do is this:
    Code:
    $ENV{PGPASSWORD}=mypassword;
    Then calling something after that will be able to see the env var. Run perldoc perlvar and search for "ENV" for more information.
    To test it do something like this after that statement:
    Code:
    system('echo $PGPASSWORD');
    Also I want to run a command against the database from the psql shell to list the databases. Here's the command I'm trying but even when I manually type the password I get a syntax error (some kind of problem with the \l)
    Code:
    /usr/local/pgsql/bin/psql -U postgres -c \\l -d template1
    Since you didn't post the complete code to this I can't say. Running from a shell that will work fine. If you are running it from inside perl the type of quotes you are using is important. I recommend using this structure:
    Code:
    $output = qx'/usr/local/pgsql/bin/psql -U postgres -c \l -d template1'
    Note: those are forward single quotes as the delimiter. See perldoc perlop and search for "qx" for more info. Note the column on "interpolation"

  3. #3
    Join Date
    Aug 2003
    Posts
    17
    Wow, thanks for all that information! I really appreciate it. I'll give that code a try tonight or tomorrow and let you know what ended up working. Thanks again!


    -Cliff

  4. #4
    Join Date
    Aug 2003
    Posts
    17
    I got it working! Here is the script I'm using.. I'm a pretty awful Perl scripter but it seems to get the job done...

    Code:
    #!/usr/bin/perl
    #
    # cron script to create backups each night
    
    use strict;
    umask(0022);
    
    
    # GLOBAL CONFIG START
    
    # naming and location of files
    my $target = "/home/ftp";
    my $name_date = qx(/bin/date +%Y-%m-%d); chomp $name_date;
    $target .= "/".$name_date;
    
    # store host name
    my $host = qx("hostname"); chomp $host;
    my @hosts = split(/\./, $host);
    my $name_host = $hosts[0];
    
    # GLOBAL CONFIG END
    
    
    
    # SCRIPT START
    
    # set the environment password
    $ENV{PGPASSWORD}='mypassword';
    
    # connect to the db and capture dbnames
    my @dbs = qx'/usr/local/pgsql/bin/psql -U postgres -d template1 -c \\\l';
    
    # trim off junk rows
    shift @dbs; shift @dbs; shift @dbs; pop @dbs; pop @dbs;
    
    foreach my $db (@dbs)
    {
            # extract just the dbname
            my @string = split(/\|/, $db);
            my $dbname = $string[0];
            # remove all whitespace
            $dbname =~ s/\s//g;
    
            my $time = time();
            print localtime() ." - pgsql database ".$dbname." - started\n";
            my $test = $target."/".$name_date."-".$name_host."-pgsql-".$dbname.".sql";
            !system("/usr/local/pgsql/bin/pg_dump -U postgres $dbname | /bin/gzip > $test.gz");
            print localtime() ." - pgsql database ".$dbname." - finished - ".(time() - $time)."s\n";
    }
    
    # SCRIPT END

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •