Thread: Backing up several PostgreSQL databases

On 02/05/2011 03:43 PM, JORGE MALDONADO wrote:
> What would be an efficient way to backup several PostgreSQL databases? The
> number of DB's [sic] is not a constant because it depends on the number of clients
> our company has. Because our company is new, we actually have 1 customer, but
> in the future we plan to grow so we probably have to manage many DB's. The
> application we offer is a web app and we also provide hosting so one server
> can have more than one database.

I am no expert in this area, as undoubtedly some other responders will be, but
even I know that this depends on how you deploy those databases.  Will they be
colocated on one server?  Will you have virtual hosts, with one database (or
more) on each?  Will you have physically separate nodes hosting each database?

It also depends on the parameters for your backup.  Will you pull the systems
offline to back them up?  How much downtime can you allow, if any?  How often
will you back up?  What are your operational requirements for restore
scenarios?  (For some reason, restore operations are often much less well
planned than backup operations.)  What does "efficient" mean to you?  What are
the data quality requirements for the restores?

There are quite a few ways to back up databases, such as PG's own utilities,
replicators like slony, and hard-drive image backups.  I defer to the experts
and the documentation regarding the strengths and weaknesses of each.  You
should consider an adaptable strategy, so that the technique you use today for
one database need not be the one you use tomorrow for a thousand.

There is no "one size fits all" answer.  You have to provide the operational
constraints for the question of the right backup (and restore!) strategy even
to make sense.

--
Lew
Ceci n'est pas une fenêtre.
.___________.
|###] | [###|
|##/  | *\##|
|#/ * |   \#|
|#----|----#|
||    |  * ||
|o *  |    o|
|_____|_____|
|===========|

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