Re: Backing up several PostgreSQL databases

On 2011-02-05, Rolando Edwards <redwards@logicworks.net> wrote:

> I wrote a script a few months ago to pg_dump separate databases and archive=
>  them by date-named folder, except the template databases

this doesn't backup the users, (and possibly other global things excluded
from pg_dump output?)

A few months ago I wrote a perl script to burst pg_dumpall output into
separate files... it was my first ever perl program written from scratch
so I favoured understandability over style.

It's going to fail slightly if it hits a database containing no tables but
containig a funtion with a line that starts "COPY" (so don't do that)

#!/usr/bin/perl
#
# perl script to split a postgres cluster dump into database dump files
# also emits input on stdout, use redirection to supress if desired.
#
$o=0;

while( ( $a = <STDIN> ) && ! ( $a =~ /^-- Roles\n$/ ) ){
  $h[$o++] = "$a";
}

exit if (! $o);
open OH, ">!roles.sql";
print OH @h;
print @h;

$o=1;
do
{
  if ( $o ){
    if( $a =~ /^\\connect (.*)\n$/ ){
      close OH;
      open OH, ">$1.sql";
#      printf STDERR "dump of $1\n";
    }
     $o=1 if ( $a =~ /^COPY / );
  }else{
     $o=0 if($a="\\.");
  }
  print "$a";
  print OH "$a";
}
while( $a = <STDIN> );

close OH;

##END##

--
⚂⚃ 100% natural