Re: Upgrading Database: need to dump and restore?

In response to "Carlos Oliva" <carlos(at)pbsinet(dot)com>:

> I think that I understand. Would we need to stop the databse and then do
> the copy? Is this the state to which you are refering? If the tables never
> changed after a week or so, what else would change in the database for these
> tables after a month, two months, or a year? Would we need to put the
> databse in the correct state a week later, a month later, a year later?

You really need to work on your posting etiquette a bit. This thread is
painful to read because everything is jumbled together.

There are two supported methods for backing up data. These are separate,
you can do either or both, they have advantages and disadvantages.

You should really read this chapter:
http://www.postgresql.org/docs/8.3/static/backup.html

It seems to me that all of the questions you're asking are answered in
there.

But, specifically, if you're using pg_dump, you can specify to only back
up certain tables, or to back up everything _except_ certain tables, and
that would allow you to back up tables that don't change much infrequently
and tables that change a lot more often. That will work fine from a
database server standpoint. Whether it works for you data in particular,
is a question that only someone familiar with your data can answer. My
opinion: if you can't answer that question yourself, just back up everything
to be safe.

With filesystem level backup (or PITR, which is just filesystem backup
without having to stop the sever and a few other cool perks) you back up
the entire database or nothing.

Hope this helps.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/