Re: 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|
|_____|_____|
|===========|