Re: Backing up several PostgreSQL databases

From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Backing up several PostgreSQL databases
Date: 2011-02-09 12:00:14
Message-ID: iitvke$3dt$1@reversiblemaps.ath.cx
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

On 2011-02-05, Rolando Edwards <redwards(at)logicworks(dot)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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Jasen Betts 2011-02-09 12:16:53 Re: raise statement, how to do multi-line message
Previous Message Jasen Betts 2011-02-09 10:57:30 Re: ERROR: invalid datatype 'FILE'