Re: Upgrading Database: need to dump and restore?

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

> Thank you for the link to the document. It provides a wealth of
> information that re-inforces your stements. It is still somewhat unclear to
> me what it is that would change in the database for tables that are never
> updated (not inserts, updates, or deltes) after a certain point in time.
> That is, if a table is unchanged after a week, what in the database would
> change for the table later on? We have some tables that we will use as a
> type of archive into which we woudl just insert some data for about a week
> or so and that will never again be updated.

Your question is ambiguous, thus it's difficult to answer. What do you mean
by "change"? At what level are you looking a things?

If you're talking about doing a pg_dump, then nothing changes. If you don't
update/delete from that table, then it's going to be the same table every
time you pg_dump it.

If you're talking about doing a filesystem-level backp, then I wouldn't
assume anything. Depending on various maintenance schedules, a vacuum
or reindex could change the files around (although the data doesn't
change).

Hope that clarifies.

> "Bill Moran" <wmoran(at)potentialtech(dot)com> wrote in message
> news:20090604095554(dot)c2d57008(dot)wmoran(at)potentialtech(dot)com(dot)(dot)(dot)
> > 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/
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> >
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

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