Skip site navigation (1) Skip section navigation (2)

Re: Backing up several PostgreSQL databases

From: Lew <noone(at)lewscanon(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Backing up several PostgreSQL databases
Date: 2011-02-06 17:53:20
Message-ID: iimn6c$o5q$1@news.albasani.net (view raw or flat)
Thread:
Lists: pgsql-novice
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|
|_____|_____|
|===========|

In response to

pgsql-novice by date

Next:From: matty jonesDate: 2011-02-08 20:04:37
Subject: table design
Previous:From: Leon StarrDate: 2011-02-06 06:09:36
Subject: raise statement, how to do multi-line message

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group